SharePoint site collection: Report Last access date and time

  • Article

Applies To

  1. SharePoint Server 2007
  2. SharePoint Server 2010
  3. SharePoint Foundation 2010
  4. SharePoint Foundation 2013
  5. SharePoint Server 2013

Requirement

Report the last access time stamp of site collections to find stale content

Solution

For this requirement, you need two reports as below

  1. Site collection administrators across all site collections
  2. Site collections which are under-utilized / not been accessed from a long time.

We 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.

References