Support
 
PhoneGet Quote
 
Support
 
US Sales: +1 888 720 9500
US Support: +1 844 245 1108
Intl: +1 925 924 9500
Aus: +1 800 631 268
UK: 0800 028 6590
CN: +86 400 660 8680

Direct Inward Dialing: +1 408 916 9890

 
 

How to export, configure, and view audit log records

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.

Microsoft 365

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

Click to copy entire script

    $auditlog | Select-Object -Property CreationDate,UserIds,RecordType,AuditData | Export-Csv -Path c:\AuditLogs\PowerShellAuditlog.csv -NoTypeInformation

Click to copy entire script

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	

Click to copy entire script

    $auditlog | Select-Object -Property CreationDate,UserIds,RecordType,AuditData | Export-Csv -Append -Path c:\AuditLogs\PowerShellAuditlog.csv -NoTypeInformation

Click to copy entire script

M365 Manager Plus

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.

Limitations of using Microsoft 365

  • Administrators should have the required role to access individual native admin centers and execute tasks in them.
  • The exporting and filtering process is long and tedious.

Benefits of using M365 Manager Plus to export audit log reports

  • Easily view, filter, and export audit log records from a single window. You can also automate the report profiles to generate these detailed reports without manual intervention periodically.
  • Assign tasks to any technician without needing to grant them elevated native privileges.
  • Manage AD and Microsoft 365 objects in bulk and automate repetitive tasks.
  • Generate intuitive reports and keep an eye on user and admin activities in your Microsoft 365 environment.
  • Ensure compliance with detailed audit reports.
  • Monitor the health and performance of your Microsoft 365 services 24/7.
  • Delegate custom roles to help desk technicians and reduce the administrator's workload.

View, filter, and export audit log records from a single console.

Try now for free

  • Streamline your Microsoft 365 governance and administration with M365 Manager Plus

Related Resources

A holistic Microsoft 365 administration and security solution
 
x