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.
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
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:
protected void LogActivity(string activity, bool recordPageUrl)
{
if (Request.IsAuthenticated)
{
// Get information about the currently logged on user
MembershipUser currentUser = Membership.GetUser(false);
if (currentUser != null)
{
Guid userId = (Guid)currentUser.ProviderUserKey;
// Log the activity in the database
using (SqlConnection myConnection = new
SqlConnection(ConfigurationManager.
ConnectionStrings["MembershipConnectionString"].ConnectionString))
{
SqlCommand myCommand = new SqlCommand();
myCommand.CommandText = "usp_LogUserActivity";
myCommand.CommandType = CommandType.StoredProcedure;
myCommand.Connection = myConnection;
myCommand.Parameters.AddWithValue("@UserId", userId);
myCommand.Parameters.AddWithValue("@Activity", activity);
if (recordPageUrl)
myCommand.Parameters.AddWithValue("@PageUrl", Request.RawUrl);
else
myCommand.Parameters.AddWithValue("@PageUrl", DBNull.Value);
myConnection.Open();
myCommand.ExecuteNonQuery();
myConnection.Close();
}
}
}
}
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.
ALTER PROCEDURE dbo.usp_LogUserActivity
(
@UserId uniqueidentifier,
@Activity nvarchar(100),
@PageUrl nvarchar(100)
)
AS
BEGIN TRY
BEGIN TRANSACTION -- Start the transaction
DECLARE @CurrentTimeUtc datetime
SET @CurrentTimeUtc = getutcdate()
-- Update the LastActivityDate in aspnet_Users
UPDATE dbo.aspnet_Users
SET LastActivityDate = @CurrentTimeUtc
WHERE @UserId = UserId
-- Insert activity record for user
INSERT INTO ActivityLog(UserId, Activity, PageUrl, ActivityDate)
VALUES(@UserId, @Activity, @PageUrl, @CurrentTimeUtc)
-- If we reach here, success!
COMMIT
END TRY
BEGIN CATCH
-- Whoops, there was an error
IF @@TRANCOUNT > 0
ROLLBACK
-- Raise an error with the details of the exception
DECLARE @ErrMsg nvarchar(4000), @ErrSeverity int
SELECT @ErrMsg = ERROR_MESSAGE(),
@ErrSeverity = ERROR_SEVERITY()
RAISERROR(@ErrMsg, @ErrSeverity, 1)
END CATCH
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:
protected void Page_Load(object sender, EventArgs e)
{
if (!Page.IsPostBack)
{
base.LogActivity("Visiting the homepage...", true);
}
}
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.
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.
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.
ALTER PROCEDURE dbo.usp_GetUserActivityLog
(
@UserID uniqueidentifier
)
AS
DECLARE @CurrentDateUtc datetime
SET @CurrentDateUtc = getutcdate()
SELECT ActivityLogID, Activity, PageUrl, ActivityDate, @CurrentDateUtc as CurrentDate
FROM ActivityLog
WHERE UserID = @UserID
ORDER BY ActivityDate DESC
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.
ALTER PROCEDURE
dbo.usp_GetCurrentActivityForOnlineUsers
(
@MinutesSinceLastInactive int
)
AS
DECLARE @CurrentTimeUtc datetime
SET @CurrentTimeUtc = getutcdate()
DECLARE @DateActive datetime
SELECT @DateActive = DATEADD(minute, -(@MinutesSinceLastInactive), @CurrentTimeUtc)
SELECT act.UserId,
u.UserName,
act.Activity,
act.PageUrl,
act.ActivityDate,
@CurrentTimeUtc as CurrentDate
FROM dbo.aspnet_Users u(NOLOCK)
INNER JOIN dbo.ActivityLog act(NOLOCK) ON
act.UserId = u.UserId
WHERE u.LastActivityDate > @DateActive AND
act.ActivityDate = u.LastActivityDate
ORDER BY act.ActivityDate DESC
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!
Reference:
