IMPORTANT: We recommend that you purge threat events with the built-in server task created for this purpose. Use this article only if the built-in tasks aren't sufficient.

This article provides guidance on how to purge large volumes of threat event information from the ePO database. To accomplish this task, you must use the attached script (PurgeThreatEvents.zip). You must modify the script to target the events you want to purge.

You can use the attached SQL query to purge events. By default, it purges any event with a DetectedUTC time older than 12 months in batches of 4,900 with a three-second delay between batches. You might need to adjust these values for your environment. Batch sizes larger than 5,000 must be avoided, because it causes SQL to change from a row lock to a table lock. To adjust the values, modify the following lines in the attached script to reflect your needs:
 
  --*****EDIT THE NEXT FOUR LINES AS NEEDED *****
declare @BatchSize int = 4900
declare @BatchDelaySeconds int = 3
declare @DeleteTime int = -12
declare @PurgeWhereClause NVARCHAR(MAX) = 'DetectedUTC < DATEADD(MM, @DeleteTime, GETDATE())'
 
Guidance on editing the above variables:

• @BatchSize – The larger your batch size, the faster the purge. A smaller batch size doesn't grow the transaction log as much. Keep the batch size under 5,000 to prevent SQL from reverting to a table lock. 
• @BatchDelaySeconds – This variable controls how long the script pauses between batches. If you develop a large backlog of events in the <ePOInstallDir>DB\Events folder while the purge script is running, you might need to increase the delay so the event parser can catch up on events between batches.
• @DeleteTime – If you're purging by time, this parameter controls the duration, in months, after which the events are purged. By default, it purges all events older than 12 months.
• @PurgeWhereClause – Write a select statement to identify the events you want to purge. Paste the WHERE clause of the select statement into this variable, between the single quotes. 

NOTE: If you need to include single quotes in your WHERE clause, you must put two single quotes together to avoid escaping the query. 

Example WHERE clauses:

• Purge all occurrences of EventID 1095:
  ThreatEventID = 1095

• Purge all occurrences of EventID 1095 and 1092:
  ThreatEventID IN (1092,1095)

• Purge all occurrences of EventIDs 1095 and 1092, which occurred more than 365 days (1 year) ago (Pass 365 for the variable @DeleteTime): 
  ReceivedUTC < DATEADD(dd, @DeleteTime, GETDATE()) AND ThreatEventID IN (1092,1095)

• Purge all occurrences of Events that occurred more than 365 days (1 year) ago (Pass 365 for the variable @DeleteTime): 
  ReceivedUTC < DATEADD(dd, @DeleteTime, GETDATE())

After you modify the script to meet your needs, use the following instructions to execute it:

1. Open SQL Management Studio and connect to the primary ePO database. See KB67591 - How to run a SQL script provided by Technical Support against the ePolicy Orchestrator database if you require detailed instructions for this step.
   
   IMPORANT: If you're using ePO 5.10, don't point to the events database. The script doesn't work properly unless it's run against the primary database.
    
2. Paste the contents of the script into the query window.
3. Click Execute or press F5.
4. Monitor the <InstallDirectory>\DB\Events folder on the ePO server and remote handlers. Confirm that you don't have an excessive backlog of events building up. If you do, you must stop the script and either decrease the batch size or increase the batch delay.
   
   NOTE: Depending on how many events you're purging, the script can take hours or days to complete. But, your ePO server continues to function while the script is running.

1. Make sure that the SQLSERVERAGENT service is running, and set to automatic start mode.
2. Open the Microsoft SQL Server Management Studio and navigate to SQL Server Agent, Jobs.
3. Right-click Jobs, and then select New Job.
4. Specify a name for the job. For example, ePO Events purging.
5. Click Steps in the left pane.
6. Click New in the Steps window.
7. Enter the SQL script (PurgeThreatEvents.zip) that you modified to meet your needs, as explained in the above section.
8. Make sure that the primary ePO database is selected.
9. Click Schedules, and then click New to create a schedule.
10. Give the schedule a name (for example, Weekly), and configure the schedule.
11. Click Notifications. This option enables error reporting.
12. Select the following:

• Write to the Windows Application event log
• When the job completes

12. To add the schedule, click OK.