Count users online with Sitecore OMS

When you have a web application, you might want to know how many users are currently online. If you have visited some of web online forums you can see (usually on the first page) the number of currently online users.
This information might be also used to postpone some website jobs if there are too many users online, etc.
How do we know / count how many sessions or users are currently connected to our website in Sitecore?


Actually, there is no 100% true way to tell who is online, because HTTP is a stateless protocol and it works simply on a Request-Response basis. In ASP.NET, there is a MembershipProvider.GetNumberOfUsersOnline method, it can be used, but has a major limitation – it does not count unauthenticated users.
Internally, GetNumberOfUsersOnline calls stored procedure from the “Core” database (in Sitecore). Here’s the procedure text:

DECLARE @DateActive datetime
SELECT  @DateActive = DATEADD(minute,  -(@MinutesSinceLastInActive), @CurrentTimeUtc) 

DECLARE @NumOnline int
SELECT  @NumOnline = COUNT(*)
FROM    dbo.aspnet_Users u(NOLOCK),
dbo.aspnet_Applications a(NOLOCK),
dbo.aspnet_Membership m(NOLOCK)
WHERE   u.ApplicationId = a.ApplicationId                  AND
LastActivityDate > @DateActive                     AND
a.LoweredApplicationName = LOWER(@ApplicationName) AND
u.UserId = m.UserId
RETURN(@NumOnline) 

Looks really simple, it just checks LastActivityDate column. In Sitecore OMS we can check LastPageTimestamp column of the Session database instead, and it will count both authenticated and non-authenticated users. Here’s the query text (“5” is a timeframe in minutes within user is considered “online”) :

SELECT COUNT(*)
FROM [Session]
WHERE LastPageTimestamp > DATEADD(minute,  -(5), GETDATE()) 

That’s all about query, but how do we execute it? Add custom stored procedure to the database? – not a best choice if we want to use this approach across multiple solutions. We can use Sitecore OMS API to execute query, it’s rather simple, but needs some explanation if you haven’t used it before.

public class OnlineUsersManager
{
private const string CommandText = "SELECT COUNT(*) FROM {0}Session{1} WHERE {0}LastPageTimestamp{1} > DATEADD(minute,  -(5), GETDATE())";

public static int GetOnlineUsersCount()
{
return AnalyticsManager.ReadOne(CommandText, ReadOnlineUsersCount);
}

private static int ReadOnlineUsersCount(DataProviderReader reader)
{
var analyticsProvider = new AnalyticsProviderSqlServer();
return analyticsProvider.GetInt(0, reader);
}
}

We use AnalyticsManager.ReadOne method to execute our query, passing the ReadOnlineUsersCount method (which will process results) as parameter. The “{0}” and “:{1}” placeholder should be used instead of “[“ and “]” chars, I suppose it’s required for compatibility with Oracle.

That’s all. Now you can display number of online users at your website. Code example in this post does not contain caching, but I strongly recommend using it. Also, you can use approach described in this article:  Using Sitecore media cache with custom handlers to generate nice images with your website stats and cache them automatically - .

And one more thing – this example just shows how simple interaction with Sitecore OMS  API is. In fact, you can write much more complex queries, like “online users from your country” and so on. It’s only limited by your imagination.