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"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.
After (parsed Microsoft Purview export)
JSON data from the AuditData column expanded into its own CSV file.
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
Example: Pivot by successful operations
I hope this helps someone looking to achieve a similar level of analysis of the M365 unified audit log from Microsoft Purview.