ASP.Net User activity tracking in database

Question

This is about a simple yet efficient activity logging framework that I want to integrate with my existing ASP.Net based web-app (I've a LINQ-to-SQL based SQL DB as backend). I'm using something like a service-architecture to perform DB operations - that is invoke relevant LINQ operations. I've a service class for almost every entity (i.e. DB table) and it handles the CRUD operations.

In general, I need to track activities like - Mr.X added a new Item, My.Y searched on this filter, Mr.Z exported the result of Grid to an excel document, etc... and similar simple operation based logging (field-level logging is far for now)

So, here's what I've found in my two days of R&D on the SO, other forums and the web:

Approach 1: Simple old way of using two tables: Activity (stores ALL the activities along with its actor) & ActivityType (lists types of activities). I've a service layer so either I can have a "ServieBase" class which taps ALL the CRUD events and logs the one in which I'm interested. Everything is handled from within the code.

Example: http://dotnetslackers.com/articles/aspnet/Tracking-User-Activity.aspx

Approach 2: Use database TRIGGERs to tap events at table level and then perform logging. This will be completely 'abstract' to the app. I've "LastModifiedBy" field in each table so I'll get the 'actor' data and I can do the logging but this might limit me to DB-operations & need me to track other app-activities separately. but if its worth I can consider it.

Approach 3: (conceptual, need more guidance)

3.1 MVC approach - We're thinking of adopting MVC in future and I've found some efficient logging tricks in MVC like - (is ther anything like that for traditional L2S based web-app?)

Log User Activity on ASP.NET MVC Application Track user activity/actions for an asp.net mvc website?

3.2 Tracking Services I came across a 'tracking service' feature in windows - is there its web-equivalent?

http://msdn.microsoft.com/en-us/magazine/cc163466.aspx http://www.codeproject.com/KB/WF/WWF__Tracking_Service.aspx?msg=2879654

3.3 Misc - Some other options which I came across but don't seem too convincing or I'd better say they do their work but not mine :-)

Ref -

http://learn.iis.net/page.aspx/480/sample-web-analytics-tracking-module/

SQL Profiler: https://web.archive.org/web/1/http://articles.techrepublic%2ecom%2ecom/5100-10878_11-5054787.html http://technet.microsoft.com/en-us/library/cc966515.aspx

So, what say? Any suggestions and new thoughts are welcome. For now, it seems I'd land somewhere between the first-two approaches because we want it to be easy in future to be able to add any extra activity to be logged.

Thank you.

Answer 1

Here's another item to add to your toolkit: Sql Server Change Tracking. But it won't do everything you're looking for here. You might take a look at the Command pattern. I would create an interface, ITrackedCommand, and then implement those as the commands that the user can perform. Then, each one is executed through a command dispatcher which automatically calls ITrackedCommand.Log. I think that will get you where you need to go.

Answer 2

I ended up using among the first two approaches because they seem more simple and easier. Most of all they provide more control and allow to keep thing as flexible as required (i.e. its near to the actual backend coding which makes it more accessible)

I'm marking it as there has been no other extensive reviews.