Table of Contents
Office 365 Developers Take 237 Days to Fix Compliance Issue
In September 2018, I reported the failure of a PowerShell demo at the UK Evolve conference. The failure was not my fault (my code was, of course, immaculate). It was caused by a truncation of data in Office 365 audit records generated for group creation (the add group operation).
Bafflingly, the same code had worked perfectly during a similar demo at the European Collaboration Summit in late May. Something had changed inside Office 365 to cause the truncation. Checking the audit records in my tenant revealed that the change to the audit record structure happened between July 5 and July 11. And as it turned out, the truncation affected other group actions too, like adding a member to a group or deleting a group.
A Chat at Ignite
I reported the problem to Microsoft on September 12. Two weeks later, I met with some Exchange engineers at the Ignite conference in Orlando to review the problem. I’m not sure that Microsoft understood that a failure in audit records was a serious issue in terms of compliance, but I did my best to emphasize that it was unacceptable for audit records to be compromised. I went away from the meeting happy that the problem was understood and would be corrected.
Alas, the problem wasn’t quite as easily fixed as I anticipated. In fact, the fix only became available on May 7, some 237 days since the original problem report and 306 days since a code update introduced the issue to the Office 365 audit log. The fix also applies to the other group-associated events that were truncated before.
Holes Remain in the Office 365 Audit Log
Truncated records remain in the Office 365 audit log and will not be backfilled. This means that any group creation or update event stored in the audit log since early July 2018 is truncated. Office 365 E5 tenants have access to audit record for 365 days while the records for other tenants are cleared after 90 days. Untruncated events are available in Office 365 Cloud App Security or third-party products like Quadrotech’s Radar for Security and Audit that ingest audit data from Office 365 without going through the audit log.
Office 365 tenant administrators might not have been aware of the problem because Microsoft filtered the truncated events out from the set shown in Security and Compliance Center audit log searches. The only way to find the problematic events was with the Search-UnifiedAuditLog cmdlet. Hopefully, the fixed events will now reappear in the audit log search.
So So Slow
Since last September, I have been in contact with multiple people inside the Office 365 development group to try and advance the fix. It’s been a source of wonderment and frustration to me that Microsoft could leave an obvious gaping hole in an audit/compliance function for so long. It has not been their proudest hour.
In any case, the fix is in and truncation has stopped (at least for these records). We should be thankful for small mercies.
For more information about the Office 365 audit log and many practical examples of how to interrogate its contents, read Chapter 21 of the Office 365 for IT Pros eBook. We’ve been complaining about the truncation problem since last September because it affected one of the examples used in Chapter 21. All fixed in the next update.
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
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…