How to export, configure, and view audit log records

How to export, configure, and view audit log records in Microsoft Purview

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.

1. Run an audit log search in Microsoft Purview Audit and customize the search criteria if required until you get the desired results.
2. Click the Export option in the search results page. This action exports all audit records retrieved from the audit log search you ran and adds this raw data to a CSV file. Please note that this process may take some time, especially for larger searches.
3. Once the export is finished, you'll see a message at the top of the window prompting you to open the CSV file and save it to your local computer. Additionally, you can find this CSV file in the Downloads folder.

Format the exported audit log using the Power Query Editor

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.

1. Open a new workbook in Excel for Office 365, Excel 2019, or Excel 2016.
2. Go to the Data tab and locate the Get & Transform Data section. From there, choose From Text/CSV .
3. Open the downloaded CSV file.
4. In the displayed window, click on Transform Data . This opens the CSV file in the Query Editor , where you'll find four columns: CreationDate , UserIDs , Operations , and AuditData . The AuditData column is a JSON object containing multiple properties, which need to be further split so that there is a column for each property.
5. Right-click on the title in the AuditData column, then select Transform , and choose JSON .
6. Click the expand icon located in the upper-right corner of the AuditData column to reveal a partial list of properties in the JSON objects.
7. To view all properties, click Load more . You can unselect any unnecessary properties to reduce the data displayed in the audit log.
8. Next, decide on the format for the column titles added for each JSON property selected.
   1. Uncheck the Use original column name as prefix checkbox to use the JSON property names as column titles (e.g., RecordType or SourceFileName).
   2. Alternatively, leave the checkbox selected to add the AuditData prefix to the column titles (e.g., AuditData.RecordType or AuditData.SourceFileName).
9. Click OK . This action splits the AuditData column into multiple columns, each corresponding to a property in the AuditData JSON object. Each row in the column contains the value for the respective property. If a property doesn't contain a value, the cell will remain empty.
10. Finally, in the Home tab, click Close & Load to exit the Power Query Editor and open the updated CSV file in an Excel workbook.

Search and export audit logs records using PowerShell

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

How to mail-export, configure, and view audit log records in M365 Manager Plus

1. Log in to M365 Manager Plus.
2. Switch to Auditing & Monitoring and navigate to the Audit tab.
3. Here, you can view the audit logs for various Microsoft 365 services such as Exchange Online, Microsoft Teams, and SharePoint Online.
4. Choose an audit report. (For this example, we are choosing the Mailbox Create and Delete Activities .)
5. Go to Exchange Online > Mailbox > Mailbox Create and Delete Activities .
6. In the window displayed, select the period during which you want the results from.
7. You can also filter according to Business Hours and the Virtual Tenant .
8. You can view the search results and the graph.
9. To export the search results, click Export As and choose the format in which you want it to be exported.
10. You can filter out the report even before exporting them.
11. In the search results, click on the Filter icon. Add the filter conditions and click Filter.
12. You can also Add/Remove Columns to filter the report.
13. You can schedule these audit profiles so that these reports can be periodically generated automatically.