Manual Removal Steps for Events Using the Stored Procedure.

Products

• Critical System Protection
• Data Center Security Server
• Data Center Security Server Advanced

Issue/Introduction

To know how to purge events from SCSP database using a manual process follow the steps in this article.

Resolution

SCSP 5.x, DCS 6.5.x

In SCSP 5.1 and newer a purging was integrated into the management console, which calls a stored procedure called "SCSP_PurgeEvents". While it is preferable to use the console to schedule regular maintenance of the scspdb database, it may be necessary to maintain more control over the purge process. This can be accomplished by manually calling the "SCSP_PurgeEvents" stored procedure using the SQL Query Analyzer. Below is the information regarding this stored procedure and two examples of how it can be used:

Variables:
@EventCLASS Nvarchar(100) One of "REALTIME", "PROFILE", "ANALYSIS"

@PurgeMode Nvarchar(100) One of "TESTMODE", "PURGE" -- "TESTMODE" doesn't delete, but "PURGE" does.

@FilterMode Nvarchar(100)='DAYS' Currently, the only value allowed is "DAYS".

@PurgeLimit int=0 This is a governor to limit how many records to delete at once. Default "0" means no limit. Use this value to keep a very large purge operation from blocking other activity, such as event inserts.
For example, a value of 10000 will limit processing to this amount, even if there are a million matching records. Please note, however, that the return value will always be the total number of records.
So, when testing, and the purge has been limited to 50 records out of 150 total, the return will be 150. When you purge, the return will be the same, so it will look like all of the records have been
removed. This isn't the case, though, and can be proved by switching back to TESTMODE and running the query again. The return will now be 100 records confirming the purging of 50 records.

@Process_Rules varchar(8)='P' Flags indicating processing mode. "P" (Print Mode) will print progress and results to screen (good for OSQL). "Q" (Quiet Mode) does not print to screen.


Samples:

Display summary of events that would be purged that are greater than 8 days old:
exec scsp_purgeevents @eventclass = 'realtime', @purgemode = 'testmode', @filtermode = 'days', @filtervalue = '8'


Purge records older than 30 days from the RealTime Event table, limit the purge to no more than 10,000 rows:
exec scsp_purgeevents @eventclass = 'realtime', @purgemode = 'purge', @filtervalue = '30', @purgelimit = 10000

SCSP 5.0 and below -This should only be used as a last resort, make sure to have a full backup of the database.
In earlier versions of SCSP (prior to 5.1), manually purging the database was performed by Database Admins (DBA) by creating queries similar to the following:

• USE SCSPDB
  DELETE FROM CSPEVENT WHERE EVENT_DT<= '2006-03-10 23:59:59:999'

Sample query to count the amount of events before a specific date.

The follow query can be used to get a count of how many events are older than a specific date.  This can help determine if the purge process is removing all the events generated.  The dates can be edited to get an understanding of the amount of events received for specific time frames.

• USE SCSPDB
  SELECT COUNT(*) FROM CSPEVENT WHERE EVENT_DT<= '2017-05-17 23:59:59:999'