DSM Reporter: How to cleanup old report results from the database.

book

Article ID: 4189

calendar_today

Updated On:

Products

CA Automation Suite for Data Centers - Configuration Automation CA Client Automation - Asset Management CA Client Automation - IT Client Manager CA Client Automation CA Client Automation - Remote Control CA Client Automation - Asset Intelligence CA Client Automation - Desktop Migration Manager CA Client Automation - Patch Manager

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
 
End of technical document.