title: ASP.Net User activity tracking in database
body: <p>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.</p>
  <blockquote>
  <p>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)</p>
  </blockquote>
  <p>So, here's what I've found in my two days of R&amp;D on the SO, other forums and the web:</p>
  <p><strong>Approach 1:</strong>
  Simple old way of using two tables: Activity (stores ALL the activities along with its actor) &amp; ActivityType (lists types of activities). I've a service layer so either I can have a &quot;ServieBase&quot; class which taps ALL the CRUD events and logs the one in which I'm interested. Everything is handled from within the code.</p>
  <blockquote>
  <p>Example:
  <a href="http://dotnetslackers.com/articles/aspnet/Tracking-User-Activity.aspx" rel="noreferrer">http://dotnetslackers.com/articles/aspnet/Tracking-User-Activity.aspx</a></p>
  </blockquote>
  <p><strong>Approach 2:</strong>
  Use database TRIGGERs to tap events at table level and then perform logging. This will be completely 'abstract' to the app. I've &quot;LastModifiedBy&quot; 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 &amp; need me to track other app-activities separately. but if its worth I can consider it.</p>
  <p><strong>Approach 3:</strong> (conceptual, need more guidance)</p>
  <p><strong>3.1 MVC approach</strong> - 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?)</p>
  <p><a href="https://stackoverflow.com/questions/1822754/log-user-activity-on-asp-net-mvc-application">Log User Activity on ASP.NET MVC Application</a>
  <a href="https://stackoverflow.com/questions/1514616/track-user-activity-actions-for-an-asp-net-mvc-website">Track user activity/actions for an asp.net mvc website?</a></p>
  <p><strong>3.2 Tracking Services</strong> I came across a 'tracking service' feature in windows - is there its web-equivalent?</p>
  <blockquote>
  <p><a href="http://msdn.microsoft.com/en-us/magazine/cc163466.aspx" rel="noreferrer">http://msdn.microsoft.com/en-us/magazine/cc163466.aspx</a>
  <a href="http://www.codeproject.com/KB/WF/WWF__Tracking_Service.aspx?msg=2879654" rel="noreferrer">http://www.codeproject.com/KB/WF/WWF__Tracking_Service.aspx?msg=2879654</a></p>
  </blockquote>
  <p><strong>3.3 Misc</strong> - 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 :-)</p>
  <p>Ref -</p>
  <p><a href="http://learn.iis.net/page.aspx/480/sample-web-analytics-tracking-module/" rel="noreferrer">http://learn.iis.net/page.aspx/480/sample-web-analytics-tracking-module/</a></p>
  <blockquote>
  <p>SQL Profiler:
  <a href="https://web.archive.org/web/1/http://articles.techrepublic%2ecom%2ecom/5100-10878_11-5054787.html" rel="noreferrer">https://web.archive.org/web/1/http://articles.techrepublic%2ecom%2ecom/5100-10878_11-5054787.html</a>
  <a href="http://technet.microsoft.com/en-us/library/cc966515.aspx" rel="noreferrer">http://technet.microsoft.com/en-us/library/cc966515.aspx</a></p>
  </blockquote>
  <p>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.</p>
  <p>Thank you.</p>