Direct Inward Dialing: +1 408 916 9890
Exporting, configuring, and viewing audit log records is crucial for monitoring user activities, identifying security threats, and ensuring regulatory compliance. These practices allow organizations to safeguard their data, streamline operations, and maintain trust.
Once you've searched the audit log and downloaded the results to a CSV file, you'll notice a column labeled AuditData. This column contains additional information about each event in the form of a JSON object. Within this JSON object, multiple properties are configured as property:value pairs, separated by commas. To analyze this data effectively, you can use the JSON transform feature in the Power Query Editor within Excel. This feature allows you to split each property in the JSON object within the AuditData column into separate columns, making it easier to sort and filter based on specific properties. This capability streamlines the process of locating the precise auditing data you require.
The tables below compare how to export, configure, and view audit log records using Microsoft Purview and Exchange Online PowerShell and M365 Manager Plus.
Export audit log search results
First, you need to search the audit log and then export the results in a CSV file to your local computer.
Now, you can use the JSON transform feature within the Power Query Editor in Excel to separate each property in the JSON object within the AuditData column into individual columns. Once this is done, you can filter the columns to view records based on specific property values, allowing you to easily view the desired auditing data.
Instead of using the Microsoft Purview portal, you can use the Search-UnifiedAuditLog cmdlet in Exchange Online PowerShell to export audit log search results to a CSV file. Then, follow the same procedure above to format the log using the Power Query editor. The PowerShell cmdlet allows you to search for specific service events with the RecordType parameter. Here are examples of exporting audit records to a CSV file for use in the Power Query editor.
Use the following commands to return all records related to SharePoint sharing operations:
$auditlog = Search-UnifiedAuditLog -StartDate 06/01/2019 -EndDate 06/30/2019 -RecordType SharePointSharingOperation
$auditlog | Select-Object -Property CreationDate,UserIds,RecordType,AuditData | Export-Csv -Path c:\AuditLogs\PowerShellAuditlog.csv -NoTypeInformation
The search results are exported to a CSV file that contains four columns: CreationDate, UserIDs, RecordType, and AuditData.
You can also use the name or enum value for the record type as the value for the RecordType parameter. In this parameter, you can include only a single value. To search for audit records of other record types, rerun the previous two commands, specifying a different record type each time, and update the new results to the original CSV file.
For example, you can use the following commands to add SharePoint file activities from the same date range to the file.
$auditlog = Search-UnifiedAuditLog -StartDate 06/01/2019 -EndDate 06/30/2019 -RecordType SharePointFileOperation
$auditlog | Select-Object -Property CreationDate,UserIds,RecordType,AuditData | Export-Csv -Append -Path c:\AuditLogs\PowerShellAuditlog.csv -NoTypeInformation
View, filter, and export audit log records from a single console.