Query M365 Audit Log
![Query M365 Audit Log](https://icewolffile.blob.core.windows.net/$web/thumbnails/PowerShell7_thumbnail.jpg)
Hi All,
Recently i was playing around with some M365 Audit Log Querys.
There are many ways how you can query the M365 Audit Log:
- The Audit Log search Microsoft Purview compliance portal
- Search-UnifiedAuditLog
- Management Activity API
- Preview Microsoft.Graph API
Note: Update on the Deprecation of Admin Audit Log Cmdlets
- The Admin Audit Log cmdlets will be deprecated on September 15, 2024.
- The Mailbox Audit Log cmdlets will have a separate deprecation date, which will be announced early next year.
- We encourage customers to begin transitioning to the Unified Audit Log (UAL) cmdlet i.e. Search-UnifiedAuditLog as soon as possible. Alternatively, you can explore using the Audit Search Graph API, which is currently in Public Preview and is expected to become Generally Available by early July 2024.
Additional Infos
- Get started with auditing solutions
- Learn about auditing solutions in Microsoft Purview
- Manage mailbox auditing
Search-UnifiedAuditLog
It’s an Exchange Commandlet.
Connect-ExchangeOnline -ShowBanner:$false
$Start = Get-Date
$StartDate = [datetime]::parseexact("2024-04-01", "yyyy-MM-dd", $null)
$EndDate = [datetime]::parseexact("2024-06-01", "yyyy-MM-dd", $null)
$SessionID = "DemoSearch_" + (Get-Date -Format "yyyyMMdd_HHmm")
$AuditRecords = @()
$AuditRecords = Search-UnifiedAuditLog -Operations MailItemsAccessed -StartDate $StartDate -EndDate $EndDate -ResultSize 5000 -Formatted -SessionCommand ReturnLargeSet -SessionID $SessionID
$AuditRecords.Count
$End = Get-Date
$Timespan = New-Timespan -Start $Start -End $End
$Timespan
Note: The AuditRecords Count is 5'000 so we met or exeeded the Search result
The AuditRecords contains an Attribute AuditData but it’s in JSON Format
$AuditRecords[0]
First we make the
$Records = $AuditRecords | Sort-Object Identity -Unique
$Records.count
Then we convert the AuditData from JSON to a PowerShell Object
$Records[0].AuditData | ConvertFrom-Json
We have detected, that the Result exeeded the maximum of 5000. We need a Workaround. One way is to break down the Search into smaller peaces (1 Hour or 60 Minutes) between StartDate and Enddate.
There exist an example to Use a PowerShell script to search the audit log
I’ve adapted the Script slightly
#Modify the values for the following variables to configure the audit log search.
$Start = Get-Date
$logFile = "C:\Temp\AuditLogSearchLog.txt"
$outputFile = "C:\Temp\AuditLogRecords.csv"
[DateTime]$StartDate = [datetime]::parseexact("2024-04-01", "yyyy-MM-dd", $null) #[DateTime]::UtcNow.AddDays(-90)
[DateTime]$EndDate = [datetime]::parseexact("2024-06-01", "yyyy-MM-dd", $null) #[DateTime]::UtcNow.AddDays(-20)
#$record = "AzureActiveDirectory"
$resultSize = 5000
$intervalMinutes = 60
#Start script
[DateTime]$currentStart = $StartDate
[DateTime]$currentEnd = $EndDate
Function Write-LogFile ([String]$Message)
{
$final = [DateTime]::Now.ToUniversalTime().ToString("s") + ":" + $Message
$final | Out-File $logFile -Append
}
Write-LogFile "BEGIN: Retrieving audit records between $($startdate) and $($enddate), RecordType=$record, PageSize=$resultSize."
Write-Host "Retrieving audit records for the date range between $($startdate) and $($enddate), RecordType=$record, ResultsSize=$resultSize"
$totalCount = 0
while ($true)
{
$currentEnd = $currentStart.AddMinutes($intervalMinutes)
if ($currentEnd -gt $EndDate)
{
$currentEnd = $EndDate
}
if ($currentStart -eq $currentEnd)
{
break
}
$sessionID = [Guid]::NewGuid().ToString() + "_" + "ExtractLogs" + (Get-Date).ToString("yyyyMMddHHmmssfff")
Write-LogFile "INFO: Retrieving audit records for activities performed between $($currentStart) and $($currentEnd)"
Write-Host "Retrieving audit records for activities performed between $($currentStart) and $($currentEnd)"
$currentCount = 0
$sw = [Diagnostics.StopWatch]::StartNew()
do
{
$results = Search-UnifiedAuditLog -StartDate $currentStart -EndDate $currentEnd -Operations MailItemsAccessed -SessionId $sessionID -SessionCommand ReturnLargeSet -ResultSize $resultSize
if (($results | Measure-Object).Count -ne 0)
{
$results | export-csv -Path $outputFile -Append -NoTypeInformation
$currentTotal = $results[0].ResultCount
$totalCount += $results.Count
$currentCount += $results.Count
Write-LogFile "INFO: Retrieved $($currentCount) audit records out of the total $($currentTotal)"
if ($currentTotal -eq $results[$results.Count - 1].ResultIndex)
{
$message = "INFO: Successfully retrieved $($currentTotal) audit records for the current time range. Moving on!"
Write-LogFile $message
Write-Host "Successfully retrieved $($currentTotal) audit records for the current time range. Moving on to the next interval." -foregroundColor Yellow
""
break
}
}
}
while (($results | Measure-Object).Count -ne 0)
$currentStart = $currentEnd
}
Write-LogFile "END: Retrieving audit records between $($start) and $($end), RecordType=$record, PageSize=$resultSize, total count: $totalCount."
Write-Host "Script complete! Finished retrieving audit records for the date range between $($startdate) and $($enddate). Total count: $totalCount" -foregroundColor Green
$End = Get-Date
$Timespan = New-Timespan -Start $Start -End $End
$Timespan
It took almost half an Hour to get these Audit Log Records. And there is no guarantee that we exceeded somewhere the 5'000 Results in one Hour especially in large Tenants.
Graph API Permissions
We need to have an Entra Application with the following Permissions:
- AuditLog.Read.All
- AuditLogsQuery.Read.Al
Using the Microsoft.Graph PowerShell to Connect with a Certificate
The script is also available on my GitHub Repo
#Azure AD App > Icewolf
$TenantId = "46bbad84-29f0-4e03-8d34-f6841a5071ad"
$AppID = "99d8df8d-67b6-4a3a-b915-5cfc835fbfc7" #AuditLog
$CertificateThumbprint = "07EFF3918F47995EB53B91848F69B5C0E78622FD"
Connect-MgGraph -ClientId $AppID -TenantId $TenantId -CertificateThumbprint $CertificateThumbprint -NoWelcome
Create the Search and we get back a SearchID
###############################################################################
# Create Search
###############################################################################
Write-Output "Create Array"
$OperationsArray = @()
$OperationsArray += "MailItemsAccessed"
Write-Output "Create Search"
$DisplayName = "DemoSearch_" + (Get-Date -Format "yyyyMMdd_HHmm")
[String]$StartDate = [datetime]::parseexact("2024-04-01", "yyyy-MM-dd", $null).Tostring("yyyy-MM-ddT00:00:00Z")
[String]$EndDate = [datetime]::parseexact("2024-06-01", "yyyy-MM-dd", $null).Tostring("yyyy-MM-ddT00:00:00Z")
$Uri = "https://graph.microsoft.com/beta/security/auditLog/queries"
$SearchParameters = @{
displayName = "$DisplayName"
filterStartDateTime = "$StartDate"
filterEndDateTime = "$EndDate"
recordTypeFilters = @("ExchangeItemAggregated")
operationFilters = @("MailItemsAccessed")
}
Write-Output "Invoke Search"
$SearchQuery = Invoke-MgGraphRequest -Method POST -Uri $Uri -Body $SearchParameters
$SearchId = $SearchQuery.Id
Write-Output "Searchid: $SearchId"
If ($SearchId -eq $null -or $SearchId -eq "")
{
Write-Output "No SearchId > Aborting Script"
#Exit
}
Now we can check for the Status of that SearchID
###############################################################################
# Check if SearchQuery Suceeded
###############################################################################
Write-Output "Wait for Search to complete"
#$AuditSearch = Get-MgBetaSecurityAuditLogQuery -AuditLogQueryId $SearchId | fl
$AuditSearch = Get-MgBetaSecurityAuditLogQuery -AuditLogQueryId $SearchId
$AuditSearchStatus = $AuditSearch.Status
Write-Output "Status: $AuditSearchStatus"
While ($AuditSearch.Status -ne "succeeded")
{
$AuditSearch = Get-MgBetaSecurityAuditLogQuery -AuditLogQueryId $SearchId
$AuditSearchStatus = $AuditSearch.Status
Write-Output "Status: $AuditSearchStatus"
Start-Sleep -Seconds 60
}
When the Search is suceeded we can start downloading that data
###############################################################################
# Get Data from SearchQuery
###############################################################################
Write-Output "Loop through results"
$Uri = ("https://graph.microsoft.com/beta/security/auditLog/queries/{0}/records" -f $SearchId)
[array]$SearchRecords = Invoke-MgGraphRequest -Uri $Uri -Method GET
$AuditRecords += $SearchRecords.value
# Paginate to fetch all available audit records
$NextLink = $SearchRecords.'@Odata.NextLink'
While ($null -ne $NextLink) {
$SearchRecords = $null
[array]$SearchRecords = Invoke-MgGraphRequest -Uri $NextLink -Method GET
$AuditRecords += $SearchRecords.value
Write-Host ("{0} audit records fetched so far..." -f $AuditRecords.count)
$NextLink = $SearchRecords.'@odata.NextLink'
}
$AuditRecordCount = $AuditRecords.Count
Write-Output "Audit Records found: $AuditRecordCount"
It took only about 15 Minutes - half of the Time of the Search-UnifiedAuditLog and we have the guarantee, we didn’t miss any record.
$End = Get-Date
$Timespan = New-Timespan -Start $Start -End $End
$Timespan
Another Plus is that the Records are already PowerShell Object’s and there is no need to do a JSON Conversion.
$AuditRecords[0].Auditdata
Some additional Querys with the Result
$AuditRecords.Auditdata | where-object {$_.UserId -eq "a.bohren@icewolf.ch"} | select-object -First 1
$AuditRecords.Auditdata | where-object {$_.UserId -eq "a.bohren@icewolf.ch"} | measure
Azure Automation
You can use the similar Code to retreive Auditlog Data with the Graph API in a Azure Automate Runbook.
If your query is big or contains a result make sure you don’t exceed the 2 GB Limit
Regards
Andres Bohren