Question querying audit data

[QA Eric]

Our company uses the built-in auditing tables provided by Progress (_aud-audit-data, _aud-event, etc.). Over time, these tables have acquired an extensive number of records (in the hundreds of millions). One of the problems we are encountering is performing efficient audit log searches. For example, one of our main criteria when searching is date. The _aud-audit-data table stores datetime together in a single field. This means that whenever we perform a search on the table, we have to use a range query which means it stops using the index. Other than archiving old records and decreasing table size, is there a better way to query these tables by date that would continue to use an index?

 

[TheMadDBA]

Best practice is to occasionally archive the audit data into your own database. This solves the problem of having large amounts of audit data in your production data as well as being able to index those tables in a more appropriate manner.

 

[Rob Fitzpatrick]

And if you do as TheMadDBA suggests and archive this data on a regular basis (which can be automated), you can do all your audit reporting from the archive DB instead of prod. Then you can deactivate most of the audit indexes in prod. This helps reduce the I/O impact of audit writes.