The .NET Framework 2.0+ comes with a set of functionality for easily building security enforced web applications mainly with the ASP.NET Membership API and the Security web controls. The Membership class has most of the feature one would need with user management, and because it is based on a Provider Model you can write a provider for any Database you wish.

While working on an intranet application I often faced the following situation. Several people were online, logged in, when I needed to upload a new version of the web application. Because I personally know all the people who have access to this application it’s important for me to see who is online so I can announce them before taking the application down and uploading a new version.

The MembershipProvider Class exposes the GetNumberOfUsersOnline() method which returns the number of users online apparently. Well theoretically there is no 100% true way to tell who is online, because HTTP is a stateless protocol and it works simply on a Request-Response basis. There is no guarantee that somebody is still there in 5 seconds after the Response has ended.

Therefore the MembershipProvider can be set to consider a user as online for a certain amount of minutes after the last request. The aspnet_Users table contains a column which stores the LastActivityDate for each user which is then compared to the current time – the UserIsOnlineTimeWindow Property that is set in your web.config file.

This is all very nice and useful except it’s not enough for me. It’s not enough to know that there are 7 people online, I would like to show a list of users online, I would like to know who exactly is online and interesting enough … there is no implementation for this.

So it wouldn’t be a big deal to write a little code to replicate the same functionality to return the usernames aswell. For sake of speed, simplicity and because it’s the most accessible at the moment I’m using an Entity Data Model which is generated for a standard Database (which has been prepared for ASP.NET Application Services with aspnet_regsql.exe). So I won’t be writing any SQL manually, I’ll just use LINQ to Entities to query the data model.

Entities entities = new Entities();
IEnumerable<aspnet_Users> users = entities.aspnet_Users.Include("aspnet_Membership");
var result = (from usr in users
              select new
              {
                  UserID = usr.UserId,
                  Username = usr.UserName,
                  LastActivityDate = usr.LastActivityDate
              }).AsQueryable()
                .Where(u => DateTime.Now.Subtract(u.LastLoginDate).TotalMinutes < 10);

Although everything seems fine, interestingly this doesn’t yield the expected results. And after you take a look at the stored procedure that the  SqlMembershipProvider executes when you call GetNumberOfUsersOnline() it’s not difficult to understand why.

CREATE PROCEDURE [dbo].[aspnet_Membership_GetNumberOfUsersOnline]
    @ApplicationName            nvarchar(256),
    @MinutesSinceLastInActive   int,
    @CurrentTimeUtc             datetime
AS
BEGIN
    DECLARE @DateActive datetime
    SELECT  @DateActive = DATEADD(minute,  -(@MinutesSinceLastInActive), @CurrentTimeUtc)

    DECLARE @NumOnline int
    SELECT  @NumOnline = COUNT(*)
    FROM    dbo.aspnet_Users u(NOLOCK),
            dbo.aspnet_Applications a(NOLOCK),
            dbo.aspnet_Membership m(NOLOCK)
    WHERE   u.ApplicationId = a.ApplicationId                  AND
            LastActivityDate > @DateActive                     AND
            a.LoweredApplicationName = LOWER(@ApplicationName) AND
            u.UserId = m.UserId
    RETURN(@NumOnline)
END
GO

When you take a look at the CurrentTimeUtc declaration everything becomes clear. Because SQL Server and IIS instances may not run from the exact same computer the result of DateTime.Now can be very far apart (imagine the web server in America and the SQL Server in Australia –> totally different time zones –> totally different values for DateTime.Now). Also to avoid any sort of confusion @MinutesSinceLastInActive represents the UserIsOnlineTimeWindow property.

To show a list of users online only slight modification is needed to achieve the desired results, you only need to call ToUniversalTime() on DateTime.Now because this is how SQL Servers stores the LastActivityDate.

Entities entities = new Entities();
IEnumerable<aspnet_Users> users = entities.aspnet_Users.Include("aspnet_Membership");
var result = (from usr in users
              select new
              {
                  UserID = usr.UserId,
                  Username = usr.UserName,
                  LastActivityDate = usr.LastActivityDate
              }).AsQueryable()
                .Where(u => DateTime.Now.ToUniversalTime().Subtract(u.LastLoginDate).TotalMinutes < 10);

gv.DataSource = rez.OrderByDescending(u => u.LastActivityDate);
gv.DataBind();

Binding this result to the following GridView will display the list of the users online.

<asp:GridView ID="gv" runat="server" AutoGenerateColumns="false">
    <Columns>
        <asp:BoundField DataField="Username" HeaderText="Username" />
        <asp:BoundField DataField="LastActivityDate" HeaderText="Last activity date" />
    </Columns>
</asp:GridView>

Throughout my example I’ve used the value of 10 for the UserIsOnlineTimeWindow property, but this could come directly from web.config, from an appSetting or anything convenient to you.

Any feedback is welcome, I appreciate all of your comments, questions and opinions.