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