Audit probe does not clean audit_event table automatically

Products

• DX Unified Infrastructure Management (Nimsoft / UIM)

Issue/Introduction

By default, the audit probe is configured to clean up the audit_event table from records older than 30 days. The default interval is 24 hours, so the cleanup is done once a day.

However, you may experience an issue, when there are too many records to delete, hence SQL query times out, which results in a growing audit_event table. The following message might be seen in the audit.log: 

 

Jan 17 03:00:53:041 [4168] 0 audit: ExecuteNoRecords - Query: DELETE FROM AUDIT_EVENT WHERE DATEDIFF(ss,event_time,GETDATE()) > 2592000 
Jan 17 03:00:53:041 [4168] 0 audit: [audit] ExecuteNoRecords - 1 errors
Jan 17 03:00:53:041 [4168] 0 audit: (1) ExecuteNoRecords [Microsoft SQL Server Native Client 11.0] Query timeout expired

 

 

Environment

Release : 20.x

Cause

The cleanup interval is too large, and the probe tries to remove the old records too rarely - thus those records keep stacking up day by day resulting in a huge amount and SQL query timeout. 

Resolution

By default, the cleaning process only runs once a day as the default "Administration time specification" specifies "03:00:00 interval 24:00:00" so it will execute this task at 3 AM every day.

That might not be enough to clean out all the 30-day-old records all at once in some systems. To workaround this issue, you would need to manually clean up the table from outdated records and adjust interval configuration, by increasing the execution frequency such as "00:00:00 interval 01:00:00". This configuration value will execute the cleaning task every 1h (or you can even set a lower value) which should prevent this issue from happening in the future.