There are many instances in which you will want to determine the Last User Activity of a SharePoint or OneDrive Site was. It could be monitor adoption following a Migration, it could be as part of a support call or simply as part of your on-going governance.
However, you may have found as I did that there are many timestamps available to you, all showing you different information. I’ve been through this pain, so I thought I’d share my findings to hopefully save you the same pain and time.
1. SharePoint Online PowerShell
Firstly, you’ve got Get-SPOSite as part of the SharePoint Online PowerShell module. This returns ‘LastContentModifiedDate’ which looks promising at face value.
Truth be told, I was using this quite happily for a while until I started to notice Sites that I knew were not being used. So, I referred to my old friend the Audit Logs. I searched for all activity on a Site that was showing as having a ‘LastContentModifiedDate’ in the past 24 hours.
This is what I found:
The Audit Log was empty, despite the ‘LastContentModifiedDate’ suggesting there had been some form of activity within 24 hours. This is what kickstarted my investigation to find a reliable alternative.
When I spoke to Microsoft about this, they advised that this timestamp is affected by System Activity such as Audit Logs, Content Type Hub subscriptions, Site Usage Recalculation, Permissions changes and Features being activated/Deactivated. However, they confess that they do not have an official article detailing which System activities trigger an update to this timestamp.
But I now knew what I needed to know, not to use this timestamp anymore!
2. SharePoint / OneDrive Usage Reports
Secondly, you can download the SharePoint or OneDrive usage reports from the tenant either via the GUI or via Graph API.
This is a great way to get a quick list of all your SharePoint or OneDrive sites and shows you “Last Activity”. However, almost in complete contrast to the previous method, this often showed no activity at all when I knew there had been at some point in the past. I suspect this is in part caused by the report period also constraining the Last Activity reported (although this didn’t always correlate in my case).
While I think these reports are great to get an indicative view on overall adoption and usage, I don’t think the information is accurate, or at least updated regularly enough to be consumed in any governance processes. This could result in some rather frustrated users.
3. CSOM Web – LastItemUserModifiedDate
Finally we come to the winner. Using CSOM to get the ‘LastItemUserModifiedDate’ seems to be the most accurate method of measuring actual User activity on a site. In my testing, this always reflected the last time I actually did something on the site and was not affected by any system activity and did not seem to expire like the Usage Report activity date seemed to.
When I asked Microsoft about this, they confirmed that this is the most accurate representation of the last User activity on a site and is not updated by background system tasks. Finally, I had my answer!
Here’s how to get the timestamp in PowerShell:
$ctx = New-Object Microsoft.SharePoint.Client.ClientContext($siteUrl)
$credentials = New-Object Microsoft.SharePoint.Client.SharePointOnlineCredentials($spCred.Username, $spCred.Password)
$ctx.Credentials = $credentials
$web = $ctx.Web
$ctx.Load($web)
$ctx.ExecuteQuery()
$web.LastItemUserModifiedDate
Conclusion
There are many others that I have not mentioned here, but I think the three I have mentioned are the most commonly used so most likely to be mistaken from reliable sources of activity to underpin some important decisions. If you want to get an accurate view on whether there has been User Activity on a Site, CSOM is your answer. At least, until there is a simple PnP Cmdlet which I am sure there will be very soon (if there isn’t already and I just don’t know about it!).
