none
DayLastAccessed - Not giving accurate data (Trying to find out subsites that have not been visited in a long time). RRS feed

  • Question

  • Hi there,

    In order to find out subsites that no one is visiting in last few months - I ran this SQL Query.
    Actually all the LastAccessedDate values come-out as Jan 3 2016 or Jan 2 2016 - which is wrong as all the sites are visited all the times - I have visited many of these just today.

    The bottom listings show a strange date - however those sites were visited today as well.

    Please help me find true dates for all sites when they were last visited.

    Thanks a lot.

    Tuesday, February 23, 2016 10:05 PM

Answers

  • Using LogParser on IIS logs is going to be your best bet. Querying SharePoint databases directly is unsupported; your query would introduce locks into the environment.

    The other route you could take is using SPWeb.LastItemModifiedDate, but that won't give you read information. You could also enable View audit logging on a Site Collection and monitor it for X period of time.


    Trevor Seward

            

    This post is my own opinion and does not necessarily reflect the opinion or view of Microsoft, its employees, or other MVPs.


    Tuesday, February 23, 2016 10:18 PM
    Moderator
  • Microsoft [Technical] PMs have repeatedly stated 'don't use T-SQL on SharePoint databases'. In addition, if you're going to read from the database, always use (NoLock). 

    Trevor Seward

            

    This post is my own opinion and does not necessarily reflect the opinion or view of Microsoft, its employees, or other MVPs.

    • Marked as answer by ran009 Wednesday, February 24, 2016 7:42 PM
    Wednesday, February 24, 2016 4:10 PM
    Moderator

All replies

  • Using LogParser on IIS logs is going to be your best bet. Querying SharePoint databases directly is unsupported; your query would introduce locks into the environment.

    The other route you could take is using SPWeb.LastItemModifiedDate, but that won't give you read information. You could also enable View audit logging on a Site Collection and monitor it for X period of time.


    Trevor Seward

            

    This post is my own opinion and does not necessarily reflect the opinion or view of Microsoft, its employees, or other MVPs.


    Tuesday, February 23, 2016 10:18 PM
    Moderator
  • Hi ran009,

    In SharePoint, there is no OOB way to get the last accessed date for sites.

    As Trevor suggested, you can parse the IIS logs.

    If you want a repeatable process, you can develop a desktop application for parsing the IIS logs and install it at the server machine. Then every time you need a report of unused sites, all you need to do is running this application.

    Or you can use code to get access date when users access the site, and add each access date into a list. Then you can filter and analysis the data of this list to get the last accessed date.

    Best regards,

    Linda Zhang


    TechNet Community Support
    Please remember to mark the replies as answers if they help, and unmark the answers if they provide no help. If you have feedback for TechNet Support, contact tnmff@microsoft.com.

    Wednesday, February 24, 2016 1:01 PM
    Moderator
  • Reading from SharePoint databases is a bit of a grey area, they've tweaked the wording since the last time i read it in depth:

    Reading from the SharePoint databases programmatically, or manually, can cause unexpected locking within Microsoft SQL Server which can adversely affect performance. Any read operations against the SharePoint databases that originate from queries, scripts, .dll files (and so on) that are not provided by the Microsoft SharePoint Development Team or by Microsoft SharePoint Support will be considered unsupported if they are identified as a barrier to the resolution of a Microsoft support engagement.

    If unsupported read operations are identified as a barrier to the resolution of support engagement, the database will be considered to be in an unsupported state. To return the database to a supported state, all unsupported read activities must stop.

    https://support.microsoft.com/en-gb/kb/841057

    Or in other words, its' allowed until/unless it causes issues, in which case it becomes unsupported.

    However I agree, it's a bad idea.

    Wednesday, February 24, 2016 1:20 PM
  • Microsoft [Technical] PMs have repeatedly stated 'don't use T-SQL on SharePoint databases'. In addition, if you're going to read from the database, always use (NoLock). 

    Trevor Seward

            

    This post is my own opinion and does not necessarily reflect the opinion or view of Microsoft, its employees, or other MVPs.

    • Marked as answer by ran009 Wednesday, February 24, 2016 7:42 PM
    Wednesday, February 24, 2016 4:10 PM
    Moderator