Share via

Retrieving Office 365 Audit Data using PowerShell

  • Article

Introduction

In today's world, security, compliance and auditing have become a top priority for I.T. admins. Office 365 has several built-in capabilities when it comes to auditing and compliance. If you have enabled auditing for your tenant, you can easily retrieve audit logs using the following methods:

If you need to retrieve audit logs on a regular basis, you should consider implementing a solution using the Office 365 Management Activity API as that is the method that can provide you scalability and performance that you will need if you need to pull millions of audit records on a regular basis. However, there may be certain situations where you need to manually retrieve audit data using PowerShell and Search-UnifiedAuditLog has been designed for exactly that purpose. This is what I am going to focus on for this post.

Using Search-UnifiedAuditLog - The Challenges

The documentation for Search-UnifitAuditLog provides a good overview of the various different parameters and their use. There are a number of challenges when using this command to retrieve a large number of audit records:

  • You cannot guarantee how many records the command will return in a single call even when you have specified the ResultSize. You should repeatedly call Search-UnifiedAuditLog in a loop using the same value for SessionId parameter until records stop getting returned.
  • The maximum number of records that you can retrieve for a given criteria and SessionId is 50,000. That too when you use the ReturnLargeSet option for the SessionCommand parameter. If you find that the value for ResultCount in the returned data is greater than 50,000, that means you're not going to be able to retrieve all records in the current session and you should narrow your search to ensure the ResultCount stays below 50,000.  Remember that you have to issue multiple calls to Search-UnifiedAuditLog in a loop using the same SessionId to retrieve all 50,000 records.
  • If you have a slow connection, and it's taking a long time to retrieve the data, your session may time out on the server and in that case, you will see that the value for ResultIndex is getting reset to 1 before you could retrieve all data that matched the criteria specified.
  • When you are using repeated calls to Search-UnifiedAuditLog to retrieve data, you may get a few calls that return 0 results due to a network glitch, and your script may be "fooled" into believing that all data has been retrieved.

As you can see from the above, it can be quite a challenge to retrieve a large audit data set using PowerShell. But wait, I have a solution!

The Solution - An Intelligent Script

I have written a script that attempts to mitigate the challenges mentioned above in an automated fashion. The basic premise of the script is this: Let's say you are trying to retrieve 24 hours worth of audit data, the script will break that into several smaller intervals (15 minutes by default, but configurable). The smaller intervals will ensure that you are not hitting the max limit of how many records can be retrieved in a single session. For each smaller interval, the script will use a new session Id. Smaller intervals will also mean there will be a lesser chance of the session timing out on the server side. There is also retry mechanism built into the script (3 by default and configurable) that should address the problem of intermittent connectivity failures. I am providing the script below. Don't forget to change the configurable values to match your requirements. A few other things to note regarding the script:

  • The script will log it's progress in a log file
  • The output will be saved in a CSV file
  • if you see the following message in the log file, that would indicate you need to adjust the configurable variables to ensure each interval is not returning more than the maximum limit:
    "WARNING: x total records match the search criteria. Some records may get missed. Consider reducing the time interval!"

Let me know if this proves useful for you!

[code lang="ps"]

$UserCredential = Get-Credential
$Session = New-PSSession -ConfigurationName Microsoft.Exchange -ConnectionUri https://outlook.office365.com/powershell-liveid/ -Credential $UserCredential -Authentication Basic -AllowRedirection
Import-PSSession $Session
#################################### Configuration Section ###################################################
$logFile = "C:\Users\username\Desktop\MyLog.txt"
$outputFile = "C:\Users\username\Desktop\AuditRecords.csv"
[DateTime]$start = "1/22/18 03:59"
[DateTime]$end = "1/23/18 03:59"
$record = "SharePointFileOperation"
$resultSize = 1000
$intervalMinutes = 15
$retryCount = 3
#################################### End Configuration Section ###################################################
[DateTime]$currentStart = $start
[DateTime]$currentEnd = $start
$currentTries = 0

Function Write-LogFile ([String]$Message)
{
$final = [DateTime]::Now.ToString() + ":" + $Message
$final | Out-File $logFile -Append
}

while ($true)
{
$currentEnd = $currentStart.AddMinutes($intervalMinutes)
if ($currentEnd -gt $end)
{
break
}
$currentTries = 0
$sessionID = [DateTime]::Now.ToString().Replace('/', '_')
Write-LogFile "INFO: Retrieving audit logs between $($currentStart) and $($currentEnd)"
$currentCount = 0
while ($true)
{
[Array]$results = Search-UnifiedAuditLog -StartDate $currentStart -EndDate $currentEnd -RecordType $record -SessionId $sessionID -SessionCommand ReturnNextPreviewPage -ResultSize $resultSize
if ($results -eq $null -or $results.Count -eq 0)
{
#Retry if needed. This may be due to a temporary network glitch
if ($currentTries -lt $retryCount)
{
$currentTries = $currentTries + 1
continue
}
else
{
Write-LogFile "WARNING: Empty data set returned between $($currentStart) and $($currentEnd). Retry count reached. Moving forward!"
break
}
}
$currentTotal = $results[0].ResultCount
if ($currentTotal -gt 5000)
{
Write-LogFile "WARNING: $($currentTotal) total records match the search criteria. Some records may get missed. Consider reducing the time interval!"
}
$currentCount = $currentCount + $results.Count
Write-LogFile "INFO: Retrieved $($currentCount) records out of the total $($currentTotal)"
$results | epcsv $outputFile -NoTypeInformation -Append
if ($currentTotal -eq $results[$results.Count - 1].ResultIndex)
{
$message = "INFO: Successfully retrieved $($currentTotal) records for the current time range. Moving on!"
Write-LogFile $message
break
}
}
$currentStart = $currentEnd
}
Remove-PSSession $Session

Comments

  • Anonymous
    January 30, 2018
    Hi Raza, Thanks for sharing the intelligent script. The below tool can help you to collect the audit data from Office 365 and save it in local storage. Also, the tool provides analytics and statistics on activities performed.https://gallery.technet.microsoft.com/office/Office-365-Auditing-Tool-01747cd4
    • Anonymous
      January 31, 2018
      Thanks Robert! The tool looks great!
  • Anonymous
    January 09, 2019
    Very helpful script, Tehnoon! This worked great for me for Power BI audit logs just by changing the file paths and the $report variable to "PowerBI". I'm also going to create a Power BI report to parse and display the resulting CSV.
  • Anonymous
    March 14, 2019
    Hi, Tehnoon. This script is great and solves an immediate need I had. One thing I would suggest is that, instead of using hard-coded variables for the "Search-UnifiedAuditLog" string, maybe wrap the entire script in a function and set things like $record, $resultSize, and $intervalMinutes as parameters. That way, you don't have to modify your code any time you want to use different values. Thanks again!
    • Anonymous
      March 14, 2019
      Thanks for the feedback Matthew - that's a very good point. I'll update the script when I get a chance.