Thursday, February 13, 2014
Last access date/time for SharePoint site collections
Based on the research done, analyzed so many test cases, spend so many hours to prepare a script, spend numerous days to explore the site columns in SQL database and then finally got it that I wanted as per my requirements.
You must be thinking that which path/solution I am talking about-
Let me brief some details before sharing the actual data-
For this requirement, you need two reports like-
Site collection administrators across all site collections
Site collections which are under-utilized / not been accessed from a long time.
I will not talk about the first point as it’s very easy and you can prepare it in a simple manner via PowerShell. In case of any queries then please let me know.
Regarding second point which is our main agenda / goal – This can be extracted via SQL Query!
Please refer the following SQL Query which will accomplish this requirement.
SELECT FullUrl AS 'Site URL', TimeCreated,
DATEADD(d,DayLastAccessed + 65536, CONVERT(datetime, '1/1/1899', 101))
AS lastAccessDate FROM Webs WHERE
(DayLastAccessed <> 0) AND (FullUrl LIKE N'sites/%') ORDER BY lastAccessDate
You need to run this query against the specific content database and extract the report.
1. Login to your SQL box
2. Open SQL Server management studio
3. Click on “New Query” from the top
4. Change the database from the dropdown
5. Select your content database
6. Paste the above query in the query box
7. Select it (control A)
8. Execute it from the top.
9. You will get the report in the bottom section. That’s it- You are done.
If you have any queries/questions regarding the above mentioned information then please let me know. I would be more than happy to help you as well as resolves your issues, Thank you.
Product Applies To:-
1. SharePoint Server 2007
2. SharePoint Server 2010
3. SharePoint Foundation 2010
4. SharePoint Foundation 2013
5. SharePoint Server 2013
Location: Birmingham, West Midlands, UK