by 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.
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
$Session = New-PSSession -ConfigurationName Microsoft.Exchange -ConnectionUri https://outlook.office365.com/powershell-liveid/ -Credential $Credential -Authentication Basic -AllowRedirection Import-PSSession $Session -AllowClobber 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.
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
$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 – https://docs.microsoft.com/en-gb/office365/securitycompliance/search-the-audit-log-in-security-and-compliance#sharing-and-access-request-activities ## 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 – 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:
- Low cost storage
- Easy connection and data retrival
- NO SQL format allows storing information in multiple schema formats easily
- Data Types can be easily set and managed
Below is the script for the same.
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| [Reflection.Assembly]::LoadWithPartialName("System.Web.Script.Serialization") | |
| $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 | |
| } | |
| Disconnect-AzureRmAccount | |
| } |
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.
Tech Huddle 
Where is the powershell?
LikeLike
Seems like there was a probleme with the earlier theme. Have updated the theme now and seems like code is shown properly. Let me know if it still doesn’t come up.
LikeLike