Purging Records

<< Click to Display Table of Contents >>

Navigation:  Additional Tips and Resources > Database Tips >

Purging Records

The event log consolidation and process tracking tables might grow too large after a while. You can configure your system to periodically purge records that are no longer relevant, e.g. after 3 months. This chapter shows:

 

How to use the included database purge utility to purge records automatically on all supported databases

The SQL Queries needed to purge database records from any feature (e.g. event log monitoring, performance monitoring)

How to setup scheduled jobs on Microsoft SQL Server® to automate the purging of records

 

info_48

EventSentry includes a command-line application that can purge records from the EventSentry database. The utility can be scheduled to run on a regular basis using the EventSentry application scheduler or the Windows Task Scheduler. See Purging Records Automatically for more information.

 

Setting up an automatic job (Microsoft SQL Server® 2000)

 

1.Open "Start" -> "Programs" -> "Administrative Tools" -> "Services" and make sure that the service "SQLSERVERAGENT" is running and set to automatic start mode.
2.Open "SQL Server Enterprise Manager" and navigate to "SQL Server Group" -> "Your Servername" -> "Management" -> "SQL Server Agent" -> "Jobs":

 

clip0531

 

3.Right-click "Jobs" in the left pane and select "New Job ..."
4.In the "General" tab, enter a name for the job, such as EventSentry Database Purge

 

clip0532

 

5.Click on "Steps" and create a new step by clicking on the "New" button. In this step we will add a SQL script that deletes all records from the table that are older than 90 days.

 

clip0533

 

Apply a descriptive name to this step, set the type to "Transact-SQL Script", select the correct database and paste the SQL command(s) from the Microsoft SQL Server® section below into the "Command" window while making sure that the correct database is selected.

 

The number "90" shown in the SQL statements is the number of days you want to keep records in the table. This script will remove all records from the table that are older than 90 days. Click OK to save this step.

 

6.Click on "Schedules" to specify how often you want to purge records. In our example we will purge records once a week, but you could also run this script daily or bi-monthly. Click on "New Schedule" and add a new recurring schedule. Assign a descriptive name to the schedule and click OK.

 

7.At last you can specify whether you want to be notified when this job runs or generates an error. Click the "Notifications" tab and set the desired options. In our case we write an event to the event log every time the job runs:

 

clip0534

 

warning_32

It is recommended that you purge data frequently (with smaller amounts of data affected at each purge) to reduce the impact of the purge on the database. For example, instead of setting up a job to delete records that are older than 180 days once a month, set the job up to run at least once a week. This way each job will affect less data and as such complete more quickly.

 

Setting up an automatic job (Microsoft SQL Server® 2005 and later)

 

1.Open the "Microsoft SQL Server® Management Studio" and navigate to "SQL Server Agent" -> "Jobs".

2.Right-click "Jobs" and select "New Job ..."

3.Specify a name for the job (e.g. "EventSentry Database Purge") and click on "Steps" in the left pane.

4.In the steps window, click the "New..." button.

5.Give the step a descriptive name and paste one of the SQL statements below. You can specify multiple SQL statements, but separate them with a GO statement in a single line.

 

clip0263

 

6.Make sure the correct database is selected.

7.Click the "Advanced" tab and select "Go to the next step" for the "On failure action" setting. Click OK.

8.Add another step if necessary.

 

clip0264

 

9.Click "Schedules" and add a new schedule.

10.Give the schedule a name (e.g. Weekly) and configure the schedule.

11.Click on "Notifications" to enable error reporting. Check the box "Write to the Windows Application event log" and select "When the job completes". You can change this option and only log an event if the job fails.

 

clip0265

 

12.Click OK to add the schedule

 

Microsoft SQL Server®

 

set QUOTED_IDENTIFIER on;

DELETE ESAppHistory where recorddate < DATEADD(dd, -90, getdate())

DELETE ESDiskspace where recorddate < DATEADD(dd, -90, getdate())

DELETE ESEnvironment where recorddate < DATEADD(dd, -90, getdate())

DELETE ESEventlogMain where eventtime < DATEADD(dd, -90, getdate())

DELETE ESFileMain where recorddate < DATEADD(dd, -90, getdate())

DELETE ESFileMainDelim where recorddate < DATEADD(dd, -90, getdate())

DELETE ESFilemonHistory where recorddate < DATEADD(dd, -90, getdate())

DELETE ESFolderStatus where recorddate < DATEADD(dd, -90, getdate())

DELETE ESHeartbeatHistory where recorddate < DATEADD(dd, -90, getdate())

DELETE ESHeartbeatPingTracking where recorddate < DATEADD(dd, -90, getdate())

DELETE ESLogonTracking where start_datetime < DATEADD(dd, -90, getdate())

DELETE ESMotionTracking where recorddate < DATEADD(dd, -90, getdate())

DELETE ESNessusLog where recorddate < DATEADD(dd, -90, getdate())

DELETE ESObjectTracking where recorddate < DATEADD(dd, -90, getdate())

DELETE ESPerformance where recorddate < DATEADD(dd, -90, getdate())

DELETE ESPrintTracking where start_datetime < DATEADD(dd, -90, getdate())

DELETE ESPSTracking where start_datetime < DATEADD(dd, -90, getdate())

DELETE ESServiceHistory where recorddate < DATEADD(dd, -90, getdate())

DELETE ESSnmpTraps where recorddate < DATEADD(dd, -90, getdate())

DELETE ESSyslogMain where recorddate < DATEADD(dd, -90, getdate())

DELETE ESTrackingAccountGroups where recorddate < DATEADD(dd, -90, getdate())

DELETE ESTrackingAccountComputers where recorddate < DATEADD(dd, -90, getdate())

DELETE ESTrackingAccountUsers where recorddate < DATEADD(dd, -90, getdate())

DELETE ESTrackingPolicy where recorddate < DATEADD(dd, -90, getdate())

DELETE ESTrackingAuthFailure where recorddate < DATEADD(dd, -90, getdate())

DELETE ESTrackingLogonByType where recorddate < DATEADD(dd, -90, getdate())

DELETE ESTrackingLogonAuth where recorddate < DATEADD(dd, -90, getdate())

DELETE ESScheduledTasks where recorddate < DATEADD(dd, -90, getdate())

DELETE ESScheduledTasksHistory where recorddate < DATEADD(dd, -90, getdate())

DELETE ESDiskspaceLargeFiles where recorddate < DATEADD(dd, -90, getdate())

DELETE ESNetFlowMain where recorddate < DATEADD(dd, -90, getdate())

DELETE ESNetFlowBandwidth where recorddate < DATEADD(dd, -90, getdate())

 

Built-In PostgreSQL Database

 

PostgreSQL does not currently ship with an equivalent of a SQL Server Agent where you would be able to schedule SQL commands. In order to run SQL commands on PostgreSQL you can use a scripting language such as Perl for example, and you can then schedule your scripts using Windows' "Scheduled Tasks". Alternatively you can also purge records using the database purge utility.

 

Use the following SQL statements to purge records that are older than 90 days:

 

DELETE FROM ESAppHistory WHERE EXTRACT(EPOCH FROM current_timestamp - recorddate) / 86400 > 90

DELETE FROM ESDiskspace WHERE EXTRACT(EPOCH FROM current_timestamp - recorddate) / 86400 > 90

DELETE FROM ESEnvironment WHERE EXTRACT(EPOCH FROM current_timestamp - recorddate) / 86400 > 90

DELETE FROM ESEventlogMain WHERE EXTRACT(EPOCH FROM current_timestamp - eventtime) / 86400 > 90

DELETE FROM ESFileMain WHERE EXTRACT(EPOCH FROM current_timestamp - recorddate) / 86400 > 90

DELETE FROM ESFileMainDelim WHERE EXTRACT(EPOCH FROM current_timestamp - recorddate) / 86400 > 90

DELETE FROM ESFileMonHistory WHERE EXTRACT(EPOCH FROM current_timestamp - recorddate) / 86400 > 90

DELETE FROM ESFolderStatus WHERE EXTRACT(EPOCH FROM current_timestamp - recorddate) / 86400 > 90

DELETE FROM ESHeartbeatHistory WHERE EXTRACT(EPOCH FROM current_timestamp - recorddate) / 86400 > 90

DELETE FROM ESHeartbeatPingTracking WHERE EXTRACT(EPOCH FROM current_timestamp - recorddate) / 86400 > 90

DELETE FROM ESLogonTracking WHERE EXTRACT(EPOCH FROM current_timestamp - start_datetime) / 86400 > 90

DELETE FROM ESMotionTracking WHERE EXTRACT(EPOCH FROM current_timestamp - recorddate) / 86400 > 90

DELETE FROM ESNessusLog WHERE EXTRACT(EPOCH FROM current_timestamp - recorddate) / 86400 > 90

DELETE FROM ESObjectTracking WHERE EXTRACT(EPOCH FROM current_timestamp - recorddate) / 86400 > 90

DELETE FROM ESPerformance WHERE EXTRACT(EPOCH FROM current_timestamp - recorddate) / 86400 > 90

DELETE FROM ESPrintTracking WHERE EXTRACT(EPOCH FROM current_timestamp - start_datetime) / 86400 > 90

DELETE FROM ESPSTracking WHERE EXTRACT(EPOCH FROM current_timestamp - start_datetime) / 86400 > 90

DELETE FROM ESServiceHistory WHERE EXTRACT(EPOCH FROM current_timestamp - recorddate) / 86400 > 90

DELETE FROM ESSyslogMain WHERE EXTRACT(EPOCH FROM current_timestamp - recorddate) / 86400 > 90

DELETE FROM ESSnmpTraps WHERE EXTRACT(EPOCH FROM current_timestamp - recorddate) / 86400 > 90

DELETE FROM ESTrackingAccountGroups WHERE EXTRACT(EPOCH FROM current_timestamp - recorddate) / 86400 > 90

DELETE FROM ESTrackingAccountComputers WHERE EXTRACT(EPOCH FROM current_timestamp - recorddate) / 86400 > 90

DELETE FROM ESTrackingAccountUsers WHERE EXTRACT(EPOCH FROM current_timestamp - recorddate) / 86400 > 90

DELETE FROM ESTrackingPolicy WHERE EXTRACT(EPOCH FROM current_timestamp - recorddate) / 86400 > 90

DELETE FROM ESTrackingAuthFailure WHERE EXTRACT(EPOCH FROM current_timestamp - recorddate) / 86400 > 90

DELETE FROM ESTrackingLogonByType WHERE EXTRACT(EPOCH FROM current_timestamp - recorddate) / 86400 > 90

DELETE FROM ESTrackingLogonAuth WHERE EXTRACT(EPOCH FROM current_timestamp - recorddate) / 86400 > 90

DELETE FROM ESScheduledTasks WHERE EXTRACT(EPOCH FROM current_timestamp - recorddate) / 86400 > 90

DELETE FROM ESScheduledTasksHistory WHERE EXTRACT(EPOCH FROM current_timestamp - recorddate) / 86400 > 90

DELETE FROM ESDiskspaceLargeFiles WHERE EXTRACT(EPOCH FROM current_timestamp - recorddate) / 86400 > 90

DELETE FROM ESNetFlowMain WHERE EXTRACT(EPOCH FROM current_timestamp - recorddate) / 86400 > 90

DELETE FROM ESNetFlowBandwidth WHERE EXTRACT(EPOCH FROM current_timestamp - recorddate) / 86400 > 90

 

MySQL

 

MySQL does not currently ship with an equivalent of a SQL Server Agent where you would be able to schedule SQL commands. In order to run SQL commands on MySQL you can use a scripting language such as Perl for example, and you can then schedule your scripts using Windows' "Scheduled Tasks". Alternatively you can also purge records using the database purge utility.

 

Use the following SQL statements to purge records that are older than 90 days:

 

DELETE FROM ESAppHistory WHERE DATE_SUB(CURDATE(),INTERVAL 90 DAY) > recorddate;

DELETE FROM ESDiskspace WHERE DATE_SUB(CURDATE(),INTERVAL 90 DAY) > recorddate;

DELETE FROM ESEnvironment WHERE DATE_SUB(CURDATE(),INTERVAL 90 DAY) > recorddate;

DELETE FROM ESEventlogMain WHERE DATE_SUB(CURDATE(),INTERVAL 90 DAY) > eventtime;

DELETE FROM ESFileMain WHERE DATE_SUB(CURDATE(),INTERVAL 90 DAY) > recorddate;

DELETE FROM ESFileMainDelim WHERE DATE_SUB(CURDATE(),INTERVAL 90 DAY) > recorddate;

DELETE FROM ESFileMonHistory WHERE DATE_SUB(CURDATE(),INTERVAL 90 DAY) > recorddate;

DELETE FROM ESFolderStatus WHERE DATE_SUB(CURDATE(),INTERVAL 90 DAY) > recorddate;

DELETE FROM ESHeartbeatHistory WHERE DATE_SUB(CURDATE(),INTERVAL 90 DAY) > recorddate;

DELETE FROM ESHeartbeatPingTracking WHERE DATE_SUB(CURDATE(),INTERVAL 90 DAY) > recorddate;

DELETE FROM ESLogonTracking WHERE DATE_SUB(CURDATE(),INTERVAL 90 DAY) > start_datetime;

DELETE FROM ESMotionTracking WHERE DATE_SUB(CURDATE(),INTERVAL 90 DAY) > recorddate;

DELETE FROM ESNessusLog WHERE DATE_SUB(CURDATE(),INTERVAL 90 DAY) > recorddate;

DELETE FROM ESObjectTracking WHERE DATE_SUB(CURDATE(),INTERVAL 90 DAY) > recorddate;

DELETE FROM ESPerformance WHERE DATE_SUB(CURDATE(),INTERVAL 90 DAY) > recorddate;

DELETE FROM ESPrintTracking WHERE DATE_SUB(CURDATE(),INTERVAL 90 DAY) > start_datetime;

DELETE FROM ESPSTracking WHERE DATE_SUB(CURDATE(),INTERVAL 90 DAY) > start_datetime;

DELETE FROM ESServiceHistory WHERE DATE_SUB(CURDATE(),INTERVAL 90 DAY) > recorddate;

DELETE FROM ESSyslogMain WHERE DATE_SUB(CURDATE(),INTERVAL 90 DAY) > recorddate;

DELETE FROM ESSnmpTraps WHERE DATE_SUB(CURDATE(),INTERVAL 90 DAY) > recorddate;

DELETE FROM ESTrackingAccountGroups WHERE DATE_SUB(CURDATE(),INTERVAL 90 DAY) > recorddate;

DELETE FROM ESTrackingAccountComputers WHERE DATE_SUB(CURDATE(),INTERVAL 90 DAY) > recorddate;

DELETE FROM ESTrackingAccountUsers WHERE DATE_SUB(CURDATE(),INTERVAL 90 DAY) > recorddate;

DELETE FROM ESTrackingPolicy WHERE DATE_SUB(CURDATE(),INTERVAL 90 DAY) > recorddate;

DELETE FROM ESTrackingAuthFailure WHERE DATE_SUB(CURDATE(),INTERVAL 90 DAY) > recorddate;

DELETE FROM ESTrackingLogonByType WHERE DATE_SUB(CURDATE(),INTERVAL 90 DAY) > recorddate;

DELETE FROM ESTrackingLogonAuth WHERE DATE_SUB(CURDATE(),INTERVAL 90 DAY) > recorddate;

DELETE FROM ESScheduledTasks WHERE DATE_SUB(CURDATE(),INTERVAL 90 DAY) > recorddate;

DELETE FROM ESScheduledTasksHistory WHERE DATE_SUB(CURDATE(),INTERVAL 90 DAY) > recorddate;

DELETE FROM ESDiskspaceLargeFiles WHERE DATE_SUB(CURDATE(),INTERVAL 90 DAY) > recorddate;

DELETE FROM ESNetFlowMain WHERE DATE_SUB(CURDATE(),INTERVAL 90 DAY) > recorddate;

DELETE FROM ESNetFlowBandwidth WHERE DATE_SUB(CURDATE(),INTERVAL 90 DAY) > recorddate;

 

Oracle

 

Oracle does not currently ship with an equivalent of a SQL Server Agent where you would be able to schedule SQL commands. In order to run SQL commands on Oracle you can use a scripting language such as Perl for example, and you can then schedule your scripts using Windows' "Scheduled Tasks". Alternatively you can also purge records using the database purge utility.

 

delete from ESEventlogMain where trunc(sysdate,'DAY') - eventtime + 1 > 90;

delete from ESSyslogMain where trunc(sysdate,'DAY') - recorddate + 1 > 90;

delete from ESSnmpTraps where trunc(sysdate,'DAY') - recorddate + 1 > 90;

delete from ESNessusLog where trunc(sysdate,'DAY') - recorddate + 1 > 90;

 

delete from ESPSTracking where trunc(sysdate,'DAY') - start_datetime + 1 > 90;

delete from ESLogonTracking where trunc(sysdate,'DAY') - start_datetime + 1 > 90;

delete from ESPrintTracking where trunc(sysdate,'DAY') - start_datetime + 1 > 90;

delete from ESObjectTracking where trunc(sysdate,'DAY') - recorddate + 1 > 90;

delete from ESTrackingAccountGroups where trunc(sysdate,'DAY') - recorddate + 1 > 90;

delete from ESTrackingAccountComputers where trunc(sysdate,'DAY') - recorddate + 1 > 90;

delete from ESTrackingAccountUsers where trunc(sysdate,'DAY') - recorddate + 1 > 90;

delete from ESTrackingPolicy where trunc(sysdate,'DAY') - recorddate + 1 > 90;

delete from ESTrackingAuthFailure where trunc(sysdate,'DAY') - recorddate + 1 > 90;

delete from ESTrackingLogonByType where trunc(sysdate,'DAY') - recorddate + 1 > 90;

delete from ESTrackingLogonAuth where trunc(sysdate,'DAY') - recorddate + 1 > 90;

 

delete from ESAppHistory where trunc(sysdate,'DAY') - recorddate + 1 > 180;

delete from ESServiceHistory where trunc(sysdate,'DAY') - recorddate + 1 > 180;

delete from ESHeartbeatHistory where trunc(sysdate,'DAY') - recorddate + 1 > 180;

delete from ESHeartbeatPingTracking where trunc(sysdate,'DAY') - recorddate + 1 > 180;

 

delete from ESPerformance where trunc(sysdate,'DAY') - recorddate + 1 > 120;

delete from ESDiskspace where trunc(sysdate,'DAY') - recorddate + 1 > 120;

delete from ESDiskspaceLargeFiles where trunc(sysdate,'DAY') - recorddate + 1 > 180;

delete from ESEnvironment where trunc(sysdate,'DAY') - recorddate + 1 > 90;

delete from ESMotionTracking where trunc(sysdate,'DAY') - recorddate + 1 > 360;

delete from ESFolderStatus where trunc(sysdate,'DAY') - recorddate + 1 > 360;

 

delete from ESFileMain where trunc(sysdate,'DAY') - recorddate + 1 > 90;

delete from ESFileMainDelim where trunc(sysdate,'DAY') - recorddate + 1 > 90;

 

delete from ESFilemonHistory where trunc(sysdate,'DAY') - recorddate + 1 > 90;

 

delete from ESScheduledTasks where trunc(sysdate,'DAY') - recorddate + 1 > 360;

delete from ESScheduledTasksHistory where trunc(sysdate,'DAY') - recorddate + 1 > 360;

 

delete from ESNetFlowMain where trunc(sysdate,'DAY') - recorddate + 1 > 90;

delete from ESNetFlowBandwidth where trunc(sysdate,'DAY') - recorddate + 1 > 90;