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.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 |
/****** this is Content Database of Site Collection ******/ use WSS_Content_DBSC go /****** 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 WHERE t1.Occurred BETWEEN CAST(GETDATE() AS DATE) AND DATEADD(DAY, 1, CAST(GETDATE() AS DATE)) --only today records AND t1.UserId = t2.tp_ID AND t2.tp_Login NOT LIKE 'sharepoint%'AND t2.tp_Login NOT LIKE '%authori%' AND t2.tp_SiteID = '3E6BD7AC-E731-7621-32DB-6781321DFB4D' -- this is site collection ID in content Database ORDER by Occurred desc |
SELECT from table WHERE items IN (@variable)
Delete SharePoint sites recursively