Forum Discussion
PowerShell script to export Audit log search Data based on userID filter
Office 365 Audit Log platform is helping you to monitor and control activities on your tenant.
In some case, it's necessary to export some user activity to detect some problematic usage.
FROM WEB ADMIN SITE
It's possible to do that export from the dedicated Admin site "Security & Compliance":
You can select certain filter options to focus on your question based on:
- Activity type
- Start & End DateTime
- UsersID (email or O365 login)
- File, folder, url or site
this filterset will be used:
- to execute the search and export the resultset (in CSV format)
- to create an alert
You can find a lot of details related to that Audit Log usage:
But some important restrictions need to be accepted before work with that solution:
- Number of lines (or Events) is limited to 50’000 max
- Number of characters exported to the AuditData details is restricted to the first 3’060 chars
FROM POWERSHELL
Based on many other articles and blogposts, I wrote a dedicated PowerShell script like a toolkit you can use to connect directly Office 365 Audit Log system and send the filters you need to get the result set in memory.
Based on that results, the script will directly extract the AuditData field and convert it into a CSV file with many fields (extracted form the JSON Format)
The PowerShell command used is:
Function Split-O365AuditLogs-FromO365 () { #Get the content to process Write-host " -----------------------------------------" -ForegroundColor Green [string]$username = "YourAdminAccount@yourtenant.onmicrosoft.com" [string]$PwdTXTPath = "C:\SECUREDPWD\ExportedPWD-$($username).txt" $secureStringPwd = ConvertTo-SecureString -string (Get-Content $PwdTXTPath) $UserCredential = New-Object System.Management.Automation.PSCredential $username, $secureStringPwd #This will prompt the user for credential # $UserCredential = Get-Credential $Session = New-PSSession -ConfigurationName Microsoft.Exchange -ConnectionUri https://outlook.office365.com/powershell-LiveID/ -Credential $UserCredential -Authentication Basic -AllowRedirection Import-PSSession $Session [DateTime]$startDate = "01/01/2019 00:00" #Format: mm/dd/yyyy hh:MM [DateTime]$endDate = "01/11/2019 23:59" #Format: mm/dd/yyyy hh:MM $SpecifiedUserIDs = "Youremailtoscan@yourtenant.com", "Youremailtoscan2@yourtenant.com" #syntax: "<value1>","<value2>",..."<valueX>". $scriptStart=(get-date) $sessionName = (get-date -Format 'u')+'o365auditlog' # Reset user audit accumulator $aggregateResults = @() $i = 0 # Loop counter Do { Write-host " >> Audit Request Details: StartDate=", $startDate, "- EndDate=", $endDate, "SpecifiedUserIDs=", $SpecifiedUserIDs $currentResults = Search-UnifiedAuditLog -StartDate $startDate -EndDate $enddate -SessionId $sessionName -SessionCommand ReturnLargeSet -ResultSize 1000 -UserIds $SpecifiedUserIDs if ($currentResults.Count -gt 0) { Write-Host (" Finished {3} search #{1}, {2} records: {0} min" -f [math]::Round((New-TimeSpan -Start $scriptStart).TotalMinutes,4), $i, $currentResults.Count, $user.UserPrincipalName ) # Accumulate the data $aggregateResults += $currentResults # No need to do another query if the # recs returned <1k - should save around 5-10 sec per user if ($currentResults.Count -lt 1000) { $currentResults = @() } else { $i++ } } } Until ($currentResults.Count -eq 0) # --- End of Session Search Loop --- # [int]$IntemIndex = 1 $data=@() foreach ($line in $aggregateResults) { Write-host " ItemIndex:", $IntemIndex, "- Creation Date:", $line.CreationDate, "- UserIds:", $line.UserIds, "- Operations:", $line.Operations Write-host " > AuditData:", $line.AuditData $datum = New-Object -TypeName PSObject $Converteddata = convertfrom-json $line.AuditData $datum | Add-Member -MemberType NoteProperty -Name Id -Value $Converteddata.Id $datum | Add-Member -MemberType NoteProperty -Name CreationTimeUTC -Value $Converteddata.CreationTime $datum | Add-Member -MemberType NoteProperty -Name CreationTime -Value $line.CreationDate $datum | Add-Member -MemberType NoteProperty -Name Operation -Value $Converteddata.Operation $datum | Add-Member -MemberType NoteProperty -Name OrganizationId -Value $Converteddata.OrganizationId $datum | Add-Member -MemberType NoteProperty -Name RecordType -Value $Converteddata.RecordType $datum | Add-Member -MemberType NoteProperty -Name ResultStatus -Value $Converteddata.ResultStatus $datum | Add-Member -MemberType NoteProperty -Name UserKey -Value $Converteddata.UserKey $datum | Add-Member -MemberType NoteProperty -Name UserType -Value $Converteddata.UserType $datum | Add-Member -MemberType NoteProperty -Name Version -Value $Converteddata.Version $datum | Add-Member -MemberType NoteProperty -Name Workload -Value $Converteddata.Workload $datum | Add-Member -MemberType NoteProperty -Name UserId -Value $Converteddata.UserId $datum | Add-Member -MemberType NoteProperty -Name ClientIPAddress -Value $Converteddata.ClientIPAddress $datum | Add-Member -MemberType NoteProperty -Name ClientInfoString -Value $Converteddata.ClientInfoString $datum | Add-Member -MemberType NoteProperty -Name ClientProcessName -Value $Converteddata.ClientProcessName $datum | Add-Member -MemberType NoteProperty -Name ClientVersion -Value $Converteddata.ClientVersion $datum | Add-Member -MemberType NoteProperty -Name ExternalAccess -Value $Converteddata.ExternalAccess $datum | Add-Member -MemberType NoteProperty -Name InternalLogonType -Value $Converteddata.InternalLogonType $datum | Add-Member -MemberType NoteProperty -Name LogonType -Value $Converteddata.LogonType $datum | Add-Member -MemberType NoteProperty -Name LogonUserSid -Value $Converteddata.LogonUserSid $datum | Add-Member -MemberType NoteProperty -Name MailboxGuid -Value $Converteddata.MailboxGuid $datum | Add-Member -MemberType NoteProperty -Name MailboxOwnerSid -Value $Converteddata.MailboxOwnerSid $datum | Add-Member -MemberType NoteProperty -Name MailboxOwnerUPN -Value $Converteddata.MailboxOwnerUPN $datum | Add-Member -MemberType NoteProperty -Name OrganizationName -Value $Converteddata.OrganizationName $datum | Add-Member -MemberType NoteProperty -Name OriginatingServer -Value $Converteddata.OriginatingServer $datum | Add-Member -MemberType NoteProperty -Name SessionId -Value $Converteddata.SessionId $data += $datum $IntemIndex += 1 } $datestring = (get-date).ToString("yyyyMMdd-hhmm") $fileName = ("C:\AuditLogs\CSVExport\" + $datestring + ".csv") Write-Host (" >>> writing to file {0}" -f $fileName) $data | Export-csv $fileName -NoTypeInformation Remove-PSSession $Session } Split-O365AuditLogs-FromO365
You can use that script as you want and adapt it with your own needs
Attention:
AuditData limitation stay into the PowerShell option, but it's a MS Support Bug known as detailed into that following posts, I hope that will be fixed soon:
- https://techcommunity.microsoft.com/t5/Office-365/Incomplete-data-from-Search-UnifiedAuditLog-cmdlet-for-AzureAD/td-p/240805
- https://office365itpros.com/2018/10/22/longer-retention-office365-auditdata/
Additional links:
- https://angryanalyticsblog.azurewebsites.net/index.php/2018/02/16/power-bi-audit-log-analytics-solution/
- https://docs.microsoft.com/en-us/office365/securitycompliance/detailed-properties-in-the-office-365-audit-log
- https://docs.microsoft.com/en-us/office365/securitycompliance/detailed-properties-in-the-office-365-audit-log
- https://docs.microsoft.com/en-us/office365/securitycompliance/search-the-audit-log
- https://www.youtube.com/watch?v=KUyE59E3EFY
- https://blogs.msdn.microsoft.com/tehnoonr/2018/01/26/retrieving-office-365-audit-data-using-powershell/
- https://office365itpros.com/2018/10/22/longer-retention-office365-auditdata/
- https://docs.microsoft.com/en-us/powershell/module/exchange/policy-and-compliance-audit/search-unifiedauditlog?view=exchange-ps
- https://www.sherweb.com/blog/activity-reports-audit-logs-office-365/
- http://alexbrassington.com/2016/03/03/splitting-office-365-audit-logs/
- https://www.powershellgallery.com/packages/O365_Unified_Auditlog_parser/1.1/Content/O365_Unified_Auditlog_parser.ps1
Fabrice Romelard
French version:
- FromelardSteel Contributor
After discussion with different IT people, I improve the script adding options in the request but also managing the issue from the truncate JSON field.
Now the CSV export will support this error from the JSON conversion and push the full AuditData field into a dedicated CSV field (AuditDataRaw).
You can now use it as you need to.
Function Split-O365AuditLogs-FromO365 () { #Get the content to process Write-host " -----------------------------------------" -ForegroundColor Green <# [string]$username = "AdminAccount@yourtenant.com" [string]$PwdTXTPath = "C:\SECUREDPWD\ExportedPWD-$($username).txt" $secureStringPwd = ConvertTo-SecureString -string (Get-Content $PwdTXTPath) $UserCredential = New-Object System.Management.Automation.PSCredential $username, $secureStringPwd #> #This will prompt the user for credential $UserCredential = Get-Credential $Session = New-PSSession -ConfigurationName Microsoft.Exchange -ConnectionUri https://outlook.office365.com/powershell-LiveID/ -Credential $UserCredential -Authentication Basic -AllowRedirection Import-PSSession $Session [bool]$specifyPeriod = $true # if false it will be the default period time specified $DefaultPeriodToCheck (last XX days) [int]$DefaultPeriodToCheck = -7 #last 7 days by default [DateTime]$startDate = "01/01/2019 00:00" #"01/01/2019 00:00" #Format: mm/dd/yyyy hh:MM #(get-date).AddDays(-1) [DateTime]$endDate = "01/31/2019 23:59" #"01/11/2019 23:59" #Format: mm/dd/yyyy hh:MM #(get-date) [bool]$specifyUserIDs = $false $SpecifiedUserIDs = "UserIdEmailAddress@yourtenant.com" #syntax: "<value1>","<value2>",..."<valueX>" [bool]$specifyRecordTypes = $false $RecordTypeValues = "SharePoint" #Only one field to put, biggest products: "OneDrive" "SharePoint" "Sway" "PowerBI" "MicrosoftTeams" "MicrosoftStream" # Possible values: <ExchangeAdmin | ExchangeItem | ExchangeItemGroup | SharePoint | SyntheticProbe | SharePointFileOperation | OneDrive | AzureActiveDirectory | AzureActiveDirectoryAccountLogon | DataCenterSecurityCmdlet | ComplianceDLPSharePoint | Sway | ComplianceDLPExchange | SharePointSharingOperation | AzureActiveDirectoryStsLogon | SkypeForBusinessPSTNUsage | SkypeForBusinessUsersBlocked | SecurityComplianceCenterEOPCmdlet | ExchangeAggregatedOperation | PowerBIAudit | CRM | Yammer | SkypeForBusinessCmdlets | Discovery | MicrosoftTeams | MicrosoftTeamsAddOns | MicrosoftTeamsSettingsOperation | ThreatIntelligence AeD, MicrosoftStream, ThreatFinder, Project, SharePointListOperation, DataGovernance, SecurityComplianceAlerts, ThreatIntelligenceUrl, SecurityComplianceInsights, WorkplaceAnalytics, PowerAppsApp, PowerAppsPlan, ThreatIntelligenceAtpContent, LabelExplorer, TeamsHealthcare, ExchangeItemAggregated, HygieneEvent> $scriptStart=(get-date) $sessionName = (get-date -Format 'u')+'o365auditlog' # Reset user audit accumulator $aggregateResults = @() $i = 0 # Loop counter Do { Write-host " -----------------------------------------" -ForegroundColor Yellow if($specifyPeriod -eq $true) { if($specifyUserIDs -eq $true) { if ($specifyRecordTypes -eq $true) { Write-host " >> Audit Request Details: StartDate=", $startDate, "- EndDate=", $endDate, "- SpecifiedUserIDs=", $SpecifiedUserIDs, "- RecordType=", $RecordTypeValues -ForegroundColor Magenta $currentResults = Search-UnifiedAuditLog -StartDate $startDate -EndDate $enddate -SessionId $sessionName -SessionCommand ReturnLargeSet -ResultSize 1000 -UserIds $SpecifiedUserIDs -RecordType $RecordTypeValues } else { Write-host " >> Audit Request Details: StartDate=", $startDate, "- EndDate=", $endDate, "- SpecifiedUserIDs=", $SpecifiedUserIDs -ForegroundColor Magenta $currentResults = Search-UnifiedAuditLog -StartDate $startDate -EndDate $enddate -SessionId $sessionName -SessionCommand ReturnLargeSet -ResultSize 1000 -UserIds $SpecifiedUserIDs } } else { if ($specifyRecordTypes -eq $true) { Write-host " >> Audit Request Details: StartDate=", $startDate, "- EndDate=", $endDate, "- RecordType=", $RecordTypeValues -ForegroundColor Magenta $currentResults = Search-UnifiedAuditLog -StartDate $startDate -EndDate $enddate -SessionId $sessionName -SessionCommand ReturnLargeSet -ResultSize 1000 -RecordType $RecordTypeValues } else { Write-host " >> Audit Request Details: StartDate=", $startDate, "- EndDate=", $endDate -ForegroundColor Magenta $currentResults = Search-UnifiedAuditLog -StartDate $startDate -EndDate $enddate -SessionId $sessionName -SessionCommand ReturnLargeSet -ResultSize 1000 } } } else { $enddate = get-date $startDate = $enddate.AddDays($DefaultPeriodToCheck) #default period is the last week if($specifyUserIDs -eq $true) { if ($specifyRecordTypes -eq $true) { Write-host " >> Audit Request Details: StartDate=", $startDate, "- EndDate=", $endDate, "- SpecifiedUserIDs=", $SpecifiedUserIDs, "- RecordType=", $RecordTypeValues -ForegroundColor Magenta $currentResults = Search-UnifiedAuditLog -StartDate $startDate -EndDate $enddate -SessionId $sessionName -SessionCommand ReturnLargeSet -ResultSize 1000 -UserIds $SpecifiedUserIDs -RecordType $RecordTypeValues } else { Write-host " >> Audit Request Details: StartDate=", $startDate, "- EndDate=", $endDate, "- SpecifiedUserIDs=", $SpecifiedUserIDs -ForegroundColor Magenta $currentResults = Search-UnifiedAuditLog -StartDate $startDate -EndDate $enddate -SessionId $sessionName -SessionCommand ReturnLargeSet -ResultSize 1000 -UserIds $SpecifiedUserIDs } } else { if ($specifyRecordTypes -eq $true) { Write-host " >> Audit Request Details: StartDate=", $startDate, "- EndDate=", $endDate, "- RecordType=", $RecordTypeValues -ForegroundColor Magenta $currentResults = Search-UnifiedAuditLog -StartDate $startDate -EndDate $enddate -SessionId $sessionName -SessionCommand ReturnLargeSet -ResultSize 1000 -RecordType $RecordTypeValues } else { Write-host " >> Audit Request Details: StartDate=", $startDate, "- EndDate=", $endDate -ForegroundColor Magenta $currentResults = Search-UnifiedAuditLog -StartDate $startDate -EndDate $enddate -SessionId $sessionName -SessionCommand ReturnLargeSet -ResultSize 1000 } } } if ($currentResults.Count -gt 0) { Write-Host (" Finished {3} search #{1}, {2} records: {0} min" -f [math]::Round((New-TimeSpan -Start $scriptStart).TotalMinutes,4), $i, $currentResults.Count, $user.UserPrincipalName ) # Accumulate the data $aggregateResults += $currentResults # No need to do another query if the # recs returned <1k - should save around 5-10 sec per user if ($currentResults.Count -lt 1000) { $currentResults = @() } else { $i++ } } } Until ($currentResults.Count -eq 0) # --- End of Session Search Loop --- # [int]$IntemIndex = 1 $data=@() foreach ($line in $aggregateResults) { Write-host " ItemIndex:", $IntemIndex, "- Creation Date:", $line.CreationDate, "- UserIds:", $line.UserIds, "- Operations:", $line.Operations #Write-host " > AuditData:", $line.AuditData $datum = New-Object -TypeName PSObject try { $Converteddata = convertfrom-json $line.AuditData $datum | Add-Member -MemberType NoteProperty -Name Id -Value $Converteddata.Id $datum | Add-Member -MemberType NoteProperty -Name CreationTimeUTC -Value $Converteddata.CreationTime $datum | Add-Member -MemberType NoteProperty -Name CreationTime -Value $line.CreationDate $datum | Add-Member -MemberType NoteProperty -Name Operation -Value $Converteddata.Operation $datum | Add-Member -MemberType NoteProperty -Name OrganizationId -Value $Converteddata.OrganizationId $datum | Add-Member -MemberType NoteProperty -Name RecordType -Value $Converteddata.RecordType $datum | Add-Member -MemberType NoteProperty -Name ResultStatus -Value $Converteddata.ResultStatus $datum | Add-Member -MemberType NoteProperty -Name UserKey -Value $Converteddata.UserKey $datum | Add-Member -MemberType NoteProperty -Name UserType -Value $Converteddata.UserType $datum | Add-Member -MemberType NoteProperty -Name Version -Value $Converteddata.Version $datum | Add-Member -MemberType NoteProperty -Name Workload -Value $Converteddata.Workload $datum | Add-Member -MemberType NoteProperty -Name UserId -Value $Converteddata.UserId $datum | Add-Member -MemberType NoteProperty -Name ClientIPAddress -Value $Converteddata.ClientIPAddress $datum | Add-Member -MemberType NoteProperty -Name ClientInfoString -Value $Converteddata.ClientInfoString $datum | Add-Member -MemberType NoteProperty -Name ClientProcessName -Value $Converteddata.ClientProcessName $datum | Add-Member -MemberType NoteProperty -Name ClientVersion -Value $Converteddata.ClientVersion $datum | Add-Member -MemberType NoteProperty -Name ExternalAccess -Value $Converteddata.ExternalAccess $datum | Add-Member -MemberType NoteProperty -Name InternalLogonType -Value $Converteddata.InternalLogonType $datum | Add-Member -MemberType NoteProperty -Name LogonType -Value $Converteddata.LogonType $datum | Add-Member -MemberType NoteProperty -Name LogonUserSid -Value $Converteddata.LogonUserSid $datum | Add-Member -MemberType NoteProperty -Name MailboxGuid -Value $Converteddata.MailboxGuid $datum | Add-Member -MemberType NoteProperty -Name MailboxOwnerSid -Value $Converteddata.MailboxOwnerSid $datum | Add-Member -MemberType NoteProperty -Name MailboxOwnerUPN -Value $Converteddata.MailboxOwnerUPN $datum | Add-Member -MemberType NoteProperty -Name OrganizationName -Value $Converteddata.OrganizationName $datum | Add-Member -MemberType NoteProperty -Name OriginatingServer -Value $Converteddata.OriginatingServer $datum | Add-Member -MemberType NoteProperty -Name SessionId -Value $Converteddata.SessionId $datum | Add-Member -MemberType NoteProperty -Name LogonError -Value $Converteddata.LogonError $datum | Add-Member -MemberType NoteProperty -Name Subject -Value $Converteddata.Subject $datum | Add-Member -MemberType NoteProperty -Name ObjectId -Value $Converteddata.ObjectId $datum | Add-Member -MemberType NoteProperty -Name SiteUrl -Value $Converteddata.SiteUrl $datum | Add-Member -MemberType NoteProperty -Name SourceRelativeUrl -Value $Converteddata.SourceRelativeUrl $datum | Add-Member -MemberType NoteProperty -Name AuditDataRaw -Value $line.AuditData } catch { Write-host " =====>>>> JSON FORMAT NOT CORRECT " -ForegroundColor Red Write-host " =====>>>> AuditData:", $line.AuditData -ForegroundColor Yellow [guid]$NewGuid = [guid]::newguid() $datum | Add-Member -MemberType NoteProperty -Name Id -Value $NewGuid $datum | Add-Member -MemberType NoteProperty -Name CreationTimeUTC -Value $line.CreationDate $datum | Add-Member -MemberType NoteProperty -Name CreationTime -Value $line.CreationDate $datum | Add-Member -MemberType NoteProperty -Name UserId -Value $line.UserIds $datum | Add-Member -MemberType NoteProperty -Name Operation -Value $line.Operations $datum | Add-Member -MemberType NoteProperty -Name AuditDataRaw -Value $line.AuditData } $data += $datum $IntemIndex += 1 } $datestring = (get-date).ToString("yyyyMMdd-hhmm") $fileName = ("C:\AuditLogs\CSVExport\" + $datestring + ".csv") Write-host " -----------------------------------------" -ForegroundColor Green Write-Host (" >>> writing to file {0}" -f $fileName) -ForegroundColor Green $data | Export-csv $fileName -NoTypeInformation Write-host " -----------------------------------------" -ForegroundColor Green Remove-PSSession $Session } cls Split-O365AuditLogs-FromO365
Enjoy your day.
Fabrice Romelard
- ShrikantDatkeCopper Contributor
Fromelard I am trying to achieve a smoother and faster operation to query Compliance Portal get the Number of Files Deleted from One Drive for Business.
I am expecting approx >80K records deletion count in one day. Challenge I have is I am not sure on the exact count for 1 day or even 1 hour I am using below query$Today = Get-Date -Date (Get-Date -Format “yyyy-MM-dd”) $newStartDate = ($Today.AddDays(-$i)).AddHours($j) $newEndDate = ($Today.AddDays(-$i)).AddHours($j + 1) $deletedfiles = 1 do { $deletedfiles = Search-UnifiedAuditLog -StartDate $newStartDate -EndDate $newEndDate -RecordType SharePointFileOperation -Operations FileDeleted -SessionId "$newStartDate" -SessionCommand ReturnLargeSet -Resultsize 5000 | Where-Object {$_.AuditData -like '*"Workload"' + ":" + '"OneDrive"*' } Write-Host "Delete count: $($deletedfiles.Count)" $dfiles += $deletedfiles } Until ($deletedfiles.Count -eq 0)
Is it something you can suggest to speed up the slow query?
- SystemEngineerSteel Contributor