Cleanup Old DSM Reporter's Report Results from the Database.

book

Article ID: 4189

calendar_today

Updated On:

Products

CA Client Automation - IT Client Manager CA Client Automation

Issue/Introduction

After a long time running reports using DSM Reporter, the number of result tables in the database may be increasing.

This document provides a T-SQL script, to automate cleaning up DSM Reporter result tables and views that may have accumulated over time.  By using the script, it avoids the user from having to manually cleanup the aged result tables using the DSM Reporter GUI, or manually cleaning up these objects from SQL.

Environment

All versions of Client Automation using a Microsoft SQL database.

Resolution

Run the following script in MS-SQL, against the MDB for Client Automation.  Set the variable "@numdays" to the desired age of report results to be cleaned up:

Setting a value of @numdays=30 will cleanup all report results 30 days, and older.
Setting a value of @numdays=0 will cleanup ALL report result data.



-- Declare variables
 
DECLARE @numdays INT
DECLARE @tplid INT
DECLARE @resid INT
DECLARE @tblname NVARCHAR(64)
DECLARE @sql VARCHAR(400)
 
-- Specify the age for result table cleanup
 
SET @numdays=30
 
-- Iterate each result record
 
DECLARE Result_Cursor CURSOR for

 

  SELECT tplid, resid, tblname
  FROM rpresult
  WHERE created < DATEDIFF(s, '1970-01-01', GETUTCDATE()) - (@numdays * 86400)

 

OPEN Result_Cursor
FETCH NEXT FROM Result_Cursor INTO @tplid, @resid, @tblname
WHILE @@FETCH_STATUS = 0
 
BEGIN
 
  -- Delete result record
 
  DELETE FROM RPRESULT WHERE [email protected] AND [email protected]
 
  -- Delete filter record
 
  DELETE FROM RPFILTER WHERE [email protected] AND [email protected]
 
  -- Drop the result table
 
  SET @sql='DROP TABLE ca_itrm.' + @tblname
  EXEC (@sql)
  DELETE FROM RPTABLES WHERE [email protected]
  SET @sql='DROP TABLE ca_itrm.' + @tblname + '_M'
  EXEC (@sql)
 
  -- Drop the table record
 
  DELETE FROM RPTABLES WHERE [email protected] + '_M'
FETCH NEXT FROM Result_Cursor INTO @tplid, @resid, @tblname
END
CLOSE Result_Cursor
DEALLOCATE Result_Cursor
 
-- Iterate each temp view
 
DECLARE Temp_Cursor CURSOR FOR
 
SELECT u.name + '.' + o.name
FROM dbo.sysobjects o, dbo.sysusers u
WHERE o.name like 'rp_tmp_%' and crdate < getdate() - 1
AND o.uid=u.uid
AND o.type='V'
 
OPEN Temp_Cursor
FETCH NEXT FROM Temp_Cursor
INTO @tblname
WHILE @@FETCH_STATUS=0
BEGIN
 
   -- Drop temp view
 
   SET @sql='DROP VIEW ' + @tblname
   exec(@sql)
 
FETCH NEXT FROM Temp_Cursor
INTO @tblname
END
CLOSE Temp_Cursor
DEALLOCATE Temp_Cursor
 
-- Iterate each temp table
 
DECLARE Temp_Cursor CURSOR FOR
 
SELECT u.name + '.' + o.name
FROM dbo.sysobjects o, dbo.sysusers u
WHERE o.name like 'rp_tmp_%' and crdate < getdate() - 1
AND o.uid=u.uid
AND o.type='U'
 
OPEN Temp_Cursor
 
FETCH NEXT FROM Temp_Cursor
INTO @tblname
WHILE @@FETCH_STATUS=0
 
BEGIN
 
   -- Drop temp view
 
   SET @sql='DROP TABLE ' + @tblname
   exec(@sql)
 
FETCH NEXT FROM Temp_Cursor
INTO @tblname
END
CLOSE Temp_Cursor
DEALLOCATE Temp_Cursor