Office 365 Forensics using PowerShell and Search-UnifiedAuditLog

Oh no. You recently found out the Office 365 account of one of your users was compromised! What to do now???

Your first step should be to minimize the damage by cutting off access for the affected user account. Follow the steps in this article for an immediate and effective block of the compromised account.

Next, you need to investigate what Office 365 resources were accessed. Hopefully you have already enabled auditing in Office 365! If not, then you’re in for some trouble explaining management why this wasn’t in place…

Assuming Office 365 auditing has been in place the whole time you can now start investigating what data has been compromised.

Reviewing Office 365 Audit Logs Using the Security and Compliance Center – and Why It’s Useless…

To examine the Office 365 audit logs open up the Security and Compliance Center and go to Search -> Audit Log Search:

Seucirty and Protection Center Audit Log Search

This will allow you to perform some slow and inconsistent queries…

The audit log search interface in the Security and Compliance Center has two major flaws:

  • It will update dynamically as results are returned
  • It will not inform you that there’s a limit of 5,000 items in your search query

This means that the output is slow and can’t be trusted as a full picture. Additionally the data is difficult to export and work with.

PowerShell to the Rescue!

To work effectively with the Office 365 audit log we need PowerShell. More specifically we must use the following command:

Search-UnifiedAuditLog

It takes several parameters of which these are the most useful ones:

  • StartDate – the earliest date/time in our result set
  • EndDate – the latest date/time in our result set
  • ResultSize – set to 5000 for maximum allowed output of 5000 records
  • UserIDs – list (array) of users that we search for
  • IPAddresses – list (array) of IP addresses we search for

In order to get all log entries in a given time frame you leave out the UserIDs and IPAddresses parameters as these only narrow down the result set.

The following is a sample search of all audit logs seen between 1st of June 2019 and 1st of August 2019 relating to the user Tycho Brahe:

  1. $UserCredential = Get-Credential
  2. $Session = New-PSSession -ConfigurationName Microsoft.Exchange -ConnectionUri https://outlook.office365.com/powershell-liveid/ -Credential $UserCredential -Authentication Basic -AllowRedirection
  3. Import-PSSession $Session -DisableNameChecking
  4. Search-UnifiedAuditLog -EndDate "01-08-2019" -StartDate "01-06-2019" -UserIds "tycho.brahe@observatory.dk" -ResultSize 5000
  5. $ConvertAudit = $Audit | Select-Object -ExpandProperty AuditData | ConvertFrom-Json
  6. $ConvertAudit | Select-Object CreationTime,UserId,Operation,Workload,ObjectID,SiteUrl,SourceFileName,ClientIP,UserAgent

Three main actions are taken:

  1. Connect to Exchange Online (line 1 – 3)
  2. Perform the audit log search (line 4)
  3. Get meaningful attributes from the result set (line 5 – 6)

You should take a note of three things:

  • The useful part of the audit data is embedded as JSON in the AuditData property of the log entry
  • No more than 5000 records are returned
  • Only three months of logs are kept so you can’t search beyond this

Fixing the 5000 Entry Limit

In most cases when you’re searching the logs for all user activity you will find yourself breaking the 5000 record limit. In order to fix this you need to break down your query into multiple queries that will stay within the 5000 record limit.

One way of solving this is to break your query up into one hour time frames. Searching 14 days of log files could be done in this way:

  1. Connect to Exchange Online to access the unified audit log
  2. $UserCredential = Get-Credential
  3. $Session = New-PSSession -ConfigurationName Microsoft.Exchange -ConnectionUri https://outlook.office365.com/powershell-liveid/ -Credential $UserCredential -Authentication Basic -AllowRedirection
  4. Import-PSSession $Session -DisableNameChecking
  5. $OutputFile = ".\UnifiedAuditLog_FULL.csv"
  6. $Today = Get-Date -Date (Get-Date -Format “yyyy-MM-dd”)
  7. $intDays = 14
  8. For ($i=0; $i -le $intDays; $i++){
  9. For ($j=23; $j -ge 0; $j--){
  10. $StartDate = ($Today.AddDays(-$i)).AddHours($j)
  11. $EndDate = ($Today.AddDays(-$i)).AddHours($j + 1)
  12. $Audit = Search-UnifiedAuditLog -StartDate $StartDate -EndDate $EndDate -ResultSize 5000
  13. $ConvertAudit = $Audit | Select-Object -ExpandProperty AuditData | ConvertFrom-Json
  14. $ConvertAudit | Select-Object CreationTime,UserId,Operation,Workload,ObjectID,SiteUrl,SourceFileName,ClientIP,UserAgent | Export-Csv $OutputFile -NoTypeInformation -Append
  15. Write-Host $StartDate `t $Audit.Count
  16. }
  17. }

This will get you a nice time sorted output that can be opened with Excel for further analysis.

You must keep an eye on the number of records generated each hour (output in line 15). If any of the record sets contain 5000 items you met the limit and need to reduce the time frames further, e.g. into 30 or 15 minutes instead of one hour.

The output of the above query has very useful information about what users accessed what information and from what IP address:

Office 365 Audit Log

Especially the IP address (ClientIP) is something that will hint if the access was legit or not.

Translating the IP addresses to geo location data will allow for further analysis. Read this post to understand how to do this using PowerShell.

Did you like this post? Maybe your friends will too!
Facebook
Twitter
LinkedIn