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.