none
last access date/time for sharepoint site collections.

    Question

  • Hello,

    Can anyone tell me how to last access date/time for the all site collections? I can think the following options..

    1. Powershell

    2. IIS logs

    3. SQL Query

    I think powershell would bethe option to go with. Please advise.

    Thanks.

    Tuesday, February 05, 2013 6:58 PM

Answers

  • Oh, sorry I misread. To my knowledge there is no last accessed time variable stored in the site collection. IIS logs would be the suitable route.

    You would need to search in the latest log file backwards, that is, starting from the end as the log entries are in chronological order for the last request to http://server/sites/sitecollection.

    Even better, you could do this with Microsoft' Log Parser utility which will let you form complex queries against a text file (like IIS logs).

    If you're storing the logs in SQL, you would want to query for the maximum date/time for URLs matching your site collection.

    So for example (this is pseudo-SQL, because I don't know what your tables look like)

    SELECT max(date)
    FROM IISLogs
    WHERE url like 'http://server/sites/sitecollection%'



    Jason Warren
    Infrastructure Architect

    Wednesday, February 06, 2013 7:57 PM

All replies

  • SQL query is unsupported so don't go that route.

    SPSite.LastContentModifiedDate property will in theory be what you need. Your script will look something like this:

    $site = Get-SPSite http://server/sites/sitecollection
    $site.LastContentModifiedDate


    Jason Warren
    Infrastructure Architect

    Tuesday, February 05, 2013 8:05 PM
  • Hi, 

    The best option is to use Reporting and usage analysis - http://technet.microsoft.com/en-us/library/gg266383(v=office.14).aspx ootb way.

    To achieve this thing problematically please refer - http://www.mindfiresolutions.com/Find-The-Last-Modified-Date-Of-A-SharePoint-Site-Programmatically-1745.php

    these are few more references 

    http://sharepoint.microsoft.com/Blogs/fromthefield/Lists/Posts/Post.aspx?ID=147

    http://pyaarey.wordpress.com/2012/11/17/find-unused-sites-based-on-last-accessed-date/

    Let us know if this helps


    Warm Regards, Pratik Vyas | SharePoint Consultant | http://sharepointpratik.blogspot.com/ | Posting is provided "AS IS" with no warranties, and confers no rights Please remember to click "Mark As Answer" if a post solves your problem or "Vote As Helpful" if it was useful.

    • Proposed as answer by Craig Lussier Tuesday, February 05, 2013 9:37 PM
    • Unproposed as answer by abc67 Tuesday, February 05, 2013 10:18 PM
    Tuesday, February 05, 2013 8:10 PM
    Moderator
  • Thanks Jason for your reply. I DONT need lastcontentmodifieddate...I need last access date/time....
    Tuesday, February 05, 2013 10:16 PM
  • Thanks Pratik..for your reply. I DONT need lastcontentmodifieddate...I need last access date/time....
    Tuesday, February 05, 2013 10:18 PM
  • I got the IIS logs in the SQL. Can anyone give me thw Query which give me the last access date for site collections? Thanks.
    Tuesday, February 05, 2013 10:19 PM
  • Oh, sorry I misread. To my knowledge there is no last accessed time variable stored in the site collection. IIS logs would be the suitable route.

    You would need to search in the latest log file backwards, that is, starting from the end as the log entries are in chronological order for the last request to http://server/sites/sitecollection.

    Even better, you could do this with Microsoft' Log Parser utility which will let you form complex queries against a text file (like IIS logs).

    If you're storing the logs in SQL, you would want to query for the maximum date/time for URLs matching your site collection.

    So for example (this is pseudo-SQL, because I don't know what your tables look like)

    SELECT max(date)
    FROM IISLogs
    WHERE url like 'http://server/sites/sitecollection%'



    Jason Warren
    Infrastructure Architect

    Wednesday, February 06, 2013 7:57 PM
  • Thanks Jason..I'm working on your suggestion and I'm able to insert IISlogs to SQL successfully. Now My SQL table looks like as below and I need the output as show below as well. Can you please help me SQL query....

    Col1 Col2    Col3
    /sites/site1/Shared+Documents/Forms/AllItems.aspx 2/6/2013    User1
    /sites/site1/default.aspx 2/7/2013    User2
    /sites/site1/Shared+Documents 2/8/2013    User3
    /sites/site2/Shared+Documents/Forms/AllItems.aspx 2/9/2013    User4
    /sites/site2/default.aspx 2/10/2013    User5
    /sites/site2/Shared+Documents 2/11/2013    User6
    /sites/site3/Shared+Documents/Forms/AllItems.aspx 2/12/2013    User7
    /sites/site3/default.aspx 2/20/2013    User8
    /sites/site3/Shared+Documents 2/14/2013    User9
    /sites/site4/Shared+Documents/Forms/AllItems.aspx 2/17/2013    User10
    /sites/site4/default.aspx 2/16/2013    User11
    /sites/site4/Shared+Documents 2/15/2013    User12

    and I need OUTPUT  as below :

    OutPut :
    /sites/site1/Shared+Documents 2/8/2013     User3
    /sites/site2/Shared+Documents 2/11/2013     User6
    /sites/site3/default.aspx 2/20/2013     User8
    /sites/site4/Shared+Documents/Forms/AllItems.aspx 2/17/2013     User10
    Thursday, February 07, 2013 5:39 AM
  • Hi abc67,

    You can new a thread in SQL Server T-SQL forum to get the help.

    Anyway, you can use the following script to get the result:

    ;with cteRowNumber as (
       
    select Col1, Col2, Col3, row_number() over(partition by Col1 order by Col2 desc) as RowNum
        
    from YourTable
    )
    select Col1, Col2, Col3
       
    from cteRowNumber
       
    where RowNum = 1

    Thanks,
    Jinchun Chen


    Jinchun Chen(JC)
    TechNet Community Support

    Monday, March 11, 2013 9:48 AM
    Moderator
  • Hello,
    Although your question is very old but still You may try following query on SharePoint database to get the last access date for all site collection :)

    select

    fullurl,siteid,Title,DATEADD(d,DayLastAccessed+65536,CONVERT(datetime,'1/1/1899',101))fromallwebswith (nolock)whereParentWebId  isnull

    HTH

    Tahir

    Tuesday, January 19, 2016 12:39 PM