AspAlliance.com LogoASPAlliance: Articles, reviews, and samples for .NET Developers
URL:
http://aspalliance.com/articleViewer.aspx?aId=24&pId=-1
Who's Online Application
page
by Alex Campbell
Feedback
Average Rating: 
Views (Total / Last 10 Days): 48274/ 119

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


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