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

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

    $Session = New-PSSession -ConfigurationName Microsoft.Exchange -ConnectionUri -Credential $Credential -Authentication Basic -AllowRedirection
    Import-PSSession $Session -AllowClobber

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

  2. 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.

    $data = Search-UnifiedAuditLog
    -StartDate $startInterval
    -EndDate $endInterval
    -Operations FileAccessed, FilePreviewed, PageViewed, PageViewedExtended, SearchViewed, CompanyLinkUsed, SecureLinkUsed, FileDownloaded, FileModified, FileUploaded, FileDeleted, FolderModified, CompanyLinkCreated, SharingInheritanceBroken, ListUpdated, FileSyncDownloadedFull, FileSyncUploadedFull
    -SessionId $sessionId
    -SessionCommand ReturnLargeSet
    -ResultSize 5000
    ## Start Date – Date and Time in local Computer Date Time format
    ## End Date – Date and Time in local Computer Date Time format
    ## Operations – For more information see here –
    ## Session Id – Essential for paging and handling when data goes more than threshold limit
    ## Session Command – Determines the size of the data returned
    ## ResultSize – 5000 seems to be the upper limit for a successfull data pull

    To get more information about more parameters here –

  3. 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.

$resourceName = "<Resource Group Name>"
$azureStorage = "<Storage Container Name>"
$azureTable = "<Azure Storage Table>"
$Credential = Get-Credential
function UploadDataToAzureTableStorage {
param (
[Parameter(Mandatory = $true, HelpMessage = 'Please provide the FileName')][ValidateNotNullOrEmpty()][String]$fileName
Write-Output "Connecting to Azure Subscription"
Connect-AzureRmAccount -Credential $Credential
Write-Output "Connect to Storage Container"
$storageCont = Get-AzureRmStorageAccount -ResourceGroupName $resourceName -Name $azureStorage
$ctx = $storageCont.Context
Write-Output "Get Office 365 Audit Log Data Table"
$table = Get-AzureStorageTable -Name azureTable -Context $ctx
$partitionKey = "<PartitionKey>"
$date = Get-Date -Format s
$rowKeyPrefix = "Data_" + $date.Replace(":", "")
$JSSerializer = [System.Web.Script.Serialization.JavaScriptSerializer]::new()
$dataJson = (Get-Content -Path $fileName) | Out-String | ConvertFrom-Json;
for ($i = 0; $i -lt $dataJson.Count ; $i++) {
Write-Output $("Adding Row " + $($i + 1))
$hashtable = $JSSerializer.Deserialize(($dataJson[$i] |ConvertTo-Json), 'Hashtable')
$rowKey = $rowKeyPrefix + $i
$null = Add-StorageTableRow -table $table -partitionKey $partitionKey -rowKey ($rowKey) -property $hashtable


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.

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s