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

53 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. Devops is not a Tool.Devops Is a Practice, Methodology, Culture or process used in an Organization or Company for fast collaboration, integration and communication between Development and Operational Teams. In order to increase, automate the speed of productivity and delivery with reliability.
    python training in bangalore
    aws training in bangalore
    artificial intelligence training in bangalore
    data science training in bangalore
    machine learning training in bangalore
    hadoop training in bangalore
    devops training in bangalore

    ReplyDelete
  11. Thank you for sharing your article. Great efforts put it to find the list of articles which is very useful to know, Definitely will share the same to other forums.
    Data Science Training in chennai at Credo Systemz | data science course fees in chennai | data science course in chennai quora | data science with python training in chennai

    ReplyDelete
  12. Good job in presenting the correct content with the clear explanation. The content looks real with valid information. Good Work

    DevOps is currently a popular model currently organizations all over the world moving towards to it. Your post gave a clear idea about knowing the DevOps model and its importance.

    Good to learn about DevOps at this time.


    devops training in chennai | devops training in chennai with placement | devops training in chennai omr | devops training in velachery | devops training in chennai tambaram | devops institutes in chennai | devops certification in chennai | trending technologies list 2018

    ReplyDelete
  13. 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
  14. It's really a nice experience to read your post. Thank you for sharing this useful information. If you are looking for more about Trending Software Technologies in 2018 | Hadoop Training in Chennai | big data Hadoop training and certification in Chennai

    ReplyDelete
  15. 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
  16. 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
  17. 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
  18. 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
  19. 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
  20. 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
  21. I have been reading for the past two days about your blogs and topics, still on fetching! Wondering about your words on each line was massively effective. Techno-based information has been fetched in each of your topics. Sure it will enhance and fill the queries of the public needs. Feeling so glad about your article. Thanks…!
    magento training course in chennai
    magento training institute in chennai
    magento 2 training in chennai
    magento development training
    magento 2 course
    magento developer training

    ReplyDelete
  22. Thanks for Sharing This Article.It is very so much valuable content. I hope these Commenting lists will help to my website
    top workday studio online training

    ReplyDelete
  23. Thanks for Sharing This Article.It is very so much valuable content. I hope these Commenting lists will help to my website
    top workday studio online training

    ReplyDelete
  24. Thanks for Sharing This Article.It is very so much valuable content. I hope these Commenting lists will help to my website
    top microservices online training

    ReplyDelete
  25. 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

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.