Export, configure, and view audit log records | Microsoft Learn
<#PSScriptInfo
.VERSION 1.4-fixed
.GUID 2082a2b3-155c-42e5-b3f6-c67434a0a3e1
.AUTHOR [email protected] (fixed by PA)
.TAGS O365 UnifiedAuditlog Auditlog parser
.RELEASENOTES
Fixed encoding issues with special characters, smart quotes, colon-splitting for URLs, and uninitialized variable.
#>
<#
.DESCRIPTION
O365 auditlog (Unified log) parser.
Applies to logs downloaded from https://protection.office.com - Search & investigation - Audit log search - Download - .csv
Downloaded log has 4 colums:
CreationDate | UserIds | Operations | Auditdata
Problem: the most important one (Auditdata) is string mess where data is delimited with ; , and [] and you can't really import it to excel to filter reasonably for examing.
Also problem: different services log more or less data so no fixed amount of columns
This parser will modify the Auditdata column, creates a table and exports the parsered csv file (to be imported to excel).
More comments inside the script.
#>
Param(
[Parameter(Mandatory=$false)]
[string]$FilePath
)
# O365 auditlog (Unified log) parser
# Applies to logs downloaded from https://protection.office.com - Search & investigation - Audit log search - Download .csv
# Downloaded log has 4 colums
# CreationDate | UserIds | Operations | Auditdata
# Problem: the most important one (Auditdata) is "string mess" where data is delimited with ; , and [] and you can't really import it to excel to filter reasonably.
# Also problem: different services log more or less data so no fixed amount of columns
# Solution: delimiter to be used while importing to Excel seems to be "," BUT NOT INSIDE brackets [{ }] so we change all "," --> || as delimiter inside brackets []
# Then we can split and construct a table in a format where it is csv exportable
# Columns for the result table
# Bad thing is that columns has to be known beforehand.
# https://support.office.com/en-us/article/detailed-properties-in-the-office-365-audit-log-ce004100-9e7f-443e-942b-9b04098fcfc3
$columnheaders = `
"CreationDate",`
"UserIds",`
"Operations",`
"Actor",`
"ActorContextId",`
"ActorIpAddress",`
"AddOnName",`
"AddOnType",`
"AppAccessContext",`
"ApplicationDisplayName",`
"ApplicationId",`
"AuthenticationType",`
"AzureActiveDirectoryEventType",`
"BrowserName",`
"BrowserVersion",`
"ChannelGuid",`
"ChannelName",`
"Client",`
"ClientInfoString",`
"ClientIP",`
"ClientIPAddress",`
"CorrelationId",`
"CreationTime",`
"CustomUniqueId",`
"DestinationFileExtension",`
"DestinationFileName",`
"DestinationRelativeUrl",`
"DeviceDisplayName",`
"EventData",`
"EventSource",`
"ExternalAccess",`
"ExtendedProperties",`
"GeoLocation",`
"HighPriorityMediaProcessing",`
"ID",`
"InternalLogonType",`
"InterSystemsId",`
"IntraSystemId",`
"IsManagedDevice",`
"ItemType",`
"ListBaseType",`
"ListId",`
"ListItemUniqueId",`
"ListServerTemplate",`
"LoginStatus",`
"LogonError",`
"LogonType",`
"MailboxGuid",`
"MailboxOwnerUPN",`
"Members",`
"ModifiedProperties (Name, NewValue, OldValue)",`
"ObjectID",`
"Operation",`
"OrganizationID",`
"OrganizationName",`
"OriginatingServer",`
"Path",`
"Parameters",`
"Platform",`
"RecordType",`
"ResultStatus",`
"SecurityComplianceCenterEventType",`
"SharingType",`
"Site",`
"SiteUrl",`
"SourceFileExtension",`
"SourceFileName",`
"SourceRelativeUrl",`
"Subject",`
"TabType",`
"Target",`
"TargetContextId",`
"TargetUserOrGroupName",`
"TargetUserOrGroupType",`
"TeamGuid",`
"TeamName",`
"UniqueSharingId",`
"UserAgent",`
"UserDomain",`
"UserID",`
"UserKey",`
"UserSharedWith",`
"UserType",`
"Version",`
"WebId",`
"Workload",`
"Miscellaneous"
############# Main script
if (-not $FilePath) {
# No path provided - ask the user to type/paste it
Write-Host "No -FilePath parameter provided." -ForegroundColor Yellow
Write-Host "Tip: You can run this as: .\AuditlogParser_Fixed.ps1 -FilePath 'C:\path\to\file.csv'" -ForegroundColor Yellow
Write-Host ""
$FilePath = Read-Host "Enter the full path to the CSV audit log file"
}
if (-not (Test-Path $FilePath)) {
Write-Host "ERROR: File not found: $FilePath" -ForegroundColor Red
exit
}
$logsource = $FilePath
Write-Host "Using file: $logsource" -ForegroundColor Green
$logdirectory = Split-Path -Parent $logsource
$exported_log = $logsource -replace ".csv","_parsed.csv"
#Progressbar Start time
$PBStartTime = Get-Date
$log = Import-Csv -Path $logsource
# Use || as replacement char for commas inside brackets [{ }]
# This avoids encoding issues with special characters
$ErotinChar = "||"
# Check the log doesn't already contain our delimiter
$logAsString = $log | Out-String
if ($logAsString.Contains("||")) {
$ErotinChar = "^|^"
}
#total amount of rows in the log
$rivienmaara = $log.count
$rivilaskuri = 0
#create table to be populated, if exists then remove
$tabName = "O365LogTable"
if (Get-Variable -Name table -ErrorAction SilentlyContinue)
{
Remove-Variable table
}
$table = New-Object system.Data.DataTable "$tabName"
# Build table columns
$columnloop = 0
$cols = @()
$colheaders = @()
do
{
$cols += $columnheaders[$columnloop]
$colheaders += New-Object system.Data.DataColumn $cols[$columnloop],([string])
#Add the Columns
$table.columns.add($colheaders[$columnloop])
$columnloop++
}
until ($columnloop -gt ($columnheaders.count)-1)
# Begin to parse line by line
do
{
$string = $log[$rivilaskuri]
$parseredstring = $string
#trimming brackets
$parseredstring.Auditdata = $parseredstring.Auditdata.Trim("{","}")
$parseredstringaudit = $parseredstring.AuditData
# Initialise $parsered in case no regex matches are found
$parsered = $parseredstringaudit
#NonGreedy, fetch only [{ }] and all of them to regex-groups
$AllMatches = $parseredstringaudit | Select-String -Pattern "\[{.*?\}]" -AllMatches | foreach {$_.Matches}
$matchcount = $AllMatches.Count
if($AllMatches.Success)
{
#if only one hit, then don't iterate by items
if($matchcount -eq 1){
$old = $AllMatches.Value
$new = $old -replace ",",$ErotinChar
$parseredstringaudit = $parseredstringaudit.Replace($old,$new)
$parsered = $parseredstringaudit
}
else
{
$matchloop = 0
#loop line with regex-hits and replace comma (,) --> || ONLY inside [{ }]
do
{
$old = $AllMatches[$matchloop].Value
$new = $old -replace ",",$ErotinChar
$parseredstringaudit = $parseredstringaudit.Replace($old,$new)
$parsered = $parseredstringaudit
$matchloop++
}
until ($matchloop -gt $matchcount-1)
}
}
######## Done replacing between [{ }]
#after that we still have to look if there is ", " (<= ,<empty>) in the line
#NonGreedy, fetch only ", " and all of them to regex-groups
$AllMatches = $parseredstringaudit | Select-String -Pattern "\, " -AllMatches | foreach {$_.Matches}
$matchcount = $AllMatches.Count
if($AllMatches.Success)
{
#if only one hit, then don't iterate by items
if($matchcount -eq 1){
$old = $AllMatches.Value
$new = $old -replace ",",$ErotinChar
$parseredstringaudit = $parseredstringaudit.Replace($old,$new)
$parsered = $parseredstringaudit
}
else
{
$matchloop = 0
#loop line with regex-hits and replace comma (,) --> || ONLY inside ", "
do
{
$old = $AllMatches[$matchloop].Value
$new = $old -replace ",",$ErotinChar
$parseredstringaudit = $parseredstringaudit.Replace($old,$new)
$parsered = $parseredstringaudit
$matchloop++
}
until ($matchloop -gt $matchcount-1)
}
}
######## Done ", " -replacements
# and now we are able to split
$parseredsplitted = $parsered.Split(",")
$parseredsplitcount = $parseredsplitted.Count
$splitloop = 0
#Create a row to table
$row = $table.NewRow()
#loop and get headers and values
do
{
# FIX: Use IndexOf to find FIRST colon, but for values containing URLs (https:// etc.)
# we need to split on the first colon only, so the right part keeps the rest intact.
$currentField = $parseredsplitted[$splitloop]
$pos = $currentField.IndexOf(":")
if ($pos -lt 0) {
# No colon found, skip this field
$splitloop++
continue
}
$leftPart = $currentField.Substring(0, $pos)
# FIX: Use Substring(pos+1) to get EVERYTHING after the first colon
# This preserves URLs like https://... and timestamps with colons
$rightpart = $currentField.Substring($pos + 1)
#trim quotes
$leftPart = $leftPart.Trim('"')
$rightpart = $rightpart.Trim('"')
##### populate the values based on headers
##### best part of this that you can populate table by pointing to header names.
##### we don't have to worry about missing column values in the log row
#find matching header "number"
$colnumber = $table.Columns.IndexOf($leftPart)
#If there is no match then use Miscellaneous-column
if ($colnumber -eq "-1")
{
$colnumber = $table.Columns.IndexOf("Miscellaneous")
Write-Host "No column match for '$leftPart', using Miscellaneous, line: $rivilaskuri"
}
#Enter data in the row
$row.($cols[$colnumber]) = $rightpart
$splitloop++
}
until ($splitloop -gt $parseredsplitcount-1)
#Let's add three first columns to row from the original log
$row.($cols[0]) = $log[$rivilaskuri].CreationDate
$row.($cols[1]) = $log[$rivilaskuri].UserIds
$row.($cols[2]) = $log[$rivilaskuri].Operations
#Add the populated row to the table
$table.Rows.Add($row)
#skip to next line in log
$rivilaskuri++
#Show some progress info to user
## -- Calculate The Percentage Completed
[Int]$Percentage = ($rivilaskuri/$rivienmaara)*100
#calculate seconds
$SecondsElapsed = ((Get-Date) - $PBStartTime).TotalSeconds
$SecondsRemaining = ($SecondsElapsed / ($rivilaskuri/$rivienmaara)) - $SecondsElapsed
#transform to more readable format
$kulsek = [timespan]::fromseconds($SecondsElapsed)
$sekunnitkulunut = $kulsek.ToString("hh\:mm\:ss")
$jalsek = [timespan]::fromseconds($SecondsRemaining)
$sekunnitjaljella = $jalsek.ToString("hh\:mm\:ss")
Write-Progress -Activity "Processing" -Status "Processing: $rivilaskuri / $rivienmaara Elapsed Time: $sekunnitkulunut, Estimated time left: $sekunnitjaljella" -PercentComplete $Percentage
}
until ($rivilaskuri -gt $rivienmaara-1)
#close the bar
Write-Progress -Activity "Processing" -Status "Ready" -Completed
#Display the table
#$table | Format-Table
$table | Export-Csv -Path $exported_log -NoTypeInformation
Write-Host "`nOriginal log: $logsource" -ForegroundColor Yellow
Write-Host "Parsed log: $exported_log`n" -ForegroundColor Yellow
# Ask user if wants to open web-page where O365 detailed log is
Write-host "Would you like to open in browser detailed O365-log properties webpage (Microsoft's)?" -ForegroundColor Yellow
$Readhost = Read-Host " ( y / n ) "
Switch ($ReadHost)
{
Y {Write-host "Yes, opening"; Start-Process "https://support.office.com/en-us/article/detailed-properties-in-the-office-365-audit-log-ce004100-9e7f-443e-942b-9b04098fcfc3"}
N {Write-Host "No"; $PublishSettings=$false}
Default {Write-Host "Default, opening"; Start-Process "https://support.office.com/en-us/article/detailed-properties-in-the-office-365-audit-log-ce004100-9e7f-443e-942b-9b04098fcfc3"}
}
# Ask user if wants to open Folder of Parsed-csv
Write-host "Would you like to open Folder of End-Product ie. Parsed-csv?" -ForegroundColor Yellow
$Readhost = Read-Host " ( y / n ) "
Switch ($ReadHost)
{
Y {Write-host "Yes, opening"; Invoke-Item $logdirectory}
N {Write-Host "No"; $PublishSettings=$false}
Default {Write-Host "Default, opening"; Invoke-Item $logdirectory}
}
Loading...