Monday, September 24, 2012

Getting concurrent users from SharePoint's RequestUsage SQL view

I've been curious about how our SharePoint farm is faring.  One of the statistics that is interesting to me is concurrent users - the number of users that the service can provide at any one time.  There are various methodologies behind this, but the one I opted to use was to look at how many unique users are accessing the site in a ten minute period.  It's not a 100% accurate determination of concurrency, but I didn't want to purchase additional software to do some analytics, and the SharePoint web analytics just wasn't cutting it for me.

First, I enabled Usage and Health Reporting (from Central Administration / Monitoring / Configure usage and health data collection), with the important event being Page Requests.  Enabling this will create a new database, typically named WSS_Logging, and a few SQL views to perform selects on this data.

Next, I used the following SQL.  I wanted to grab data for a one-week period.  Here's what I came up with:

SELECT 
MIN([LogTime]) AS 'From', 
MAX([LogTime]) AS 'To', 
COUNT([LogTime]) AS 'Total', 
COUNT(DISTINCT [UserLogin]) AS 'Unique'
FROM [WSS_Logging].[dbo].[RequestUsage]
WHERE LogTime < '2012-09-24 00:00:00.000' 
AND LogTime > '2012-09-17 00:00:00.000'
AND UserLogin != 'DOMAIN\AppPool'
AND UserLogin != 'SHAREPOINT\system'
AND UserLogin != 'DOMAIN\spfarmacc'
AND UserLogin != 'DOMAIN\ServFarmAcc'
AND UserLogin NOT LIKE '0#%'
AND UserLogin != ''
AND BytesConsumed > 0
GROUP BY
DATEPART(YEAR, [LogTime]),
DATEPART(MONTH, [LogTime]),
DATEPART(DAY, [LogTime]),
DATEPART(HOUR, [LogTime]),
(DATEPART(MINUTE, [LogTime]) / 10)

I realise there are probably better ways of doing this, but I'm no SQL expert. This will show a From and To datetime, a Total count of all applicable page requests, and a Unique count of unique user accounts within that Total. Therefore, the Unique column shows how many concurrent users were active during the times shown in From and To.

No comments:

Post a Comment