Purging Records

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

EventSentry (starting with v2.71) also includes a command-line application that can purge records from the EventSentry database. You can schedule this tool to run on a regular basis using the EventSentry or Windows scheduler. Please see Purging Records Automatically for more information.

Microsoft SQL Server

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 following SQL command into the "Command" window:

set QUOTED_IDENTIFIER on;

delete from ESEventlogMain where DATEDIFF(day, eventtime, GETDATE()) > 90

where "EventSentry" is the name of the table and "90" are 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.

Hint: To purge records from other tables as well simply repeat (5) and add another step, adapting the SQL script to reflect the new table name and field containing the time stamp. SQL statements for all the tables are listed here:

set QUOTED_IDENTIFIER on;

DELETE FROM ESAppHistory WHERE DATEDIFF(day, recorddate, GETDATE()) > 90

DELETE FROM ESDiskspace WHERE DATEDIFF(day, recorddate, GETDATE()) > 90

DELETE FROM ESEnvironment WHERE DATEDIFF(day, recorddate, GETDATE()) > 90

DELETE FROM ESEventlogMain WHERE DATEDIFF(day, eventtime, GETDATE()) > 90

DELETE FROM ESFileMain WHERE DATEDIFF(day, recorddate, GETDATE()) > 90

DELETE FROM ESFileMainDelim WHERE DATEDIFF(day, recorddate, GETDATE()) > 90

DELETE FROM ESFileMonHistory WHERE DATEDIFF(day, recorddate, GETDATE()) > 90

DELETE FROM ESFolderStatus WHERE DATEDIFF(day, recorddate, GETDATE()) > 90

DELETE FROM ESHeartbeatHistory WHERE DATEDIFF(day, recorddate, GETDATE()) > 90

DELETE FROM ESHeartbeatPingTracking WHERE DATEDIFF(day, recorddate, GETDATE()) > 90

DELETE FROM ESLogonTracking WHERE DATEDIFF(day, start_datetime, GETDATE()) > 90

DELETE FROM ESMotionTracking WHERE DATEDIFF(day, recorddate, GETDATE()) > 90

DELETE FROM ESNessusLog WHERE DATEDIFF(day, recorddate, GETDATE()) > 90

DELETE FROM ESObjectTracking WHERE DATEDIFF(day, recorddate, GETDATE()) > 90

DELETE FROM ESPerformance WHERE DATEDIFF(day, recorddate, GETDATE()) > 90

DELETE FROM ESPrintTracking WHERE DATEDIFF(day, start_datetime, GETDATE()) > 90

DELETE FROM ESPSTracking WHERE DATEDIFF(day, start_datetime, GETDATE()) > 90

DELETE FROM ESServiceHistory WHERE DATEDIFF(day, recorddate, GETDATE()) > 90

DELETE FROM ESSyslogMain WHERE DATEDIFF(day, recorddate, GETDATE()) > 90

DELETE FROM ESTrackingAccountGroups WHERE DATEDIFF(day, recorddate, GETDATE()) > 90

DELETE FROM ESTrackingAccountComputers WHERE DATEDIFF(day, recorddate, GETDATE()) > 90

DELETE FROM ESTrackingAccountUsers WHERE DATEDIFF(day, recorddate, GETDATE()) > 90

DELETE FROM ESTrackingPolicy WHERE DATEDIFF(day, recorddate, GETDATE()) > 90

DELETE FROM ESTrackingAuthFailure WHERE DATEDIFF(day, recorddate, GETDATE()) > 90

DELETE FROM ESTrackingLogonByType WHERE DATEDIFF(day, recorddate, GETDATE()) > 90

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

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, 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.

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". Future versions of EventSentry will ship with an executable that will run SQL commands against a MySQL database.

In the meantime you can 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 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;

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". Future versions of EventSentry will ship with an executable that will run SQL commands against a Oracle database.

delete from ESEventlogMain where trunc(sysdate,'DAY') - eventtime + 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 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 ESPerformance where trunc(sysdate,'DAY') - recorddate + 1 > 120;

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

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

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;