Last access time in sharepoint online

SR VSP 1,251 Reputation points
2023-09-18T14:15:46.9666667+00:00

Hi Guys,

Do we have any property with "LastAccessedTime" of a file in SharePoint Powershell ? in the below script

Example: If the user has "Read" only access to the site I need to capture the "Last AccessedTime" of a file ( not LastModifiedDate)

#Function to Generate Report on all documents in a SharePoint Online Site Collection
Function Get-SPODocumentInventory($SiteURL)
{
    Try {
        #Setup the context
        $Ctx = New-Object Microsoft.SharePoint.Client.ClientContext($SiteURL)
        $Ctx.Credentials = $Credentials
    
        #Get the web from given URL and its subsites
        $Web = $Ctx.web
        $Ctx.Load($Web)
        $Ctx.Load($Web.Lists)
        $Ctx.Load($web.Webs)
        $Ctx.executeQuery()
  
        #Arry to Skip System Lists and Libraries
        $SystemLists =@("Converted Forms", "Master Page Gallery", "Customized Reports", "Form Templates", "List Template Gallery", "Theme Gallery",
               "Reporting Templates", "Solution Gallery", "Style Library", "Web Part Gallery","Site Assets", "wfpub", "Site Pages", "Images")
      
        Write-host -f Yellow "Processing Site: $SiteURL"
  
        #Filter Document Libraries to Scan
        $Lists = $Web.Lists | Where {$_.BaseType -eq "DocumentLibrary" -and $_.Hidden -eq $false -and $SystemLists -notcontains $_.Title -and $_.ItemCount -gt 0}
        #Loop through each document library
        Foreach ($List in $Lists)
        {
            #Define CAML Query to Get List Items in batches
            $Query = New-Object Microsoft.SharePoint.Client.CamlQuery
            $Query.ViewXml ="
                <View Scope='RecursiveAll'>
                   <Query>
                      <OrderBy><FieldRef Name='ID' Ascending='TRUE'/></OrderBy>
                   </Query>
                   <RowLimit Paged='TRUE'>$BatchSize</RowLimit>
                </View>"
  
            Write-host -f Cyan "`t Processing Document Library: '$($List.Title)' with $($List.ItemCount) Item(s)"
  
            Do {
                #Get List items
                $ListItems = $List.GetItems($Query)
                $Ctx.Load($ListItems)
                $Ctx.ExecuteQuery()
 
                #Filter Files
                $Files = $ListItems | Where { $_.FileSystemObjectType -eq "File"}
 
                #Iterate through each file and get data
                $DocumentInventory = @()
                Foreach($Item in $Files)
                {
                    $File = $Item.File
                    $Ctx.Load($File)
                    $Ctx.ExecuteQuery()

                 
                    $filterDate = (Get-Date).AddDays(-730).Date
                    if ($File.TimeLastModified -lt $filterDate){

                        $DocumentData = New-Object PSObject
                        $DocumentData | Add-Member NoteProperty SiteURL($SiteURL)
                        $DocumentData | Add-Member NoteProperty DocLibraryName($List.Title)
                        $DocumentData | Add-Member NoteProperty FileName($File.Name)
                        $DocumentData | Add-Member NoteProperty FileURL($File.ServerRelativeUrl)
                        $DocumentData | Add-Member NoteProperty CreatedBy($Item["Author"].Email)
                        $DocumentData | Add-Member NoteProperty CreatedOn($File.TimeCreated)
                        $DocumentData | Add-Member NoteProperty ModifiedBy($Item["Editor"].Email)
                        $DocumentData | Add-Member NoteProperty LastModifiedOn($File.TimeLastModified)
                        $DocumentData | Add-Member NoteProperty Size-KB([math]::Round($File.Length/1KB))

                    }
  
                    
                        
                    #Add the result to an Array
                    $DocumentInventory += $DocumentData
                }
                #Export the result to CSV file
                $DocumentInventory | Export-CSV $ReportOutput -NoTypeInformation -Append
                $Query.ListItemCollectionPosition = $ListItems.ListItemCollectionPosition
            } While($Query.ListItemCollectionPosition -ne $null)
        }
           
        #Iterate through each subsite of the current web and call the function recursively
        ForEach ($Subweb in $Web.Webs)
        {
            #Call the function recursively to process all subsites underneaththe current web
            Get-SPODocumentInventory($Subweb.url)
        }
    }
    Catch {
        write-host -f Red "Error Generating Document Inventory!" $_.Exception.Message
    }
}
  

[Net.ServicePointManager]::SecurityProtocol = [Net.SecurityProtocolType]::Tls12

#Config Parameters
$SiteCollURL="https://xxxx.sharepoint.com/sites/name"
$ReportOutput="C:\xxx\DocInventory.csv"
$BatchSize = 500
 
#Set user name and password to connect
$UserName="******@yyy.onmicrosoft.com"
$Password = "pass"
 
#Create Credential object from given user name and password
$Cred = New-Object Microsoft.SharePoint.Client.SharePointOnlineCredentials($UserName,(ConvertTo-SecureString $Password -AsPlainText -Force))
    
#Set up the context
$Ctx = New-Object Microsoft.SharePoint.Client.ClientContext($SiteUrl)
$Ctx.Credentials = $Cred


   
#Delete the Output Report, if exists
if (Test-Path $ReportOutput) { Remove-Item $ReportOutput }
   
#Call the function
Get-SPODocumentInventory $SiteCollURL
SharePoint
SharePoint
A group of Microsoft Products and technologies used for sharing and managing content, knowledge, and applications.
11,225 questions
0 comments No comments
{count} votes

1 answer

Sort by: Most helpful
  1. RaytheonXie_MSFT 38,956 Reputation points Microsoft Vendor
    2023-09-19T02:45:46.76+00:00

    Hi @SR VSP ,

    You could try following powershell to retrieve the audit log data and filter for file access events to get the last consultation date for each file in your chosen library.

    Connect-PnPOnline -Url "https://your-sharepoint-site" -UseWebLogin
    $libraryUrl = '/sites/YourSite/LibraryName/'
    $auditData = Get-PnPAuditLog -Query "<Query><Where><And><Eq><FieldRef Name='FileDirRef'/><Value Type='Text'>$libraryUrl</Value></Eq><Eq><FieldRef Name='Event' /><Value Type='String'>View</Value></Eq></And></Where></Query>"
    
    foreach ($entry in $auditData) {
    	$fileUrl = $entry.ItemUrl
    	$lastAccessedDate = $entry.Occurred
    	Write-Host "File URL: $fileUrl, Last Consultation Date: $lastAccessedDate"
    }
    
    

    If the answer is helpful, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

    Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.


Your answer

Answers can be marked as Accepted Answers by the question author, which helps users to know the answer solved the author's problem.