hi,
happy new year
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 http://www.google.com/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 http://aspnet.4guysfromrolla.com/articles/120705-1.aspx makes it easy to create and manage
user accounts. Many websites that use Membership are configured to use http://msdn.microsoft.com/en-us/library/system.web.security.sqlmembershipprovider.aspx, 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 http://www.microsoft.com/express/sql/default.aspx 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 http://www.asp.net/learn/security/.
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 http://msdn.microsoft.com/en-%0A%0Aus/library/system.web.security.membershipuser.lastactivitydate.aspx that records this
information; the SqlMembershipProvider stores this value in the aspnet_Users table's LastActivityDate column in http://en.wikipedia.org/wiki/Coordinated_universal_time This
LastActivityDate value is automatically updated whenever a user signs in and whenever their user account information is
accessed via the http://msdn.microsoft.com/en-%0A%0Aus/library/system.web.security.membership.getuser.aspx. The LastActivityDate is used by the
Membership system to determine how many users are online - the http://msdn.microsoft.com/en-%0A%0Aus/library/system.web.security.membership.getnumberofusersonline.aspx 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 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 http://aspnet.4guysfromrolla.com/articles/072705-1.aspx and http://www.4guysfromrolla.com/webtech/041906-1.shtml.
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. 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 http://aspnet.4guysfromrolla.com/articles/031506-1.aspx.
Figure 2: 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.
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
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.
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
- http://www.asp.net/learn/security/
- http://aspnet.4guysfromrolla.com/articles/120705-1.aspx
- http://aspnet.4guysfromrolla.com/articles/072705-1.aspx
- http://www.4guysfromrolla.com/webtech/041906-1.shtml
- http://aspnet.4guysfromrolla.com/articles/041305-1.aspx