Published: 05 Nov 2008
By: Scott Mitchell
Download Sample Code

Scott Mitchell talks about tracking user activity in web applications.

Contents [hide]

Introduction

I like data. I go gaga over measurable metrics. Nothing makes me happier than storing information and then seeing it expressed in tables of numbers and colorful charts. Whenever I work on a web application I am always looking for interesting data to record and analyze, and the most interesting (and potentially profitable) data that every website owner should track are usage statistics. Web server log files and online tools like Google Analytics provide an array of useful metrics, including how many unique visitors accessed your site, what pages were the most popular, what days of the week and hours of the day represent peak demand, and so forth.

Many ASP.NET web applications support user accounts, enabling visitors to create an account and sign in to the site. With a little bit of effort you can track the activity of your logged on users. This can include recording activities such as what pages were visited as well as what actions were performed. Consider a page that allows a user to manage his profile. When first arriving at this page the activity log might add an entry like "Visiting the User Profile page." After updating his e-mail address, the activity log might record, "Changed e-mail address from scott@example.com to mitchell@example.com." Such usage tracking offers a deeper level of analysis than is possible with log files or online visitor statistic tools. Instead of data that report total number of visitors or how the average user is interacting with the site, user activity tracking can provide a very detailed view of how a particular individual is using the application and what actions he is performing while signed on to the site.

This article examines how to record your users' activities in a database table and display this information in a web page. A complete, working demo application that shows these techniques in action is available for download.

ASP.NET's Membership system makes it easy to create and manage user accounts. Many websites that use Membership are configured to use SqlMembershipProvider, a Membership provider that ships with the .NET Framework and stores user account information in a Microsoft SQL Server database. The demo application for this article uses SqlMembershipProvider, storing user account information along with the user activity log in a Microsoft SQL Server 2008 Express Edition database file (ASPNETDB.mdf), which you will find in the application's App_Data folder. For more information on using the Membership system refer to my ASP.NET Web Security tutorial series.

A Look at the Membership System's User Tracking Implementation

One of the lesser known features of ASP.NET's Membership system is that it has a built-in mechanism to track the last date and time each user has accessed the system. Each user account has a LastActivityDate property that records this information; the SqlMembershipProvider stores this value in the aspnet_Users table's LastActivityDate column in Coordinated Universal Time (UTC). This LastActivityDate value is automatically updated whenever a user signs in and whenever their user account information is accessed via the Membership.GetUser method. The LastActivityDate is used by the Membership system to determine how many users are online - the Membership.GetNumberOfUsersOnline method returns the number of users whose LastActivityDate is within a certain window of the current date and time (15 minutes, by default).

The Membership system's user tracking implementation is pretty limited as it only specifies the last date and time a user was active on the site. It does not indicate what the user was doing at that time or provide any sort of activity history. The activity logging system presented in this article overcomes these limitations.

Designing the User Activity Log Database Table

The first step in building any analysis tool is to determine what information to track. Different website usage analytic tools capture different information: web server logs typically record the filename of each requested page, the querystring, the date and time, and the HTTP status code, whereas online tools capture information of interest to the sales and marketing departments: visit durations, the geographical locations of the site's visitors, the number of unique visitors, entry and exit pages, and so on.

What information do we need to record when tracking the online activity of a website's logged on users? At a minimum we would need to log:

  • The activity being performed
  • The user performing the activity
  • The date and time of activity
  • The page being visited when the activity is performed

This information can be modeled in a single database table. Figure 1 shows such a table, which I've named ActivityLog. This table contains one record for each activity recorded for each user on the site.

Figure 1: The ActivityLog table models the activity log.

The ActivityLog table models the activity log.

The ActivityLogID is of type uniqueidentifier and uniquely identifies each log entry. The UserId column identifies the user who performed the activity. (The UserId column in the aspnet_Users table is what uniquely identifies each user in the SqlMembershipProvider user store.) The Activity column describes the activity performed; PageUrl is the URL of the page where the activity was performed. Finally, ActivityDate is the date and time (in UTC) that the activity was performed.

The ActivityLog table is designed to have a record added for each activity performed by the user. Depending on the popularity of your website, this table can grow to include tens of thousands if not millions of records. You may want to consider implementing some mechanism to remove records older than a certain date, such as all activity log entries more than three months old. This could be accomplished by a SQL Job that executes nightly.

Logging User Activity

Web server log files automatically record each requested page; online usage statistic tools use cookies to track the pages users visit. Both of these logging mechanisms, once setup and configured, track visits to the site automatically without needed intervention from the web developer. The activity log is more flexible as it can be used to track any "activity," which may be page visits or user-instigated actions. Therefore, logging a user activity to the database involves writing code.

To help facilitate this process I created a custom base page class named BasePage that extends the System.Web.UI.Page class. BasePage includes a method named LogActivity(activity, recordPageUrl) that writes a record to the ActivityLog table with the specified activity and, if specified, the URL of the currently visited page.

The LogActivity method's code follows:

The method starts by determining if the user visiting the page is authenticated. If so, it gets the user's information via the Membership class's GetUser method. If a user is returned, the stored procedure usp_LogUserActivity is called, passing in values for the @UserId, @Activity, and @PageUrl parameters. Note that if recordPageUrl is false, the @PageUrl parameter is set to a database NULL value; if it is true, the @PageUrl parameter is assigned the raw URL of the currently requested page, which includes the directories, filename, and querystring of the requested web page (i.e., /MyApp/Users/Default.aspx?ID=2).

The usp_LogUserActivity stored procedure starts by updating the LastActivityDate column in the aspnet_Users table. As a result, adding an entry to the user activity log is tantamount to retrieving the user's information through the Membership system. Following the update to the LastActivityDate, the usp_LogUserActivity stored procedure inserts a record into the ActivityLog table. This update and insert are atomic as they are performed under the umbrella of a transaction. For background on transactions and using SQL Server's TRY...CATCH blocks see Maintaining Database Consistency with Transactions and TRY...CATCH in SQL Server.

With the BasePage class complete, the final step is to have the ASP.NET pages in the site derive from BasePage (rather than from System.Web.UI.Page). Once this has been done you can call the LogActivity method from any page. For example, the homepage (~/Default.aspx) has the following code for its Page_Load event handler:

The LogActivity method can be called from any event handler in those ASP.NET pages that derive from BasePage. Call LogActivity from the Page_Load event handler to log information when a page is first visited. You can additionally call LogActivity when the user clicks a button to log that they've performed a particular action (such as editing or deleting a record from some database table). All of the ASP.NET pages in the demo application derive from the BasePage class, and most include at least one call to the LogActivity method.

Displaying a Particular User's Activity History

The activity log provides a complete history of each user's activity on the site. The demo application includes a page named ActivityHistoryByUser.aspx that displays the complete history in a paged grid for a particular user. Figure 2 shows a screen shot of ActivityHistoryByUser.aspx in action.

The GridView controls used in the web pages in this tutorial use default paging, which is easy to implement but inefficient when paging through a large number of records. Because the activity log may contain thousands of records for each user account, the GridView controls should be retooled to use custom paging unless some mechanism is put into place to periodically cull old activity log entries from the table or if only recent activity log records are displayed in the grid. For more information on custom paging see Custom Paging in ASP.NET.

Figure 2: Scott's activity history is displayed in a grid.

Scott's activity history is displayed in a grid.

Each activity in the left column is displayed as a hyperlink that links to the activity's PageUrl value (if such a value exists). The Last Updated column shows the date the activity was performed. For activities older than a week, the date the activity was performed is displayed. If the activity occurred more recently then a human-friendly message, such as "2 days ago" or "6 minutes ago," is displayed in place of the date and time. (This display formatting is handled by the FormatLastUpdatedDate method in the BasePage class.)

The grid is populated by the records returned from the usp_GetUserActivityLog stored procedure. As the following markup shows, this stored procedure returns the ActivityLog entries for a particular user ordered from the most recent entries to the oldest.

Viewing the Online Users and their Last Performed Activity

Many websites that support user accounts have a page that lists what users are currently online and what activity they last performed. As noted earlier, the Membership system automatically records each user's last active date and time and provides a method for determining how many users have been active within a specified time window. The Membership's built-in system does not include what activity the user last performed, but this information is captured by the ActivityLog table.

I added a stored procedure to the database named usp_GetCurrentActivityForOnlineUsers that returns the list of users who are currently online along with their most recent activity. This stored procedure takes in a single input parameter, @MinutesSinceLastInactive, which is the number of minutes that has elapsed since a user has been active in the system and is still considered "online." For example, a value of 15 means that any user whose LastActivityDate is within 15 minutes of the current date and time is considered "online," whereas those users whose LastActivityDate is outside of this window are considered "offline."

The usp_GetCurrentActivityForOnlineUsers stored procedure starts by determining what time is the cutoff for a user to be considered "online." It then queries the aspnet_Users and ActivityLog tables to retrieve the username of online users along with information about their last activity.

The WhoIsOnline.aspx page displays the results from this stored procedure in a grid (see Figure 3).

Figure 3: Those users currently online are listed along with their most recent activity.

Those users currently online are listed along with their most recent activity.

Conclusion

Web server log files and online usage analysis tools are helpful in determining and evaluating macroscopic usage patterns. Unfortunately, these tools cannot provide a detailed view of how a particular user is interacting with your site. Nor can they provide live, up to the second activity information that can be used in your application to show who is currently online and what they are doing. Such deep usage analysis and real-time statistics are possible on websites that support user accounts.

ASP.NET's Membership system greatly simplifies the process of setting up, creating, and managing user accounts. However, the Membership system only tracks when each user's last activity was on the site; it does not log the activity performed or maintain an activity history. As examined in this article, it is possible to build your own user activity log with a little bit of elbow grease.

Happy Programming!

References

<<  Previous Article Continue reading and see our next or previous articles Next Article >>

About Scott Mitchell

Scott Mitchell, author of eight ASP/ASP.NET books and founder of 4GuysFromRolla.com, has been working with Microsoft Web technologies since 1998. Scott works as an independent consultant, trainer, ...

This author has published 16 articles on DotNetSlackers. View other articles or the complete profile here.

Other articles in this category


Code First Approach using Entity Framework 4.1, Inversion of Control, Unity Framework, Repository and Unit of Work Patterns, and MVC3 Razor View
A detailed introduction about the code first approach using Entity Framework 4.1, Inversion of Contr...
jQuery Mobile ListView
In this article, we're going to look at what JQuery Mobile uses to represent lists, and how capable ...
Exception Handling and .Net (A practical approach)
Error Handling has always been crucial for an application in a number of ways. It may affect the exe...
JQuery Mobile Widgets Overview
An overview of widgets in jQuery Mobile.
Book Review: SignalR: Real-time Application Development
A book review of SignalR by Simone.

You might also be interested in the following related blog posts


Announcing Visual Studio 2010 and .NET FX 4 Beta 2 read more
Adding users to a TFS project when youre not on the domain read more
Creating a Filtering User Interface With jQuery In a Web Forms Application: Part 1 read more
Silverlight 3 Activity control read more
A proposed introduction... read more
Unit Testing - Do Repeat Yourself read more
OpenId Part Three: Authorization read more
Range-Specific Requests in ASP.NET read more
Understanding Reverse Proxy Servers and the Mailman read more
Update to Logging in to DotNetNuke from a Silverlight Application with RIA Authentication read more
Top
 
 
 

Discussion


Subject Author Date
placeholder How to Use it ?? Sreekanth CHAVA 6/29/2010 12:13 PM
RE: How to Use it ?? Sonu Kapoor 7/6/2010 9:11 AM
placeholder ActivitylogID Eugene Choi 8/18/2010 2:46 PM
RE: ActivitylogID Eugene Choi 8/18/2010 3:16 PM
placeholder PageUrl attribute of ActivityLog table should become a recommendation page as user logins for second time Aejaaz Ahmed 5/13/2013 9:45 PM
At last, someone who can explain things right Dimitris Pantazopoulos 6/4/2013 3:38 AM
placeholder RE: At last, someone who can explain things right Sonu Kapoor 6/4/2013 8:49 AM
RE: RE: At last, someone who can explain things right Dimitris Pantazopoulos 6/8/2013 6:10 AM
placeholder any example with mysql? Angelo C 11/18/2013 2:27 PM
How to use it with SQL Server 2005 Ghaffar Abdul 11/21/2008 5:28 AM
placeholder RE: How to use it with SQL Server 2005 Sonu Kapoor 11/21/2008 11:54 AM
thanks for the reply ... but scripct? Ghaffar Abdul 11/22/2008 2:18 AM
placeholder RE: thanks for the reply ... but scripct? Scott Mitchell 11/22/2008 3:00 PM
RE: RE: thanks for the reply ... but scripct? Ghaffar Abdul 11/25/2008 12:53 AM
placeholder Excellent Barbara Lee 11/23/2008 1:03 PM
RE: Excellent Scott Mitchell 11/23/2008 7:41 PM
placeholder ??? Barbara Lee 11/23/2008 1:05 PM
RE: Excellent Scott Mitchell 11/23/2008 7:42 PM
placeholder Nice work Sujith PV 11/25/2008 11:35 PM
Great approach... Where to find mroe details... Zachary Hunter 12/2/2008 1:28 PM
placeholder RE: Great approach... Where to find mroe details... Sonu Kapoor 12/2/2008 1:29 PM
Very useful R Mann 12/9/2008 2:57 PM
placeholder RE: Very useful Scott Mitchell 12/9/2008 3:55 PM
How to use it in SQL Server 2005 Salman Behera 12/11/2008 3:01 AM
placeholder RE: How to use it in SQL Server 2005 Salman Behera 1/2/2009 3:09 AM
RE: RE: How to use it in SQL Server 2005 Salman Behera 1/2/2009 3:28 AM
placeholder RE: How to use it in SQL Server 2005 Scott Mitchell 1/2/2009 12:43 PM
vb code? Michael Allgier 3/21/2009 1:41 PM
placeholder RE: vb code? Scott Mitchell 3/21/2009 1:46 PM
How to use it with Windows authentication Kshokry Kshokry 4/15/2009 6:47 AM
placeholder RE: How to use it with Windows authentication Sonu Kapoor 4/15/2009 7:25 AM

Please login to rate or to leave a comment.