Category Archives: Office 365

Splitting Office 365 Audit Logs

Office 365 audit exports are quite complicated behind the scenes. If you look at the data in CSV format you’ll quickly see there’s four fields; Time, User, Action and Detail.

The fun is in the Detail field, it’s really a JSON object with all of the interesting data that the audit log holds. Whilst it’s possible to use Excel to expand those objects and convert them into usable CSV content it’s a bit clumsy.

The script below is a first draft at a script to split the content up into separate logs for future analysis.

Notes: This function is not optimised for large files. 

For large files it would be desirable to batch write actions so that no more than (for example)
100 rows are held in memory at once. Immediate write activities are possible but are suspected
to result in excessive disk write activity.
Function Split-O365AuditLogs ()
    #Get the content to process
    $content = Import-Csv $sourcePath
    $datumArray = @();

    foreach ($line in $content)
        #Details are encoded as JSON data.
        $expandedDetails = $line.Detail | ConvertFrom-Json

        #add the non JSON parameters
        Add-Member -InputObject $expandedDetails -MemberType NoteProperty -Name "Time" -Value $line.Time
        Add-Member -InputObject $expandedDetails -MemberType NoteProperty -Name "User" -Value $line.User
        Add-Member -InputObject $expandedDetails -MemberType NoteProperty -Name "Action" -Value $line.Action
        $datumArray += $expandedDetails
    #Build a list of unique actions
    $actions = $datumArray | select Action -Unique -ExpandProperty Action

    foreach ($action in $actions)
        $datumArray | ? {$_.Action -eq $action} | ConvertTo-Csv | Out-File -FilePath ("{0}\{1}.csv" -f $resultsFolderPath, $Action) -Append

There’s plenty of room for improvement. It’s highly likely that the UserType field is the key to exporting to a more concise set of exports that share common fields. If anyone’s interested i’ll give it another go.