Purge Reporting Data

Purge Data with Automation

Automated purging is now built into Endpoint Privilege Management Reporting. The automatic purge of data is immediately enabled during a new installation or an upgrade of Endpoint Privilege Management Reporting and defaults to keeping 90 days worth of data in the database. Configuration of the feature is achieved via the dbo.Config table with the following records:

Name Description
MaintenanceEnabled

A config flag to indicate if maintenance is enabled. This can be updated by the following SQL Query (1 is enabled, 0 is disabled)

UPDATE Config
SET BitValue = 1
WHERE ConfigId = 'MaintenanceEnabled'
PurgeEnabled

A config flag to indicate if automatic purging is enabled. This can be updated by the following SQL Query (1 is enabled, 0 is disabled)

UPDATE Config
SET BitValue = 1
WHERE ConfigId = 'PurgeEnabled'
LastMaintenanceRun A config to indicate when the last time that Maintenance was run. This is an internal configuration which should not be updated without consulting our support team
DataRetentionDays

Number of days worth of data to keep. This can be updated by the following SQL Query

UPDATE Config
SET BigIntValue = 90
WHERE ConfigId = 'DataRetentionDays'

 

Both the MaintenanceEnabled and the PurgeEnabled flags need to be enabled for purging to work.

Purge Data by Stored Procedure

You can use the stored procedure PurgeData to purge data from the Privilege Management Reporting Database. PurgeData accepts three arguments. Either the first argument, or the second argument can be used, but not both. NULL should be passed for the unused argument.

  • Date: All events from before this date are purged.
  • Integer: All events older that this number (in months) are purged.
  • Integer: A declared integer variable to store the return value of the PurgeData stored procedure (number of events purged). See example below. This can be set to NULL if not required.
EXEC PurgeData '20230601', NULL, NULL

This purges all data before the specified date in the format YYYYMMDD

DECLARE @EventsDeleted INT
EXEC PurgeData NULL, 6, @EventsDeleted = @EventsDeleted OUTPUT

This purges all data older than 6 months, and outputs the number of events deleted to the @EventsDeleted variable

Small Batch Examples

It may be necessary to delete data in smaller batches than above so the transaction log does not fill up. Only use this approach if there is a backlog of data to purge.

EXEC PurgeData NULL, 12, NULL;
EXEC PurgeData NULL, 11, NULL;
EXEC PurgeData NULL, 10, NULL;
EXEC PurgeData NULL, 9, NULL;
EXEC PurgeData NULL, 8, NULL;
EXEC PurgeData NULL, 7, NULL;
EXEC PurgeData NULL, 6, NULL;

This purges all data older than 12 months, then 11 months, 10 months, and so on.

Use the first parameter if you need to delete in smaller batches than 1 month:

EXEC PurgeData '20180101', NULL, NULL
EXEC PurgeData '20180102', NULL, NULL
EXEC PurgeData '20180103', NULL, NULL etc

This purges all data before the specified date in the format YYYYMMDD.

Purge by Individual User, Host, or Workstyle

You can use the stored procedure PurgeEventsByEntity to purge events from a specified host, user, or Workstyle by running the script below with the specified ID.

You can query the Hosts, FullDetail_Users, or Policies tables for the HostID, UserID, or Workstyle name respectively. The ID is populated in the script to purge the events from that specific entity. Only one ID can be used each time you run the script.

To obtain the HostID from the Hosts table:

SELECT HostID FROM Hosts WHERE NAME = 'YourHostName'

To obtain the HostID from the Users table:

SELECT UserID FROM FullDetail_Users WHERE FormattedName = 'YourDomain\YourUser'

To obtain the PolicyID:

SELECT ID FROM Policies WHERE PGPolicyName = 'WorkstyleName'

Script

Only one ID can be used each time you run this script. Replace the other two IDs with NULL.

EXEC PurgeEventsByEntity HostID, UserID, PolicyID

Examples

To purge events for HostID 12, run:

EXEC PurgeEventsByEntity 12, NULL, NULL

To purge events for UserID 17, run:

EXEC PurgeEventsByEntity NULL, 17, NULL

To purge events for Workstyle 5, run:

EXEC PurgeEventsByEntity NULL, NULL, 5

Shrink the Database

If a large amount of data is being purged from the Endpoint Privilege Management database, we recommend the database is shrunk once the purge is complete. Shrinking the database reduces the disk space consumed by the database and log files by removing empty data and log pages.

A database shrink can be configured as a Maintenance Plan in SQL Server Management Studio, and can be configured to run on a regular schedule.