How to get all sharepoint sites and its subsites 'Last Activity' info using powershell?

Question

Hi Guys, I want to get all sites and its subsites information, But the most important thing is, I need the 'Last Activity' Date and Time.

Following are the steps, I've already used:

1. Using CSOM - I found all the results except Last Activity(reference - https://learn.microsoft.com/en-us/answers/questions/1395245/get-last-modified-date-of-all-subsites-in-a-sharep)
2. Using PnP Module -I discovered that all the findings were the same as the CSOM script.
3. Using Graph - I got more information but It provides only for specified range of period was available.(reference - https://www.sharepointdiary.com/2019/11/sharepoint-online-usage-reports-using-graph-api-powershell.html)

Need : I have to list all sites and its subsites information like site name, url, template, last activity and so on. Anyone having knowledge about this, help me to find out!

Answer 1

Hi @Hsaniba，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"
}

Here is the document for more details

https://pnp.github.io/powershell/cmdlets/Get-PnPAuditing.html

https://pnp.github.io/powershell/cmdlets/Get-PnPUnifiedAuditLog.html

---

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.