How to delete old data collection information (4304135)

If old records kept in PAReporting database, such as DBO.Activity, DBO.Discovery and DBO.DeployedRules, need to be deleted, then a script is needed to do so. Currently there is no built-in functionality to perform  this action.

Other tables such as DBO.Computer, DBO.User, DBO.Rule should not be modified since they store information used for functionalities, not only data collection information.

To be able to execute SQL scripts in database, the MS SQL Server Management Studio is needed. This tool can be downloaded from its Microsoft Site Location.

To execute a script in SQL Server Management Studio this Microsoft Document can be followed.

To remove records older than n days, a script may be used, the SQL Variable @DiffDate shows the number of days retained in databases, any other record older than that value will be deleted. In this script, the number of days to be kept is 90.

USE [PAReporting]
GO

DECLARE  @DiffDate INT = 90;

DELETE * FROM [dbo].[Discovery]
WHERE DATEDIFF(day, [DateTimeUTC], GetDate())  > @DiffDate;
DELETE * FROM [PAReporting].[dbo].[Activity]
WHERE DATEDIFF(day, [DateTimeUTC], GetDate())  > @DiffDate;
DELETE * FROM [PAReporting].[dbo].[DeployedRule]
WHERE DATEDIFF(day, [DateAdded], GetDate())  > @DiffDate;

GO

DISCLAIMER:  The information in the script(s) provided is known to work successfully; however, they have not been officially tested by our Quality Control.

If any of these instructions are changed and/or incorrectly used, intentionally or unintentionally, this solution becomes unsupported by our Support and Development.
Support and Development recommend always making a backup of the current database prior to execution of any script(s) that may modify it.  For customization of the KACE Systems Management Appliance, please contact our Professional Services Organization.