Query M365 Audit Log

Andres Bohren

2024-07-15

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

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
$URI = "https://graph.microsoft.com/beta/security/auditLog/queries/$searchId"
$AuditSearch = Invoke-MgGraphRequest -Method "GET" -Uri $Uri
$AuditSearchStatus = $AuditSearch.Status
Write-Output "Status: $AuditSearchStatus"
While ($AuditSearch.Status -ne "succeeded")
{
	#$AuditSearch = Get-MgBetaSecurityAuditLogQuery -AuditLogQueryId $SearchId
	$URI = "https://graph.microsoft.com/beta/security/auditLog/queries/$searchId"
	$AuditSearch = Invoke-MgGraphRequest -Method "GET" -Uri $Uri
	$AuditSearchStatus = $AuditSearch.Status
	Write-Output "Status: $AuditSearchStatus"
	Start-Sleep -Seconds 60

	If ($AuditSearchStatus -eq "failed")
	{
		Write-Output "Audit Search failed - aborting Script"
		Exit
	}
}

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