Bryant Likes's Blog

Extending SharePoint using Global.asax - Who's Online

In my last article on this subject I explained how to add some basic page tracking to SharePoint. In this article I will expand on this by adding a Who's Online WebPart. This WebPart will show who is currently online.

First we need to create the ActiveUsers table which will keep track of the users that are currently online. We also need to modify the Hit_Add stored procedure to update the ActiveUsers table. Finally, we need to create a very simple stored procedure to get the list of users that are currently online. Below is the SQL script for these tasks.

create table ActiveUsers (
 UserID varchar(50) not null,
 LastHit datetime not null,
 LastUrl varchar(256) not null,
 constraint PK_ActiveSessions primary key clustered
 (
  UserID
 )
)
go
alter proc Hit_Add
(
 @Url varchar(256),
 @UserID varchar(50)
)
as
 -- this needs to be changed to the userID
 -- that your site runs as
 if (@UserID = 'DOMAIN\PROCESSID') return
 insert into Hits values
 (@Url, @UserID, getdate())
 delete ActiveUsers
 where UserID = @UserID
  or datediff(mi, LastHit, getdate()) > 30 -- minutes
 insert into ActiveUsers values
 (@UserID, getdate(), @Url)
go
create proc ActiveUsers_Get
as
 select UserID, LastHit, LastUrl, datediff(mi, LastHit, getdate()) Age
 from ActiveUsers

go

That is all there is to tracking the users that are currently online. You can adjust the time by changing the minutes in the delete statement. Next we need to create our WebPart. The WebPart I'm going to show you is somewhat simple and just displays a list of users. You can easily add more functionality to it if you want. Below is the code for the Who's Online WebPart.

using System;
using System.ComponentModel;
using System.Data;
using System.Data.SqlClient;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Xml.Serialization;
using Microsoft.SharePoint;
using Microsoft.SharePoint.Utilities;
using Microsoft.SharePoint.WebControls;
using Microsoft.SharePoint.WebPartPages;
namespace Bml.Stats.WebParts
{
 /// 

 /// Description for ActiveUsers.
 /// 
 [ToolboxData("<{0}:ActiveUsers runat=server>"),
  XmlRoot(Namespace="SPSStats")]
 public class ActiveUsers : Microsoft.SharePoint.WebPartPages.WebPart
 {
  
  protected override void RenderWebPart(HtmlTextWriter output)
  {
   output.AddAttribute(HtmlTextWriterAttribute.Width, "100%");
   output.AddAttribute(HtmlTextWriterAttribute.Cellpadding, "0");
   output.AddAttribute(HtmlTextWriterAttribute.Cellspacing, "0");
   output.RenderBeginTag(HtmlTextWriterTag.Table);
   output.RenderBeginTag(HtmlTextWriterTag.Tr);
   output.AddAttribute(HtmlTextWriterAttribute.Width, "100%");
   output.AddAttribute(HtmlTextWriterAttribute.Valign, "top");
   output.RenderBeginTag(HtmlTextWriterTag.Td);
   output.AddAttribute(HtmlTextWriterAttribute.Class, "ms-ls");
   output.RenderBeginTag(HtmlTextWriterTag.Table);
   AddUserRows(output);
   output.RenderEndTag(); // table
   output.RenderEndTag(); // td
   output.RenderEndTag(); // tr
   output.RenderEndTag(); // table
  }
  
  private void AddUserRows(HtmlTextWriter output)
  {
   try
   {
    SPWeb web = SPControl.GetContextWeb(Context);
    using (SqlConnection cn = new SqlConnection("[Your Connection]"))
    {
     cn.Open();
   
     SqlCommand cmd = new SqlCommand();
     cmd.Connection = cn;
     cmd.CommandText = "ActiveUsers_Get";
     cmd.CommandType = CommandType.StoredProcedure;
     
     SqlDataReader dr = cmd.ExecuteReader(CommandBehavior.CloseConnection);
     while (dr.Read())
     {
      string userName = (string)dr["UserID"];
      
      output.RenderBeginTag(HtmlTextWriterTag.Tr);
      output.AddAttribute(HtmlTextWriterAttribute.Class, "ms-lsmin ms-vb");
      output.AddAttribute(HtmlTextWriterAttribute.Valign, "top");
      output.RenderBeginTag(HtmlTextWriterTag.Td);
      output.AddAttribute(HtmlTextWriterAttribute.Align, "absbottom");
      output.AddAttribute(HtmlTextWriterAttribute.Src, "/_layouts/images/perusr.gif");
      output.RenderBeginTag(HtmlTextWriterTag.Img);
      output.RenderEndTag(); // img
      output.Write(" {0}", SPUtility.GetFullNameFromLogin(web.Site, userName));
      output.RenderEndTag(); // td
      output.RenderEndTag(); // tr
     }
     
     dr.Close();
     cn.Close();
    }
   }
   catch (Exception exc)
   {
    output.Write(exc.Message);
   }
  }
 }
}

Once you've compiled the WebPart and added it to the server you will need to make one change to your web.config file before the part will work. You will need to change the trust level from WSS_Minimal to WSS_Medium. This will allow the part to use the SQL Connection which isn't allowed in the minimal trust scheme. After you add it to a page you should see something very much like the following image.

As I wrote this WebPart I thought of a lot of other uses for the hits data such as a Favorites list (based on urls with the most hits), recently viewed pages, and others. There seems to be a lot you can do once you start collecting this data.