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 only, 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.