Retrieve Office 365 Audit logs using PowerShell and store in Azure table for quick retrieval

To create custom reports for Office 365 events, we could use the Audit logs from Security and Compliance center. The process is quite simple and could be implemented easily using PowerShell. In this blog, we will look at the steps for the same.

Later we will also see how we could store this data in a Azure Storage Table, so it is easy to fetch the data available.

Steps to fetch data from Office 365 Audit log using Exchange Online PowerShell

The first step in the process is to import the commands from Exchange online PowerShell.

In the above script, we are initializing the PowerShell session for Exchange Online PowerShell

After the commands are imported, then we could search the audit log using the Search-UnifiedAuditLog command. Below is the cmdlet and some helpful information about the parameters.

To get more information about more parameters here – https://docs.microsoft.com/en-us/powershell/module/exchange/policy-and-compliance-audit/search-unifiedauditlog?view=exchange-ps

After the audit log data is pulled, the data could be formatted and updated to provide more relevant information about the audited information.
For eg. RecordType and UserType information could be updated provide more information than just numbers.

Updating data into an Azure Table using Azure Storage PowerShell

After the data is processed and ready to be used from the above steps, we can either export this data to a CSV or store it in a Azure Table. For this blog, we will export this into CSV and then import it into an Azure Table. The benefits of Azure Table are as follows:

1. Low cost storage
2. Easy connection and data retrival
3. NO SQL format allows storing information in multiple schema formats easily
4. Data Types can be easily set and managed

Below is the script for the same.

Conclusion

In this blog, we will see how we could search Office 365 Audit, retrieve the data and then store it in a Azure Table for later use.