Last access date/time for SharePoint site collections

Governance! Governance! Governance! – This topic is long lasting… There are so many things comes under governance and if you have a good governance then it means your SharePoint environment is stable, process oriented, disciplined, following capacity planning etc. In my last article, I discussed about one of the most vital governance agenda i.e. site use confirmation and deletion- 

http://sharepointknowledgebase.blogspot.com/2014/02/site-use-confirmation-and-deletion.html

Today’s article is based in continuation with the above article. How? Let me explain - Suppose you don’t want to implement the functionality i.e. (site use confirmation and deletion), then what’s the alternative?

A.   Should be something by which we can think off?

B.   Should be something by which we can implement with proper considerations?

C.   Should be something which there is a minimal risk?

Don’t worry! I also thought of the same questions and started exploring the options by means of PowerShell, by means of any third party tool, by means of SQL queries….

Done a lot of research, 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.

References:-

http://technet.microsoft.com/en-us/library/ee663473(v=office.14).aspx

http://technet.microsoft.com/en-us/library/ee663480.aspx

Product Applies To:-

1.   SharePoint Server 2007

2.   SharePoint Server 2010

3.   SharePoint Foundation 2010

4.   SharePoint Foundation 2013

5.   SharePoint Server 2013