Extract AuditData JSON to CSV in M365 Purview audit log search

Decorative image - M365 Purview and PowerShell logos with json and csv formatted text between them

All product names, logos, and brands used in this post are property of their respective owners.

Searching and analyzing the M365 unified audit log in Microsoft Purview is not one of my favorite activities. I cannot think of a situation where the circumstances surrounding the search were joyous! But alas, the audit log search is powerful, and search/analysis is inevitable for any M365 administrator.

In my experience, the CSV download/export of the unified audit log from Purview is a little messy. At the time of writing, it includes JSON wrapped by CSV. Some audit event information is available as raw CSV (CreationDate, RecordType, Operation, UserId), but much of the most valuable data is only represented in JSON (the AuditData column). To make matters worse, the JSON schema differs slightly for different types of events, so the names (keys) do not always align. As a result, the CSV export is not readily parseable in Excel, Google Sheets, etc.

PowerShell to the rescue! Taking inspiration from Koen Van Impe, I assembled a crude PowerShell function to parse the M365 Unified Audit Log CSV export into a “real” (Excel-friendly or Sheets-friendly) CSV file. The resulting file is a CSV representation of the AuditData column, which includes all JSON name/value (key/value) pairs in distinct, CSV columns and rows. You can seamlessly sort, filter, and pivot the resulting file to suit your data analysis needs!

Note: This is not a perfect solution. There is a reason the JSON schema differs for different types of audit events, but I am ignoring that and standardizing or dumping any value that corresponds to a specific key into the same column. My goal was ease of analysis, and I was ok with a minor loss of fidelity.

The function is available as a Gist.

Usage is simple and requires two parameters: auditlogsearchresultfile and outputcsvfilepath. The first parameter is the path to the CSV file exported from Purview (the raw version). The second parameter is the path to the new, parsed CSV file the function will create when it executes.

parse-m365-auditlogsearch-result -auditlogsearchresultfile "C:\Temp\141ddc46-ee06-414c-a32c-9057ffb937ab.csv" -outputcsvfilepath "C:\Temp\141ddc46-ee06-414c-a32c-9057ffb937ab-PARSED.csv"
In that example, the raw input file (downloaded from Purview) is C:\Temp\141ddc46-ee06-414c-a32c-9057ffb937ab.csv and the parsed version will be generated in the output file C:\Temp\141ddc46-ee06-414c-a32c-9057ffb937ab-PARSED.csv.

Before (raw Microsoft Purview export)

O365 sample audit log data provided by Invictus Incident Response. It is worth noting that this dataset is formatted slightly differently than an audit log export from Purview I performed in early 2023. However, since the JSON AuditData column exists in both, the function works as expected. Screenshot of raw Microsoft M365 Purview unified audit log in CSV format (viewed in Microsoft Excel) with the AuditData column emphasized (circled in green)

After (parsed Microsoft Purview export)

JSON data from the AuditData column expanded into its own CSV file. Screenshot of parsed Microsoft M365 Purview unified audit log in CSV format (viewed in Microsoft Excel) with the AuditData column expanded with each field in its own, new CSV column

With the normalized CSV format, there are many options for further log analysis; sort, filter, and pivot to name a few.

Example: Filter by workload

Screenshot of parsed Purview CSV data in Google Sheets, filtered by the Workload column - value OneDrive

Example: Pivot by successful operations

Screenshot of parsed Purview CSV data in Google Sheets, pivoted operation and count, filtered by Success and Successful

I hope this helps someone looking to achieve a similar level of analysis of the M365 unified audit log from Microsoft Purview.