Who's Online Application
 
Published: 23 Sep 2003
Unedited - Community Contributed
Abstract
Article explaing a sample application that keeps track of every user that is online on a website. Source code available in C# and VB.Net.
by Alex Campbell
Feedback
Average Rating: 
Views (Total / Last 10 Days): 48273/ 117

Introduction

Introduction

I get a couple of emails a day asking how to make a page that shows which users are online at a particular web site.  This article is more in response to those emails than to a genuine engineering need to solve this problem (in fact, here are a bunch of reasons why this is a bad idea).

If you are just looking to download the code and get it running, click here to be taken to the source download page.

Data Model

Data Model

For the purposes of the application, we are going to keep the user's details in an SQL Server database, along with the URL and timestamp of every page they visit.  Thus we have two database tables:

/* tables for holding the details of each user and their clickstream */
    CREATE TABLE [dbo].[tbl_OnlineUsers] (
    [IP_Address] [varchar] (15) NOT NULL, /* max is 255.255.255.255 ie 15 chars */
    [Session_ID] [varchar] (50) NOT NULL,
    [User_Agent] [varchar] (250) NULL,
    [Referer] [varchar] (250) NULL, /* yes, it is spelt incorrectly in the HTTP standard */
    [TimeStamp] [datetime] NOT NULL
    )
    GO

    CREATE TABLE [dbo].[tbl_OnlineUsers_VisitedURLs] (
    [URL] [varchar] (250) NOT NULL, /* we don't keep querystrings for security reasons, so 250 chars should be enough*/
    [Session_ID] [varchar] (50) NOT NULL,
    [Timestamp] [datetime] NOT NULL
    )

And 7 stored procedures to manage them, but I won't describe them here as they are detailed in the DDL file here.

Real Code

Real Code

When a new user starts their session by visiting a page on our webserver, ASP.Net raises the Session_Start event, which is when we connect to the SQL Server database and add their details to the tbl_OnlineUsers table.  A timing issue does arise, however, because we also want to record what page the user starts on in the tbl_OnlineUsers_VisitedURLs table, which requires that their particular Session ID have a row in the tbl_OnlineUsers table.  Because the Application_Request event is raised before Session_Start, this would cause an error and cripple the whole application.

The solution is to use the Application_PreRequestHandlerExecute event to add the URL and timestamp to their table, because that event gets fired after Session_Start.

The third event used here is Session_End, where we remove the Session details from tbl_OnlineUsers.  Sadly, ASP.Net doesn't seem to raise this event when every session times out, so we schedule an SQL task to run at a certain time every day to clean out the old records.

global.asax.cs

using System;
using System.Web;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;

    public class Global : System.Web.HttpApplication
    {
        protected void Session_Start(Object sender, EventArgs e)
            {
                SqlConnection myConnection = new SqlConnection(ConfigurationSettings.AppSettings["YourConnString"]);
                SqlCommand myCommand = new SqlCommand("sp_AddOnlineUser", myConnection);
                myCommand.CommandType = CommandType.StoredProcedure;
                myCommand.Parameters.Add("@IP_Address", Request.ServerVariables["REMOTE_ADDR"]);
                myCommand.Parameters.Add("@Session_ID", Session.SessionID.ToString());
                myCommand.Parameters.Add("@user_Agent", Request.ServerVariables["HTTP_USER_AGENT"]);
                myCommand.Parameters.Add("@Referer", Request.ServerVariables["HTTP_REFERER"]);

             try
             {
             myConnection.Open();
             myCommand.ExecuteNonQuery();
             myConnection.Close();
                }

                catch (Exception Err)
                {
                    // do nothing but we really don't want to throw an
                    // exception here because that would likely crash the whole app
             }
            }
        protected void Application_PreRequestHandlerExecute(Object sender, EventArgs e)
            {
                SqlConnection myConnection = new SqlConnection (ConfigurationSettings.AppSettings["YourConnString"]);
                SqlCommand myCommand = new SqlCommand ("sp_AddVisitedURL", myConnection);
                myCommand.CommandType = CommandType.StoredProcedure;
                myCommand.Parameters.Add("@URL", Request.ServerVariables["SCRIPT_NAME"]);
                myCommand.Parameters.Add("@Session_ID", Session.SessionID.ToString());

                try
                {
                    myConnection.Open();
                    myCommand.ExecuteNonQuery();
                    myConnection.Close();
                }

             catch (Exception Err)
             {
            // do nothing but we really don't want to throw an
            // exception here because that would likely crash the whole app
                }
            }

        protected void Session_End(Object sender, EventArgs e)
        {
               SqlConnection myConnection = new SqlConnection (ConfigurationSettings.AppSettings["YourConnString"]);
            SqlCommand myCommand = new SqlCommand ("sp_DeleteOnlineUser", myConnection);
            myCommand.CommandType = CommandType.StoredProcedure;
            myCommand.Parameters.Add("@Session_ID", Session.SessionID.ToString());

            try
            {
                   myConnection.Open();
             myCommand.ExecuteNonQuery();
             myConnection.Close();
               }
            catch (Exception Err)
         {
         // do nothing but we really don't want to throw an
         // exception here because that would likely crash the whole app
            }
            }
    }

(click here for this page in VB.Net)

The above code puts the user's details and clickstream into the database, but exists in the global.asax code behind file and doesn't allow us to actually display that data.  The following pages solve that problem:

OnlineUsers.aspx

<%@ Page Language="C#" Src="OnlineUsers.cs" Inherits="ViewOnlineUsers" %>
<html>
<head>
    <title>
    Online Users
    </title>
</head>
<body>
    <h1>Online Users</h1>
    <ASP:Repeater id="rptOnlineUsers" runat="server">
        <ItemTemplate>
            User from <%# DataBinder.Eval(Container.DataItem, "IP_Address") %>
            <br />
            Session started at <%# DataBinder.Eval(Container.DataItem, "TimeStamp") %>
            <br />
            Using <%# DataBinder.Eval(Container.DataItem, "User_Agent") %>
            <br />
            <%# DataBinder.Eval(Container.DataItem, "Referer") %>
            <br />
            <a href="OnlineUsers_Clickstream.aspx?Session_ID=<%# DataBinder.Eval(Container.DataItem, "Session_ID") %>">Click
            here</a> to view the user's clickstream.
            <br />
        </ItemTemplate>
    </ASP:Repeater>
</body>
</html>

OnlineUsers.cs

using System;
using System.Web;
using System.Web.UI.WebControls;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;


    public class ViewOnlineUsers : System.Web.UI.Page
    {
        public Repeater     rptOnlineUsers;

        protected void Page_Load(Object sender, EventArgs e)
            {
                GetOnlineUsers();
            }

        private void GetOnlineUsers()
            {
                try
                    {
                        SqlConnection myConnection = new SqlConnection(ConfigurationSettings.AppSettings["yourConnString"]);
                        SqlDataAdapter myCommand = new SqlDataAdapter("sp_GetOnlineUsers", myConnection);
                        DataSet myDataSet = new DataSet();
                        myCommand.Fill(myDataSet, "tbl_OnlineUsers");
                        rptOnlineUsers.DataSource = myDataSet.Tables["tbl_OnlineUsers"].DefaultView;
                        rptOnlineUsers.DataBind();
                    }
                catch (Exception Err)
                    {
                    }

            }
    }

Click here for this page in VB.Net, or here to see it in action

This calls the stored procedure "sp_GetOnlineUsers" which returns every row of the tbl_OnlineUsers table, and then a Repeater is used to display the data.  A hyperlink is provided to view the clickstream of every user, which leads to the OnlineUsers_Clickstream.aspx page and sends that particular user's Session_ID in a querystring.

OnlineUsers_Clickstream.aspx

<%@ Page Language="C#" Src="OnlineUsers.cs" Inherits="ViewOnlineUsers" %>
<html>
<head>
    <title>
    Online Users
    </title>
</head>
<body>
    <h1>Online Users</h1>
    <ASP:Repeater id="rptOnlineUsers" runat="server">
        <ItemTemplate>
            User from <%# DataBinder.Eval(Container.DataItem, "IP_Address") %>
            <br />
            Session started at <%# DataBinder.Eval(Container.DataItem, "TimeStamp") %>
            <br />
            Using <%# DataBinder.Eval(Container.DataItem, "User_Agent") %>
            <br />
            <%# DataBinder.Eval(Container.DataItem, "Referer") %>
            <br />
            <a href="OnlineUsers_Clickstream.aspx?Session_ID=<%# DataBinder.Eval(Container.DataItem, "Session_ID") %>">Click
            here</a> to view the user's clickstream.
            <br />
        </ItemTemplate>
    </ASP:Repeater>
</body>
</html>

OnlineUsers_Clickstream.cs

using System;
using System.Web;
using System.Web.UI.WebControls;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;


    public class ViewOnlineUsers_ByClickstream : System.Web.UI.Page
    {
        public Repeater     rptOnlineUsers;
        public Repeater     rptOnlineUsers_ClickStream;

        protected void Page_Load(Object sender, EventArgs e)
            {
                GetOnlineUsers();
                GetClickStream();
            }

        private void GetOnlineUsers()
            {
                try
                    {
                        SqlConnection myConnection = new SqlConnection(ConfigurationSettings.AppSettings["YourConnString"]);

                        SqlDataAdapter myCommand = new SqlDataAdapter("sp_GetOnlineUsers_ByUser " + Request.QueryString["Session_ID"], myConnection);
                        DataSet myDataSet = new DataSet();
                        myCommand.Fill(myDataSet, "tbl_OnlineUsers");
                        rptOnlineUsers.DataSource = myDataSet.Tables["tbl_OnlineUsers"].DefaultView;
                        rptOnlineUsers.DataBind();
                    }

                catch (Exception Err)
                    {
                    }
            }

        private void GetClickStream()
            {
                try
                {
                    SqlConnection myConnection = new SqlConnection(ConfigurationSettings.AppSettings["YourConnString"]);
                    string sSQL = "sp_GetOnlineUsers_Clickstream " + Request.QueryString["Session_ID"];
                    SqlDataAdapter myCommand = new SqlDataAdapter(sSQL, myConnection);
                    DataSet myDataSet = new DataSet();
                    myCommand.Fill(myDataSet, "tbl_OnlineUsers");
                    rptOnlineUsers_ClickStream.DataSource = myDataSet.Tables["tbl_OnlineUsers"].DefaultView;
                    rptOnlineUsers_ClickStream.DataBind();
                }

                catch (Exception Err)
                {
                }
            }

    }

Click here for this page in VB.Net.

Cleaning Up

Cleaning Up

If the Session_End event in the global.asax codebehind isn't fired when a session ends, that session will remain in the process forever.  To fix this, we have an SQL Server task scheduled to run the sp_CleanOldOnlineUsers stored procedure every day.

    CREATE PROCEDURE sp_CleanOldOnlineUsers
    /*
    Cleans out any sessions 1 day or more old - prevents the DB from being clogged
    up with old sessions. If ASP.Net managed to successfully raise the Session_End
    event every time a session timed out then this procedure would be redundant.
    */
    AS
    Delete From tbl_OnlineUsers
    Where DateDiff(Day, TimeStamp, GetDate()) > 0
    /* ie if the difference in days between now and the timestamp
    is 1 or more, delete it*/

The following code creates the SQL Server job that cleans out the database every night at midnight:

exec msdb.dbo.sp_add_job
        @job_name = 'OnlineUsers',
        @description = 'Cleanup routine for Online Users',
        @category_name = '[Uncategorized (Local)]',
        @enabled = 1,
        @notify_level_email = 0,
        @notify_level_page = 0,
        @notify_level_netsend = 0,
        @notify_level_eventlog = 2,
        @delete_level= 0
exec msdb.dbo.sp_add_jobstep
        @job_name = 'OnlineUsers',
        @step_id = 1,
        @step_name = 'Remove any session older than 1 day',
        @command = 'sp_CleanOldOnlineUsers',
        @retry_attempts = 5,
        @retry_interval = 5
exec msdb.dbo.sp_update_job
        @job_name = 'OnlineUsers',
        @start_step_id = 1

exec msdb.dbo.sp_add_jobschedule
        @job_name = 'OnlineUsers',
        @name = 'DailyCleanup',
        @enabled = 1,
        @freq_type = 4,
        @freq_interval = 1
       
exec msdb.dbo.sp_add_jobserver
        @job_name = 'OnlineUsers',
        @server_name = '(local)'

If you have any difficulty with this job creation code, you might be running an earlier version of SQL Server (earlier than 2000).  As always, the SQL Server Books Online is extremely helpful.

Why This Is A Bad Idea

I said at the outset that I thought this whole thing was a stupid idea.  There are a couple of reasons:

  • If your website gets decent traffic, an application like this would cripple it because for every new session and every page click, the webserver has to call the database, run a stored procedure...
  • If your website doesn't get decent traffic, why announce it to the world by putting up an "Online Users" page?
  • Even if your website doesn't get much traffic today, what if it one day someone from a high-traffic website links to you (think Slashdot, or CNN).  Instead of having thousands of users admiring your prose, design and programming prowess, they will get a Server Busy error because of all the extra load of keeping track of their sessions from this application.
  • Who cares which of your users are online?  I don't go to The Register and think, "Wow, it would be really cool if I could have a look at which IP addresses are requesting which URLs on the server at the moment".

Having said all that, I'm sure there are specific applications where this could be useful.  I just haven't come across any yet.  I would be interested to hear if you do happen to find one.

Final Words

This article was more interesting from a technical perspective than an engineering one.  The techniques are more interesting than the final product.  I hope this article has helped you.  If you have any suggestions or comments, please contact me at alex@aspalliance.com.

Download Code



User Comments

Title: Session.SessionID.ToString());   
Name: Spyros
Date: 2011-10-26 3:19:28 PM
Comment:
Seems not to work on asp.net 3.5 .
In global asa Session.SessionID.ToString()); is null.
Any idea ?
Title: Download links are broken   
Name: Linu
Date: 2009-12-21 1:33:33 AM
Comment:
The download link is broken, please fix it!
Title: I think it is possible to overcome the database hit issue   
Name: Yasser
Date: 2009-11-16 12:56:51 PM
Comment:
You're right, there is no point in doing a trip to the database on every request. As information about active users is useless once their session expires, I think it is a silly idea to store this information in the database (unless some kind of user online history needs to be stored). One alternative is to keep all logged in users in memory and do an in-memory lookup rather than a database lookup. The cleanup tasks are also performed on the online users collection object in memory.
Title: Bad idea ?!!! are you kidding me   
Name: Vex
Date: 2008-11-09 3:51:31 PM
Comment:
When is the last time you checked out myspace.com and facebook.com . People want to know which ones of their friends are online and they want to chat to them. that's one of the main interactive features of any social network website.
Title: Why this could be useful   
Name: Sharky
Date: 2008-01-19 11:31:05 PM
Comment:
one use is to see who is online to engage in an online chat. I think MySpace does this. There is a built in ASP.Net 2.0 function that shows you the number of users online but no built in query to show you who they are.
Title: Code names mixed   
Name: Frank
Date: 2007-09-17 8:16:37 AM
Comment:
Just an FYI. The links for the zip files are backwards. CS is under VB and vice versa.

Great info tho, just what my customer wants. Same as David Allen said, small site (100 users tops) and the admin's want to be able to see who's logging in as well as who's sharing their logins.

I had no idea how this would be done and you've knocked off a lot of time figuring it out on my own.

Thanks,
Frank
Title: Why this is a good idea   
Name: David Allen
Date: 2005-08-02 10:43:21 PM
Comment:
Your concerns about performance impact are legitimate; I share them.
But in enterprise applications (not high-volume, public applications), where users are known and from a limited pool of users, and you support them, then this can be very handy.
Respectfully,
davidkallen at yahoo dot com (email munged to prevent spam)






Community Advice: ASP | SQL | XML | Regular Expressions | Windows


©Copyright 1998-2019 ASPAlliance.com  |  Page Processed at 2019-11-21 6:25:42 AM  AspAlliance Recent Articles RSS Feed
About ASPAlliance | Newsgroups | Advertise | Authors | Email Lists | Feedback | Link To Us | Privacy | Search