Documenting with PowerShell: Downloading and storing the Office 365 Audit logs (With search!)

As we’re continuing the documenting with PowerShell series I’d like to take a step away from our regular IT-Glue and Documentation scripts and look at something that is related to documentation but also the monitoring side of the house. We’re going to be checking out the Office 365 Unified Audit log.

The unified audit log is the log where all actions that you take in the O365 environment are logged too, which is a great solution for compliance, security, and finding those pesky hackers that are trying to break into our cool Office365 environment. There’s a couple of downsides to the Unified Audit log;

  • Audit logs are only retained for 30 days if you have a business subscription, or 90 days when you have an enterprise subscription. Sometimes you need to go back to what happened more than 3 months ago.
  • Searching the audit log online via the Security and Compliance center is slow and does not show all results.
  • When exporting the results via the webinterface a maximum of 5000 audit log records is exported, meaning you will have to create 10 exports if you have 50,000 items logged.
Introducing the CyberDrain.com Auditlog HTML generator.

These issues are the reason I’ve made the following script – I wanted a way to search the Audit Log easily and have all records included. My script will download all of the audit logs of the previous day and save them as a CSV file. It also creates a completely searchable HTML file for ease of use. I’ve set this up to automatically download all the audit files each day to a secure location. That way whenever I have to start digging into logs I can easily find what I’m looking for. I’ve been requested to put more screenshots of the finished results, so this is how the HTML file will look;

The script

The script uses the secure application model to connect to office365. You can find instructions for the secure application model in this blog.

##########################################
$ApplicationId         = 'xxxx-xxxx-xxx-xxxx-xxxx'
$ApplicationSecret     = 'TheSecretTheSecrey' | Convertto-SecureString -AsPlainText -Force
$TenantID              = 'YourTenantID'
$RefreshToken          = 'RefreshToken'
$ExchangeRefreshToken  = 'ExchangeRefreshToken'
$upn                   = 'UPN-Used-To-Generate-Tokens'
##########################################
$credential = New-Object System.Management.Automation.PSCredential($ApplicationId, $ApplicationSecret)
$aadGraphToken = New-PartnerAccessToken -ApplicationId $ApplicationId -Credential $credential -RefreshToken $refreshToken -Scopes 'https://graph.windows.net/.default' -ServicePrincipal -Tenant $tenantID 
$graphToken = New-PartnerAccessToken -ApplicationId $ApplicationId -Credential $credential -RefreshToken $refreshToken -Scopes 'https://graph.microsoft.com/.default' -ServicePrincipal -Tenant $tenantID 
Connect-MsolService -AdGraphAccessToken $aadGraphToken.AccessToken -MsGraphAccessToken $graphToken.AccessToken

$customers = Get-MsolPartnerContract -All
#Logged in. Moving on to creating folders and getting data.
$folderName = (Get-Date).tostring("dd-MM-yyyy")
$outputfolder = "C:\ScriptOutput"
new-item -Path $outputfolder -ItemType Directory -Name $folderName -Force
foreach ($customer in $customers) {
  $token = New-PartnerAccessToken -ApplicationId 'a0c73c16-a7e3-4564-9a95-2bdf47383716'-RefreshToken $ExchangeRefreshToken -Scopes 'https://outlook.office365.com/.default' -Tenant $customer.TenantId
  $tokenValue = ConvertTo-SecureString "Bearer $($token.AccessToken)" -AsPlainText -Force
  $credential = New-Object System.Management.Automation.PSCredential($upn, $tokenValue)
  $customerId = $customer.DefaultDomainName
  $session = New-PSSession -ConfigurationName Microsoft.Exchange -ConnectionUri "https://ps.outlook.com/powershell-liveid?DelegatedOrg=$($customerId)&BasicAuthToOAuthConversion=true" -Credential $credential -Authentication Basic -AllowRedirection
  Import-PSSession $session -allowclobber -DisableNameChecking

  $startDate = (Get-Date).AddDays(-1)
  $endDate = (Get-Date)
  $Logs = @()
  Write-Host "Retrieving logs for $($customer.name)" -ForegroundColor Blue
  do {
    $logs += Search-unifiedAuditLog -SessionCommand ReturnLargeSet -SessionId $customer.name -ResultSize 5000 -StartDate $startDate -EndDate $endDate
    Write-Host "Retrieved $($logs.count) logs" -ForegroundColor Yellow
  }while ($Logs.count % 5000 -eq 0 -and $logs.count -ne 0)
  Write-Host "Finished Retrieving logs" -ForegroundColor Green
  $ObjLogs = foreach ($Log in $Logs) {
    $log.auditdata | convertfrom-json
  }
  $PreContent = @"
<H1> $($Customer.Name) - Audit Log from $StartDate until $EndDate </H1><br>

<br> Please note that this log is not complete - It is a representation where fields have been selected that are most commonly filtered on. .<br>
To analyze the complete log for this day, please click here for the complete CSV file log: <a href="$($Customer.Name).csv"/>CSV Logbook</a>
<br/>
<br/>

<input type="text" id="myInput" onkeyup="myFunction()" placeholder="Search...">
"@ 
  $head = @"
<script>
function myFunction() {
    const filter = document.querySelector('#myInput').value.toUpperCase();
    const trs = document.querySelectorAll('table tr:not(.header)');
    trs.forEach(tr => tr.style.display = [...tr.children].find(td => td.innerHTML.toUpperCase().includes(filter)) ? '' : 'none');
  }</script>
<Title>Audit Log Report</Title>
<style>
body { background-color:#E5E4E2;
      font-family:Monospace;
      font-size:10pt; }
td, th { border:0px solid black; 
        border-collapse:collapse;
        white-space:pre; }
th { color:white;
    background-color:black; }
table, tr, td, th {
     padding: 2px; 
     margin: 0px;
     white-space:pre; }
tr:nth-child(odd) {background-color: lightgray}
table { width:95%;margin-left:5px; margin-bottom:20px; }
h2 {
font-family:Tahoma;
color:#6D7B8D;
}
.footer 
{ color:green; 
 margin-left:10px; 
 font-family:Tahoma;
 font-size:8pt;
 font-style:italic;
}
#myInput {
  background-image: url('https://www.w3schools.com/css/searchicon.png'); /* Add a search icon to input */
  background-position: 10px 12px; /* Position the search icon */
  background-repeat: no-repeat; /* Do not repeat the icon image */
  width: 50%; /* Full-width */
  font-size: 16px; /* Increase font-size */
  padding: 12px 20px 12px 40px; /* Add some padding */
  border: 1px solid #ddd; /* Add a grey border */
  margin-bottom: 12px; /* Add some space below the input */
}
</style>
"@
  #$ObjLogs
  $Logs | export-csv "$($outputfolder)\$($FolderName)\$($Customer.Name).csv" -NoTypeInformation
  $ObjLogs | Select-object CreationTime, UserID, Operation, ResultStatus, ClientIP, Workload, ClientInfoString, * -ErrorAction SilentlyContinue | Convertto-html -head $head -PreContent $PreContent | out-file "$($outputfolder)\$($FolderName)\$($customer.Name).html"
}

2 thoughts on “Documenting with PowerShell: Downloading and storing the Office 365 Audit logs (With search!)

  1. Pingback: Documenting and monitoring blogs updates - CyberDrain

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.