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