Enterprise Data Auditing

Question

Our current enterprise solution is a ASP.NET MVC application driven by the Entity Framework. There are a couple links on how to hook into the change events for auditing. I'm not really interested in this.

I'm interested in enterprise level auditing architecture. Those of you with the enterprise level battle wounds, what has been your auditing solutions? Do you serialize objects in databases in a framework. Are you setting up database triggers to audit tables? Do you use a separate database all together so your audit growth doesn't affect your app database? I'm interested in the tried and true solutions here. I know there's options in our technology choice (EF) but I'm interested in the foundation first.

Links would be very appreciated. Thanks!

Answer 1

I don't have any links, but in the system that I have the joy of maintaining here at the day job. We have a single audit table, that basically stores the following information.

TableName, PrimaryKeyValue, ModifiedColumn, OldValue, NewValue, ChangeUser, Change Date

Now, this works great for audit speed, in our code, we have a common interface for auto-implementing the audit logging, but from a "review" standpoint, it isn't the "fastest" way to get the information back out. (Granted we have not actually done anything to need to look at the audit log...)

Answer 2

I've seen several solutions, but my favorite one was simplicity itself:

• Create audit tables that mirror each source table, adding a few extra columns to track the date and type of change (insert, update, or delete if you support it) and the user making the change. Remove all constraints and indexes (unless you expect to be doing a lot of searches).

• Inside of the table update logic (we used procedures, but there's no reason it couldn't be done with an OR/M or other persistence layer, given the appropriate hooks), write to both the source table and the audit table.

This has numerous benefits, but the biggest one (in my opinion) is not having to worry about or write all the code to manage the transactional integrity of the paired write operations in the client.

Answer 3

We recently had to solve this same problem in our enterprise. We were required to be able to revert back to prior versions too.

We ended up auditing the business entities rather than the tables in sql. We basically serialize the records in the DB and keep track for the changes that are made from one version to the next. This approach allows us to retreive previous versions into the business entities and then revert back by calling the same save operations. This functionality to revert back will be shifted on the applications responsibility because it must be solved here otherwise our service might need to know about too many details about the participating applications. Serivce Operations to retrieve records by versions, by dates, view history, and of course to audit changes are provided. Its an opt-in approach for different application groups and different entities within (not everything in the DB needs to be auditted so why do that).

We then build a lightweight website that talks to the service and can display all the versions. We built a mechanism to show the additions/updates/deletions to compare between versions (really cool ui representation) this allows users to see who changed what and when. The service can send back a link to the url to view the versions of an entity. This allows our webaps + winform/wpf apps to launch a browser so users can see the changes.

Maybe I can package this up and provide if anyone is interested....