22 February, 2014

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:-


Product Applies To:-
1.   SharePoint Server 2007
2.   SharePoint Server 2010
3.   SharePoint Foundation 2010
4.   SharePoint Foundation 2013
5.   SharePoint Server 2013

32 comments:

  1. This is very cool!! Ironically, I'm doing SQL commands today :)

    ReplyDelete
  2. Thank you Amol, it would be great if you could share how to get report using PS for list of Site Collection Administrators also

    ReplyDelete
    Replies
    1. Here is the one-

      function Get-SPfarmAdministrators {
      $admin = Get-SPAdminWebApplication
      foreach ($adminsite in $admin.Sites ) {
      $adminWeb = Get-SPweb($adminsite.url)
      $AdminGroupName = $adminWeb.AssociatedOwnerGroup
      $farmAdministratorsGroup = $adminweb.SiteGroups[$AdminGroupName]
      return $farmAdministratorsGroup.users
      }
      }

      function Get-ALLSiteCollectionAdminstrators{

      $spWebApps = Get-SPWebApplications
      foreach ($spWebApp in $spWebApps)
      {

      #WEB APPLICATION ENTITY
      $WebAppElem= $resultInXml.CreateElement("WebApplication")
      $WebAppElem.SetAttribute("Url", $spWebApp.Url);
      $WebAppsElem.AppendChild($WebAppElem);

      #SITE COLLECTIONS ENTITY
      $SiteCollsElem= $resultInXml.CreateElement("SiteCollections")
      $WebAppElem.AppendChild($SiteCollsElem);

      foreach($site in $spWebApp.Sites)
      {

      #SITE COLLECTION ENTITY
      $SiteCollElem= $resultInXml.CreateElement("SiteCollection")
      $SiteCollElem.SetAttribute("Url", $site.Url);
      $SiteCollsElem.AppendChild($SiteCollElem);

      #SITE COLLECTION ADMINISTRATORS ENTITY
      $SiteCollAdmsElem= $resultInXml.CreateElement("SiteCollectionAdministrators")
      $SiteCollElem.AppendChild($SiteCollAdmsElem);

      foreach($siteAdmin in $site.RootWeb.SiteAdministrators)
      {
      #SITE COLLECTION ADMINISTRATOR ENTITY
      $SiteCollAdmElem= $resultInXml.CreateElement("SiteCollectionAdministrator")
      $SiteCollAdmElem.SetAttribute("UserLogin",$siteAdmin.UserLogin)
      $SiteCollAdmElem.SetAttribute("DisplayName",$siteAdmin.DisplayName)
      $SiteCollAdmsElem.AppendChild($SiteCollAdmElem);
      # Write-Host "$($siteAdmin.ParentWeb.Url) - $($siteAdmin.DisplayName)"
      }
      $site.Dispose()
      }

      }

      }

      Delete
  3. Let me know in case of any queries, Thanks

    ReplyDelete
  4. Hi amol i am unable to copy the code u kept in your blog....can u say y?

    ReplyDelete
  5. hi, unfortunately you are not allowed to query the Content databases directly.

    ReplyDelete
    Replies
    1. You don't have appropriate rights to execute the command, Please contact your SQL Team to get this done / get yourself proper access by which you can run this command. Thanks

      Delete
  6. Is it supported from Microsoft this approach?

    ReplyDelete
    Replies
    1. Yes as we are not modifying the database values!

      Delete
  7. Hello I know his is very old but trying to figure out what i am doing wrong.
    i past the query but get no results. Says completed successfully but all fields are empty.

    Thanks

    ReplyDelete
  8. Wanted to share my findings with the query. If I run this in our environment, I get three of our most used sites as lastAccessed over five years ago. Not sure this is bringing back the expected data. I access all three of them, and no change in the results.

    ReplyDelete
    Replies
    1. Hi Matt, were you able to find on this one..We are also running this script..but script is not showing last accessed time correctly.

      Delete
  9. DATEADD(d,DayLastAccessed + 65536, CONVERT(datetime, '1/1/1899', 101))
    AS lastAccessDate --- > what is the reason to this part and why.

    ReplyDelete
  10. I think things like this are really interesting. I absolutely love to find unique places like this. It really looks super creepy though!!
    Machine learning training in chennai
    machine learning with python course in Chennai
    best training insitute for machine learning

    ReplyDelete
  11. Thank you for allowing me to read it, welcome to the next in a recent article. And thanks for sharing the nice article, keep posting or updating news article.
    Python training in bangalore

    ReplyDelete
  12. Thank you for allowing me to read it, welcome to the next in a recent article. And thanks for sharing the nice article, keep posting or updating news article.
    Python training in bangalore
    Python training in Bangalore
    Data science with python training in Bangalore

    ReplyDelete
  13. This is a very good tip particularly to those fresh to the blogosphere. Short but very accurate information… Thank you for sharing this one. A must read post!


    ANGULAR TRAINING IN BANGALORE

    UI Development Institute in Bangalore

    Python Training in Marathahalli

    ReplyDelete
  14. Great Article… I love to read your articles because your writing style is too good, its is very very helpful for all of us and I never get bored while reading your article because, they are becomes a more and more interesting from the starting lines until the end.
    Apache Spark with Scala online training
    AWS online training

    ReplyDelete
  15. Thanks for your informative article, Your post helped me to understand the future and career prospects & Keep on updating your blog with such awesome article.

    Best Data Science with R Online Certification

    ReplyDelete
  16. Thank you for sharing. Machine Learning Training in Bangalore from Indian Cyber Security Solutions design the course to provide a broad introduction to machine learning, data-mining, and statistical pattern recognition. Machine learning is the science of getting computers to act without being explicitly programmed. In the past decade, machine learning has given us self-driving cars, practical speech recognition, effective web search, and a vastly improved understanding of the human genome. Machine learning is so pervasive today that you probably use it dozens of times a day without knowing it. Machine Learning Course done by ICSS in Kolkata. ICSS is the Best Machine Learning Institute in Bangalore.

    ReplyDelete
  17. I am looking for and I love to post a comment that "The content of your post is awesome" Great work!
    Digital marketing course mumbai

    ReplyDelete
  18. This comment has been removed by the author.

    ReplyDelete

Your feedback is always appreciated. I will try to reply to your queries as soon as possible- Amol Ghuge

Note: Only a member of this blog may post a comment.