Real-time statistics of SharePoint sites via SQL-request
There’s a table in SharePoint content database where all the events are written in case if audit is turned on. But SharePoint doesn’t display real-time statistic about users on site, it gives you statistic only on the next day.
If your manager or anybody else asks you how many users are on the site, you can’t tell him to wait until tomorrow. I advice you to execute this script on your SQL-server and answer this question.
Script to get active users in DBSC
/****** this is Content Database of Site Collection ******/
/****** Real-time statistics of SharePoint sites via SQL-request ******/
SELECT Distinct(UserId) as u1, tp_IsActive, tp_Login, tp_Title, Occurred
FROM [WSS_Content_DBSC].[dbo].[AuditData] as t1, UserInfo as t2
BETWEEN CAST(GETDATE() AS DATE) AND DATEADD(DAY, 1, CAST(GETDATE() AS DATE)) --only today records
t1.UserId = t2.tp_ID
t2.tp_Login NOT LIKE 'sharepoint%'AND t2.tp_Login NOT LIKE '%authori%'
t2.tp_SiteID = '3E6BD7AC-E731-7621-32DB-6781321DFB4D' -- this is site collection ID in content Database
ORDER by Occurred desc
No Comments »
No comments yet.
RSS feed for comments on this post. TrackBack URL