Feed Icon  


  • Bryant Likes
  • Send mail to the author(s) E-mail
  • twitter
  • View Bryant Likes's profile on LinkedIn
  • del.icio.us
Get Microsoft Silverlight
by clicking "Install Microsoft Silverlight" you accept the
Silverlight license agreement

Hosting By

Hot Topics


Open Source Projects



Extending SharePoint using Global.asax - Who's Online

Posted in SharePoint at Thursday, 13 May 2004 07:28 Pacific Daylight Time

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.

Wednesday, 07 July 2004 05:31:00 (Pacific Daylight Time, UTC-07:00)
Thank you, in advance, and I know this is not necessarily the appropriate place for this question, but can you point me to a chat room that would tell me how to set up Visual Basic 6.0 so it sees objects on my SharePoint server?
Wednesday, 21 July 2004 03:38:00 (Pacific Daylight Time, UTC-07:00)
When I do this, I get the following error message:
<br>Login failed for user '(null)'. Reason: Not associated with a trusted SQL Server connection
<br>Any idea?
Wednesday, 21 July 2004 04:24:00 (Pacific Daylight Time, UTC-07:00)
You can't use a trusted connection to connect to the database. So you need to change your connection string to something like this:
<br><a target="_new" href="http://blogs.sqlxml.org/bryantlikes/articles/583.aspx#675">http://blogs.sqlxml.org/bryantlikes/articles/583.aspx#675</a>
Thursday, 22 July 2004 05:04:00 (Pacific Daylight Time, UTC-07:00)
I've tried changing my connect string. I have searched far and wide through the net for the answer to this.
<br>My connectstring is this:
<br>strConn = "Server=MySERVER;Database=MYDEV;integrated security=SSPI";
<br>SqlConnection cn = new SqlConnection(strConn);
<br>I have also tried:
<br>strConn = "server=MYSERVER;Trusted_Connection=True;Database=MYDEV;uid=me;password=********"
<br>Neither of them work. they both work from a standard winform.
Monday, 26 July 2004 00:24:00 (Pacific Daylight Time, UTC-07:00)
You can't use "Trusted_Connection=true". See the example connection string that I posted here:
<br><a target="_new" href="http://blogs.sqlxml.org/bryantlikes/articles/583.aspx#675">http://blogs.sqlxml.org/bryantlikes/articles/583.aspx#675</a>
Tuesday, 03 August 2004 01:17:00 (Pacific Daylight Time, UTC-07:00)
Why can't you use Trusted Connection?
Tuesday, 03 August 2004 02:41:00 (Pacific Daylight Time, UTC-07:00)
Yes!? Why can't we use Trusted Connection? My company's administrator is allways boring me with this issue...
Monday, 09 August 2004 05:36:00 (Pacific Daylight Time, UTC-07:00)
You can't use it here unless the user id that executes the global.asax has access to the database.
Tuesday, 05 October 2004 02:19:00 (Pacific Daylight Time, UTC-07:00)
If the Account that your App Pool is configured to run under (in W2k3) can access the SQL Server then you can use Trusted_Connection=true.. otherwise, you will have to specify the userid and password in the connection string without the trusted_connection part.
Saturday, 17 December 2005 23:26:00 (Pacific Standard Time, UTC-08:00)
When I upload the .dwp file and add it to a page I get the control is not registered as safe error
Thursday, 20 April 2006 06:25:47 (Pacific Daylight Time, UTC-07:00)
Really nice one!
Monday, 29 May 2006 18:00:52 (Pacific Daylight Time, UTC-07:00)
I'd like to know where i need to create the global.asax
and also where i create table??
Thursday, 03 August 2006 21:30:22 (Pacific Daylight Time, UTC-07:00)
that is Pretty one
Sunday, 22 October 2006 17:36:53 (Pacific Daylight Time, UTC-07:00)
Excuse me, I think here is small error:

insert into Hits values..

Perhaps, there should be insert into ActiveUsers values...
Tuesday, 24 October 2006 10:27:59 (Pacific Daylight Time, UTC-07:00)
You can use a trusted connection in WebParts _if_ you implement impersonation. NTLM v2 will hand off your credentials to Sharepoint, but NTLM will then not hand off your credentials to SQL. I had this problem because SQL Server Analysis Services 2005 requires a trusted connection. The two solutions are to 1.) Use Basic authentication which will pass off your credentials again. Unless you're running a secure site this probably isn't a great option. The other is to impersonate via code instead of the web.config a proxy Active Directory user that has access to the account for the sections that access the database. This will allow you to establish the connection. Here's a link that explains more:

Tuesday, 24 October 2006 10:28:41 (Pacific Daylight Time, UTC-07:00)
Well, the link got cut off, copy and paste it. ;)
Friday, 17 November 2006 01:16:30 (Pacific Standard Time, UTC-08:00)
Can someone post a link to a compiled version?
Wednesday, 25 April 2007 18:33:07 (Pacific Daylight Time, UTC-07:00)
How does the ActiveUser table gets populated?
Joydeep Banerjee
Wednesday, 27 June 2007 02:21:54 (Pacific Daylight Time, UTC-07:00)
Is there a compiled version already available for download? Please email me at josh.dunigan@thetravelauthority.com if so. Thanks.
Josh Dunigan
Monday, 31 March 2008 22:40:24 (Pacific Standard Time, UTC-08:00)
I am a bit new at sharepoint in general, so couple questions. I have a SBS 2003 server, I have sharepoing 2007 installed along side the 2003 site.

How would i go about creating that table? and What do I need to compile the web part?

Is there a compiled version out there I can download? Please email me if there is, jtrober@hotmail.com
Monday, 05 May 2008 14:47:54 (Pacific Daylight Time, UTC-07:00)
When iam using this code, iam not getting anything(data value) in webpart. The webpart just displays the title and rest is just blank.

I have used SQL Authentication, and just changed the namespace and class name to 'WhoIsOnline'. Rest everything is same.

Any clue why this webpart is blank...:(
Gaurav Saxena
Monday, 12 May 2008 14:17:02 (Pacific Daylight Time, UTC-07:00)
How to integrate communicator functionality with this application.
Gaurav Saxena
Tuesday, 29 July 2008 03:32:51 (Pacific Daylight Time, UTC-07:00)
plss...is there a compiled version of this!
really want this "WhosOnline" on our Sharepoint site, with the Chat box already on it.

if there is, pls email the compiled version to:
dasniperz at gmail dot com

Wednesday, 10 September 2008 06:49:46 (Pacific Daylight Time, UTC-07:00)
Do I need to have completed the instructions on your previous post in order to get this to work?
Tuesday, 16 September 2008 12:16:49 (Pacific Daylight Time, UTC-07:00)
This one is great but i used the mysql and is there any compiled version please post link here.

Thank you in advance.
Thursday, 18 September 2008 04:46:37 (Pacific Daylight Time, UTC-07:00)
Geez. All the people asking for the precompiled version... Could y'all be any more lazy?
Thursday, 09 October 2008 02:11:46 (Pacific Daylight Time, UTC-07:00)
It is more of not knowing how to code the informtation to work than it is being lazy. I, for instance, am the Network admin and have been given the task of Administering our SharePoint sites and donot really have any experience other than installing SQL and Sharepoint to be able to create the sites. So again, the compile version would benefit many who are not involved in coding.
Friday, 06 March 2009 03:16:54 (Pacific Standard Time, UTC-08:00)
I'm new to Sharepoint and am clueless about what tools are needed to even start compiling this. A pre-compiled webpart would be nice but I'm willing to learn the process of how to compile this if I know what tools/software are required.

Can I use Visual Web Developer 2008 Express to accomplish this or do I need Visual Studio (2005 or 2008)? I don't even know what flavor language the above code is. VB/C#? And about Global.asax, that looks like its using C# script?

Any pointers would be appreciated. If I can get this compiled and working, I'll be more than glad to share it.


Tuesday, 14 April 2009 16:34:42 (Pacific Daylight Time, UTC-07:00)
I have created thw hos is online webpart,as is is given here.
it is working in Intranet site but not working in the internet site.It shows an exception called "could nor find Stored Procedure ActiveUsers_Get".

I have created the tables in the Contyent data base of the sharepoint site.

Please helpp me to solve this issue.

Its very urgent.
Wednesday, 03 June 2009 21:02:46 (Pacific Daylight Time, UTC-07:00)
Where is the HIT table in sharepoint-2007 sqlserver 2005
Friday, 05 June 2009 01:52:32 (Pacific Daylight Time, UTC-07:00)
hi,i am come from china,i am chinese,i have a little english.
recently,i have one question.
i have one requirement, when i have no operation on sharepoint site after 20 min, i want to logout sharepoint auto, how can i do this, thank you for your apply.
good night.
Thursday, 23 July 2009 08:35:26 (Pacific Daylight Time, UTC-07:00)

Thnak you for your interesting topic.

I have a question concerning webparts in sharepoint designer I created a webpart in sharepoint designer 2007, i used sql server connection to link the data.

the connexion is good but i wanted to customize the webpart dataview so i added a dropdown list to witch i chosed the same connection string as the webpart's has to link the data.

but when i save and view my sharepoint site, the drop down generates an error : trusted connected problem

If you have any clue plz tell me.
Comments are closed.