Another Audit Trail question

Another Audit Trail question

Old forum URL: forums.lhotka.net/forums/t/5806.aspx


mikemir8 posted on Friday, November 14, 2008

Hi all,
I have a requirement to implement an audit trail for many of my business objects (not all), and as a result of that I've been doing some reading about the subject. Here in the forum I've found some interesting threads about it.

Anyway, right now I'm leaning towards creating an Auditor static class following the Observer pattern. This class would be in charge of listening for object updates and, as required, creating the AuditTrailRecord for each change.

A few questions have started to pop up:

* Since I only want to log some of the business object and not all of them, it would be the responsibility of each audited business object to register with the Auditor class. Also the audited class needs to implement an IAuditable interface. Right now the most appropriate place to do this seems to be the constructor of the audited class. It probably just one line like Auditor.Register(this);. Anyone feels this might not be the best place?

* What I need to log is the kind of change on the object (create, update, delete) along with the changed properties. For the properties, all I need to do is listen to the PropertyChanged event. However, I'm not sure about the change itself. Originally I had thought about the Saved event. However, this event is not raised for child objects, and it does not provide enough info about the kind of change. Since I'm already defining an interface, I suppose I could create an Updating event that would provide the info I need. Does this sound like the best approach?

Any comments are appreciated.

- Miguel

JoeFallon1 replied on Monday, November 17, 2008

Any reason you can't implement this at the database level? I have audit tables that I populate using triggers on the main tables.

Joe

 

Kevin Fairclough replied on Monday, November 17, 2008

You might want to look at AOP (Aspect Oriented Programming),  I can remember reading something about it when looking at the Spring.NET framework, I'm pretty sure there was a logging aspect, which can be applied in configuration.  I think it uses intercepts (before/after method calls), the CSLA BO need not have any logging code inside it.  I've not used it, but it is where I would look for things like logging.

Kevin

SonOfPirate replied on Tuesday, November 18, 2008

We currently use Spring.NET for exactly this purpose.  Do a search, there are plenty of references available.

mikemir8 replied on Tuesday, November 18, 2008

Joe,
The reason I'm not doing this at the database level is because one of my main requirements is to also log the user that made the change. Using triggers I could only have access to the database user, which is always the same for the application.

Kevin / Son,
Thanks for pointing me out to Spring and AOP. What I've read sounds pretty interesting, and I will definitely start learning about it for future projects. However, I'm too far ahead with this particular project for that. Do you have any comments regarding my initial approach?

Thanks,
- Miguel

Jack replied on Wednesday, November 19, 2008

Miguel,

Not to beat a dead horse about doing it in the database but don't you have an update_user_id column in your table?  I pass the application user id (or proxy login user) back to all insert statements and the userid/update_date from the front-end.  I can use the update_date to manage concurrency issues and the update_id for all auditing related info

I have triggers on my tables that provide a default 'USER' / update_date value if no value is provided (which ends up being the logged in user as you mentioned) but otherwise it is the 'real' user.  This lets the app handle the user id but I can still do mass updates in the database and have the audits caught.

It makes it much easier to then separate the logic of auditing outside your application which sounds like a nightmare.

What will you do if you need to do some batch processing outside the scope of the application and want to retain auditing?  You would have to re-write all that code.

jack

radteam replied on Wednesday, November 19, 2008

Miguel, Jack and others...

I have been reading with interest your discussion and would like to share my recent experience:

Why not use Microsoft Policy Injection Block to do the dirty work / method call auditing?

In my case,I had a requirement for auditing that needed to work either with WinForms or Web Applications and use Microsoft Enterprise Blocks. Unfortunately, no CSLA in the picture in that project, nevertheless it can be applied to CSLA, just like any other Microsoft Blocks!

So I have created my homegrown "RADteam.Framework.Handlers" that use Microsoft Enterprise Blocks, e.g. Policy Injection, Audit and Data blocks that work with a SQL Server database (what I call the Auditing database) with a few stored procedures (could be any other database).

For easy configuration, the "handlers" rely on the Enterprise library configuration tool. The rules that you define will determine which method calls will get hooked! and which properties you want to track. I even permitted to add custom messages, not just the usual old value / new value data.

In order to utilize the Injection Block, any object you need to "inject" just needs to be instantiated using the PolicyInjection.Create<TObject>() call. ex:

DummyBO dummy =

Microsoft.Practices.EnterpriseLibrary.PolicyInjection.PolicyInjection.Create<Miguel.TestApp.BusinessLogic.DummyBO>().

I think this is a cool way to handle auditing! No messy application code. No custom database code / triggers etc... If anyone of you is interested, let me know! (I have detailed How-To documentation).

RADteam

 

 

whelzer replied on Thursday, November 20, 2008

Rad,

This sounds very interesting, I'd be interested in seeing your How-To doc.

Paul

ajj3085 replied on Thursday, November 20, 2008

From the name, I would think that's MS' answer to Spring.Net.  Smile [:)]

mikemir8 replied on Thursday, November 20, 2008

Jack,
Unfortunately, I don't have a user_id  for ALL the tables that I need to track. We're talking about pre-existing tables and right now it's just not feasible to go and change them. So, right now, doing it in the database is pretty much off the table.

To others who have responded,
As I mentioned, I had never heard before about AOP, and it does sound like a very interesting way of doing this type of auditing, as well as for other type of tasks. From what I understood, Microsoft Policy Injection Block also seems to be on that same track. I'll definitely start researching on that.

I'll still like to know what you think about the approach I suggested first. I haven't received any comments on that. To sum up, this is what I've thought:
Most of the work is carried by the Auditor class. What the audited classed need to do is very little then. Any opinions?

- Miguel

ajj3085 replied on Friday, November 21, 2008

Well, from a very high level, I think it's probably a good idea that you keep as much auditing logic out of the actual BOs as you can.. and since it's likely to be the same (or almost always the same behavior), consolidating it to an Auditor BO seems like a good design.  That's my five minute opinion anyway.  Smile [:)]

Jack replied on Friday, November 21, 2008

Miguel,

I think that is fine - I've done a similar implementation a long time ago (but not in CSLA).  It was a little more datacentric (PowerBuilder) so we dealt more with raw column names than the concept of property objectes.  The most annoying part about doing it in code is the tracking of the old/new values where in the database that is available in the trigger so its simpler.

I did a dynamic approach where I registered the auditable columns along with enough metadata so that I could just parse a generic list.  We stored the required auditable columns in a lookup table so we could add/remove as we pleased. 

   1) Register Object to Audit

   2) Register each column with propertyname, database column name, and events I cared about

   3) In the fetch/retrieve even I loop through all my pre-registered properties and set the original value

   4) In the I/U/D routines I checked if the object is registered for Audit and if so passed the object to the Audit routine

   5) Loop through the list of registered columns and compare the new values to old values.

   6) for any columns with changes add the audit info to the audit table

   7) Commit all the work

    8) If success then re-loop through the auditable columns an update the original values to the new values so we can track another set of changes.

It was something like that.  I'm sure you could make it fancier and use the CSLA objects and reflection and make it more dynamic.

I would also suggest that you use a simple generic stored proc to add the audit info to the table.  That way you can re-use it in the database if you need to do batch/bulk changes.

And thump the guy that didn't put basic audit columns on the tables in the first place :-)

jack

ozhug replied on Sunday, November 30, 2008

Hi RadTeam,

Can you please post a link to your how to document.

thanks

 

radteam replied on Monday, December 01, 2008

For the benefit of all I will make a download link available and update the forum with the location.

RADteam

Maqster replied on Friday, November 21, 2008

RADTeam, I'm very interested in the how-to documentation.

/maqster

Copyright (c) Marimer LLC