Niraj Bhatt – Architect's Blog

Ruminations on .NET, Architecture & Design

Implementing Audit Trail for your application

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.

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: