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.

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

February 2014 Cumulative Update: SharePoint 2010

February 2014 Cumulative Update Packages for SharePoint Foundation 2010, SharePoint Server 2010 and Project Server 2010 have been released. Use the following links to download the update packages:

February 2014 CU for SharePoint Foundation 2010 - KB 2863938 http://support.microsoft.com/kb/2863938

February 2014 CU for SharePoint Server 2010 - KB 2863913 

http://support.microsoft.com/kb/2863913

February 2014 CU for Project Server 2010 - KB 2863917 http://support.microsoft.com/kb/2863917  

Note: This is build 14.0.7116.5000 of the cumulative update package.

Applies to: SharePoint Server 2010

Site Use Confirmation and Deletion: SharePoint 2010

This functionality- Simply awesome! If you have governance already setup in your organization then everybody will talk about this feature implementation.

This is really a good feature which reduces the storage management task if the site is not being utilized and kept idle for long duration. We can control the number of notifications to be send across (by default is 28) and time duration is 90 days. If the site remains idle for 90 days then the timer job will start sending notifications to the site collection administrators on daily basis, until & unless the site collection administrator clicks on the confirmation link.

How to implement this / from where we can enable this functionality?

Here are the complete details:-

1.   Open the Central Administration

2.   Application management

3.   Under Site Collections

4.   Confirm site use and deletion

We can implement this functionality web application wise, hmm what is meant by web application wise? Is this something will be applicable at the web application level or site collection level? Does this functionality enable the access to web app level?

So much curiosity and excitement is there to know the answer-correct? Me too were having the same before I explored all the things J

Please refer the following screenshot-

Once you select the proper web application from the drop-down then it will be applicable for all the site collections that nested underneath.

Which things are important before applying these settings?

Basically there are two check boxes-

1.   Email notifications

2.   Deletions

Let’s talk about Email notifications:

a)   Time span- By default it is 90 days but if you think this is less time frame for the owners (think from the governance perspective) then you can adjust this window for suppose 5 months / 6 months i.e. 180 days.

b)   When you will get the email from the SharePoint system that please confirm the usage then please don’t ignore by considering that you will get the next reminder email. You need to click on the link which talks about confirmation.

c)   Let me tell you one interesting thing about these emails- customizations. Yes you got it correct; you can customize this email as per your organization requirements. The location is-  C:\Program Files\Common Files\Microsoft Shared\Web Server Extensions\14\TEMPLATE\1033\XML\DEADWEB.xml

Note: Please take the backup of this file before making any kind of changes.

d)   There is one more possibility that initially there might be large numbers of email floating around (on the first-go only) when the timer job gets execute for the first time as we have experienced this behavior.

Let’s talk about Deletions now:

     I.        Be careful on this- Once the site gets deleted then it won’t go to Recycle Bin. Most of the people are having the misconception that just like windows/SP end user recycle bin, this site will also go to the same place but it’s not like that. It will be completely removed from the database also.

   II.        Don’t worry about the sub sites as if gets deleted then you can recover them from the second stage recycle bin but this funda is not applicable for site collections.

Note: In case of any issue or large number of emails floated around then also please don’t modify the database numbers as it is completely not supported.

Let’s talk about behind the scene functionality:

Site use confirmation and deletion functionality is basically depends on three main factors / database parameters:

1)   DeadWebNotifyCount

2)   CertificationDate

3)   LastContentChange

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

Common FAQ’s:

1)   Can we disable this functionality once we enable it? – Yes, you can disable this functionality anytime by the same steps as-

·         Central Administration > Application Management > Site Use Confirmation and Deletion

·         Uncheck the "Send e-mail notifications to owners of unused site collections” check box for the web app these sits are under.

·         Verify the "Dead Site Delete" is 'disabled'

2)  Can we implement this functionality without deleting the site? – Yes, when you turn on / enable the functionality in Central Admin then don’t select the check box which says – “Automatically delete the site collection if use is not confirmed”

3)   When exactly the email will trigger? - The Confirmation and Deletion functionality will e-mail the site owner when the site goes unused for a period of time.

4)  When exactly the site will be deleted? - If there is no response after a certain number of notifications by logging in to the site then you can choose to have the site deleted automatically.

5)   Is there any SharePoint job plays a vital role in this functionality? – Yes, that’s correct. Under Operations> Timer Job Definitions > Dead Site Delete" for all web applications (You will see this job only when you enable the functionality)

I hope the above mentioned information will be helpful to understand this useful functionality.