Tag Archives: SharePoint Online Audit Log

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.

Exporting Office 365 audit logs

Open: https://protection.office.com/#/homepage

Login with your tenant admin account. It may be that you can use an account with less excessive rights, however I can’t test that at this moment.

Office 365 Protection Center

In the Protection Center, click on Reports in the left hand navigation pane, then click on View Reports

Office 365 Protection Center Reports
Then click on Office 365 audit log report in the main body of the page

This will open a popup that can show you the audit log.

Select an appropriate Start date, normally 1 month in the past. By default it will start with only the last week selected and will not allow you get more than a month of data at one time, probably to reduce the data size to something more manageable.
(Correction: You can do more than a month, I just hit issues because I selected the day I started logging, which failed because I started logging at mid-day but asked for logs from the start of the day)
Select date range for audit report

Click Search to get all the audit logs for that period.

Scroll to the top right corner, in the ridiculously small window that you can’t even re-size in my older version of IE.

Click on Export Results and in the dropdown click on Download all results

Export results from Office 365
This will give you a file download dialog
Confirmation save box

Save the file, or Save As to pick the location

It will then download the file, this may take some time, in limited tests I have seen ~100KB/sec transfer and file sizes of approximately 4-6MB per user per month.

Zip the file up before sending it anywhere, the content is highly compressible with roughly 95% reduction in size.
Compressed and uncompressed log files