Implementing Audit Trail for your application
November 2, 2009
Posted by on
I recently came across Davy’s post where he talks about implementing auditing. Davy talks about 3 requirements which I guess are quite common for audit trail:
1) We should be able to easily retrieve what user X changed in the database on day Y or during a certain time frame.
2) We need to know what record X looked like at point Y in time, and be able to show a historic list of changes for a specific record.
3) We need to configure the duration for which auditing data must be kept in the database on a per-table (or per-schema) level
In this post I would like to talk about the approach we took for one of our recent projects. We prepared a generic sort of table which would hold the audit data. This table mainly consists of AuditID, EntityID, EntityTypeID, UserID, AuditTime, AuditLevel & AuditData. AuditData is a TEXT column (we support SQL 2000) holding XML structure, for those changes made to an entity by a given user in form of old and new values, at a level specified. Level here is just an entry (capturing type of modification I/D/U) or a detail of old / new values. This would cater to the first requirement as it’s quite easy to search for activities of a given user.
Second requirement is little tough, as with this model we don’t allow direct queries against record attributes. But this can be achieved by 2 variants. First simple solution could be you have to search by entity type & a specific entity in turn for a date range & browse through records to see changes done on them. Second option is take this AuditData out and restructure its layout with help of OLAP cubes to produce meaningful quick search.
Third requirement is very much there for us in order to control size of this massive table but we for simplicity purge entire database at a given time interval & do not make it adjustable for each entity. Though I feel providing that flexibility shouldn’t be difficult considering the design we have laid out.
A final thing I wanted to highlight is about generating the XML data itself. It’s always better to do such processing in .NET code & if you have been using DataSets you get old values extraction feature out of box. If not a dataset many O/R mappers too allow to access old and new values. In case you are rolling your own repositories with domain model not capturing the old values, you would need to design a custom approach like storing original object in session and comparing it with the postback. We are yet to see a significant performance degradation due to this design but as a contingency plan I was thinking of making this XML conversion & insertion into AuditDB an asynchronous activity with help of Queue like data structure.
Look forward to read your thoughts on above.