Office 365 Forensics using PowerShell and Search-UnifiedAuditLog

Office 365 forensics using PowerShell

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:

Connect-ExchangeOnline
Search-UnifiedAuditLog -EndDate "01-08-2019" -StartDate "01-06-2019" -UserIds "tycho.brahe@observatory.dk" -ResultSize 5000
$ConvertAudit = $Audit | Select-Object -ExpandProperty AuditData | ConvertFrom-Json
$ConvertAudit | Select-Object CreationTime,UserId,Operation,Workload,ObjectID,SiteUrl,SourceFileName,ClientIP,UserAgent
Disconnect-ExchangeOnline

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:

Connect-ExchangeOnline
$OutputFile = ".\UnifiedAuditLog_FULL.csv"
$Today = Get-Date -Date (Get-Date -Format “yyyy-MM-dd”)
$intDays = 14
For ($i=0; $i -le $intDays; $i++){
  For ($j=23; $j -ge 0; $j--){
    $StartDate = ($Today.AddDays(-$i)).AddHours($j)
    $EndDate = ($Today.AddDays(-$i)).AddHours($j + 1)
    $Audit = Search-UnifiedAuditLog -StartDate $StartDate -EndDate $EndDate -ResultSize 5000
    $ConvertAudit = $Audit | Select-Object -ExpandProperty AuditData | ConvertFrom-Json
    $ConvertAudit | Select-Object CreationTime,UserId,Operation,Workload,ObjectID,SiteUrl,SourceFileName,ClientIP,UserAgent | Export-Csv $OutputFile -NoTypeInformation -Append
    Write-Host $StartDate `t $Audit.Count
  }
}
Disconnect-ExchangeOnline

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 12). 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.

Office 365 Forensics and Easy365Manager

Easy365Manager is a snap-in to Active Directory Users & Computers that allows you to perform Office 365 license and mailbox administration directly in AD Users & Computers.

As an example, watch how easy it is to create an Office 365 mailbox by assigning an Exchange Online license from AD user properties:

Create an Office 365 mailbox using Easy365Manager

With Easy365Manager you no longer need to use multiple web consoles and run complex PowerShell scripts. Everything is managed from AD Users & Computers.

Easy365Manager even lets you remove your last on-premises Exchange Server.

One of the many benefits with Easy365Manager is the ability for admins to use individual credentials.

Easy365Manager does not add an extra layer of proprietary security. This means that all security logging is performed exactly as if you were using the standard tools.

All AD actions are logged in the security log according to the SACLs configured on your AD.

All Office 365 actions are logged via the unified audit log in Office 365 as detailed in this article.

So, you can rest assured that your forensics are unaffected by Easy365Manager.

Download your fully functional 30-day trial now to make your hybrid Office 365 admin life much more efficient.

great product, I’m really happy we found it!

Charles-Eric Haché
Head of IT and Telecommunication
Sainte-Julie, Québec – Canada

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