The Sad Case of Truncated Office 365 Audit Events

2 Replies to “The Sad Case of Truncated Office 365 Audit Events”

1. Greetings, Tony,

   Sadly, the records are still being truncated — though it looks to me like they have increased the character limit slightly to 3315.

   I used the script that you published in chapter 21 as a basis to build upon, and spent the day creating a script that takes the truncated input and closes the JSON loop gracefully, so it can at least collect what data is available without generating continuous errors…

   My script uses the importExcel module https://github.com/dfinke/ImportExcel – which I highly recommend – but for admins who are purists, they could skip that and covert it to csv…

   It imports the Add Group records for the last 90days, and adds them to a spreadsheet, sorting it newest to oldest.

   on subsequent runs, it only adds new records to the spreadsheet.

   I’m a PowerShell enthusiast , but not a Guru, so I am hoping you might take a look at it and spin it pretty…

   # script Follows

   cls;$error.clear()
   import-module ImportExcel
   # Audit Add Group
   $reportFile = “\\contoso.com\ops\Prod\Reports\O365\Audit-AddGroup\Audit-AddGroup.xlsx”
   if (Test-Path -LiteralPath $reportFile){$import = Import-Excel $reportFile}else{$import = “”}
   $Records = Search-UnifiedAuditLog -StartDate “$((Get-Date).AddDays(-90))” -EndDate “$((Get-Date))” -Operations “Add Group” -ResultSize 5000

   If ($Records.Count -eq 0)
   {
   Write-Host “No Add Group records found.”
   }
   Else
   {
   Write-Host “Processing $($Records.Count) records.”
   $attribs = @()
   $attribs += $($records | select -first 1 | gm | ?{$_.MemberType -match “Property” -and $_.Name -notmatch “AuditData”} | select Name).name
   $Report = @()
   ForEach ($Rec in $Records)
   {
   Try
   {
   $ReportLine = new-object psobject
   $attribs | %{ Add-Member -InputObject $ReportLine -MemberType NoteProperty -Name $_ -Value $Rec.$_}
   $AuditAttribs = $(ConvertFrom-Json ( $rec.AuditData) | gm |?{$_.MemberType -match “Property” -and $_.Name -notmatch “RecordType”} | select name).name
   $AuditData = ConvertFrom-Json $($Rec.Auditdata -replace ‘\[\\u000d\\u000a ‘,”” -replace ‘\\u000d\\u000a\]’,””)
   $AuditAttribs = $($AuditData | gm |?{$_.MemberType -match “Property” -and $_.Name -notmatch “RecordType”} | select name).name
   $auditdata.ExtendedProperties | % { if (($_.Name -eq “resultType”) -or ($_.Name -eq “Version”)){}else{Add-Member -InputObject $ReportLine -MemberType NoteProperty -Name “$($_.Name)” -Value “$($_.Value)”}}
   $auditdata.ModifiedProperties | % {Add-Member -InputObject $ReportLine -MemberType NoteProperty -Name “$($_.Name)” -Value “$($_.NewValue)”}
   }
   catch
   {
   $ReportLine = new-object psobject
   $attribs | %{ Add-Member -InputObject $ReportLine -MemberType NoteProperty -Name $_ -Value $Rec.$_}
   $AuditTemp = $rec.auditdata -replace ‘\[\\u000d\\u000a ‘,”” -replace ‘\\u000d\\u000a\]’,””
   $endOfLine = [Regex]::Matches($AuditTemp,”(,)[^,]*$”)
   while($endOfLine.value -notmatch'”Name”‘)
   {
   write-host $($endoLine.value)
   $AuditTemp = $AuditTemp -replace “(,)[^,]*$”,””
   $endOfLine = [Regex]::Matches($AuditTemp,”(,)[^,]*$”)
   }
   $AuditTemp = $AuditTemp -replace “(,)[^,]*$”,””
   $AuditTemp = $AuditTemp +”]}”
   $AuditData = convertfrom-json $AuditTemp
   $AuditAttribs = $($AuditData | gm |?{$_.MemberType -match “Property” -and $_.Name -notmatch “RecordType”} | select name).name
   if ($auditdata.ExtendedProperties) {
   $auditdata.ExtendedProperties | % { if (($_.Name -eq “resultType”) -or ($_.Name -eq “Version”)){}else{Add-Member -InputObject $ReportLine -MemberType NoteProperty -Name “$($_.Name)” -Value “$($_.Value)”}}}
   $auditdata.ModifiedProperties | % {Add-Member -InputObject $ReportLine -MemberType NoteProperty -Name “$($_.Name)” -Value “$($_.NewValue)”}
   }
   $sum = 0
   $import | %{if (($ReportLine.CreationDate -eq $_.CreationDate) -and ($ReportLine.Id -eq $_.Id)) {$Sum ++}}
   if ($sum -eq 0){$Report += $ReportLine}
   }
   }
   $Report | select * -ExcludeProperty PSComputerName,PSShowComputerName,RunspaceId,result* | Export-Excel -path $reportFile -Append -AutoFilter -Autosize -FreezeTopRow -NoNumberConversion *
   Import-Excel $reportFile | sort CreationDate -Descending | Export-Excel -path $reportFile -AutoFilter -Autosize -FreezeTopRow -NoNumberConversion *

   #End Script

   Loading...

   Reply

   1. Greetings… I just checked what iI see in my tenant and the newer Add Group records (for example, 21 June 2019) are OK. Older ones are not (for example, 12 April 2019) because Microsoft hadn’t deployed the fix then. Is that what you see in your tenant? I know Microsoft don’t plan to backfill and fix the old bad records, so your script is needed…

      Loading...

      Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.