Table of Contents
Exploit File Operations Audit Events to Find Who Accessed a Document Last
I’m speaking about how to master the unified (Microsoft 365) audit log at the European SharePoint Conference (ESPC) event in Stockholm in early December. At this point in the proceedings, the normal panic about putting together a presentation is in full swing, and I’ve been busy creating slides and examples.
In May 2024, I published an article about how to use the Microsoft Graph PowerShell SDK to create a report of files in a SharePoint Online document library. The idea is that it’s hard to understand everything that’s in a document library by scrolling through file details in the SharePoint browser app. Sometimes it’s just easier to see things in a report, and it’s definitely easier to figure out which files can be removed to clean up the document library. The temptation to leave well alone is deep in us all, but cleaning out old files from SharePoint has two benefits: it returns some storage quota, and it eliminates some of the potential for digital rot that can affect AI results.
A reader asked if the SharePoint files report could include the last accessed date for documents. The Graph API to List children of a drive item (folder) or the equivalent SDK Get-MgDriveItemChild cmdlet doesn’t return a last accessed date as far as I can see, so some other method must be used.
Analyzing SharePoint Online File Operations Audit Events
The unified audit log is a feature available to all tenants with Office 365 E3 or higher licenses. SharePoint Online creates a profusion of audit events that the audit log ingests on an ongoing basis. In this case, we’re interested in the FileAccessed event, which is logged when someone opens a file. Other events are logged for creation (FileUploaded), modification (FileModified), downloaded (FileDownload), and so on. You might be surprised at how many file operation events are logged for a busy SharePoint Online site. Figure 1 shows the count of file operations for some of documents used to generate the Office 365 for IT Pros eBook over the last six months.
Scripting a Solution Based on File Operations Audit Events
The outline of the PowerShell script to answer the request is:
- Connect to Exchange Online with an administrator account.
- Run the Search-UnifiedAuditLog to find SharePoint file operations audit events for the target site over whatever period is required. Office 365 E3 tenants store audit events for 180 days. E5 tenants store events for 365 days. Remove any duplicates that might have been fetched from the audit log. You could also interrogate the audit log with the Graph AuditLog Query API, but richer information is fetched by Search-UnifiedAuditLog.
- Filter out file events logged by human users. SharePoint Online has many background processes to do things like clean out the recycle bin, preserve files for retention, and so on. We’re not interested in system events.
- The full set of file operation events can be used to generate statistics, such as the count of user activity over the period, or the number of operations for individual files. We’re interested in file access events and use FileModified and FileAccessed events to generate this information, so the script populates a separate array with those events.
- By grouping the file access events by file name and sorting the events by date, we can easily extract the last accessed date for each file. The result is something like this:
File User Timestamp ---- ---- --------- 01 Introduction and Overview.docx paul.robichaux@office365itpros.com 31-Oct-2024 12:34:06 02 Managing Identities.docx tony.redmond@office365itpros.com 31-Oct-2024 14:12:54 03 Tenant Management.docx paul.robichaux@office365itpros.com 31-Oct-2024 20:21:47 04 User Management.docx paul.robichaux@office365itpros.com 31-Oct-2024 20:21:48 05 Managing Exchange Online.docx Andy.Ruth@office365itpros.com 29-Oct-2024 20:45:03 06 Managing Mail Flow.docx James.ryan@office365itpros.com 29-Sep-2024 15:07:31 07 Managing SharePoint Online.docx tony.redmond@office365itpros.com 14-Oct-2024 13:00:56 08 Managing Tasks.docx paul.robichaux@office365itpros.com 29-Oct-2024 19:40:47 09 Managing Video.docx paul.robichaux@office365itpros.com 29-Oct-2024 19:40:47 10 Managing Microsoft 365 Groups.docx brian.weakliamoffice365itpros.com 20-Oct-2024 17:49:23 11 Teams Architecture and Structure.docx tony.redmond@office365itpros.com 16-Oct-2024 15:02:20 12 Managing Teams.docx Lotte.Vetler@office365itpros.com 04-Nov-2024 19:01:57
Two odd user identifiers for bdc6105c-4e11-4050-82e6-6549f9b99b89 and eba15bfd-c28e-4433-a20e-0278888c5825 can appear in file operation events. I assume these identifiers belong to background SharePoint Online processes, so the script filters these events from the set.
You can download the complete script from GitHub.
Good Example of the Power of the Audit Log
Finding who last accessed SharePoint Online documents and when that access occurred is a good example of why the unified audit log is a great repository of information for tenant administrators and forensic investigators alike. If you’re at ESPC 24 in Stockholm, come along to my session on Decoding the Microsoft 365 Audit Log on Tuesday, December 3 at 10:30am. I’ll share more useful tips about exploiting the audit log there.
Insight like this doesn’t come easily. You’ve got to know the technology and understand how to look behind the scenes. Benefit from the knowledge and experience of the Office 365 for IT Pros team by subscribing to the best eBook covering Office 365 and the wider Microsoft 365 ecosystem.
Hi Tony
This is great. Please let me know how can I exclude app@sharepoint entries from the report.
Thank you
I got it.
Again Thank you.
The version now online in GitHub excludes app@sharepoint.
Unfortunately I still see the below entry
05-Nov-2024 10:19:07 app@sharepoint FileAccessed
Did you download the code from GitHub that contains the line:
$FilesAccessed = $FilesAccessed | Where-Object {$_.User -ne ‘app@sharepoint’}
If so, you can debug the code from that point to find out why these records are getting through. They don’t here.
I found some additional GUIDs for unknown SharePoint background processes that I need to investigate, so I updated the script to V1.1. Online now.
And after doing some more tests, I concluded that the combination of FileAccessed and FileModified events gives the most accurate picture of the last accessed date for files, so I modified the script to match.
Tony, can this script be used to find all files that haven’t been access / modified in “X” Years? I would like to find stale files that haven’t been accessed or modified since 01/01/2021.
Nope. Audit records only go back a year… and anyway, you wouldn’t find audit records if files aren’t accessed or modified. To check the modified date for files, you’d use something like the script in https://practical365.com/sharepoint-online-files-report/.