PowerShell script to export Audit log search Data based on userID filter

[Fromelard]

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":

• https://protection.office.com/?rfr=AdminCenter#/unifiedauditlog

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:

• https://docs.microsoft.com/en-us/office365/securitycompliance/search-the-audit-log-in-security-and-c...

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:

• Search-UnifiedAuditLog - https://docs.microsoft.com/en-us/powershell/module/exchange/policy-and-comp...

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...
• 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-solut...
• https://docs.microsoft.com/en-us/office365/securitycompliance/detailed-properties-in-the-office-365-...
• https://docs.microsoft.com/en-us/office365/securitycompliance/detailed-properties-in-the-office-365-...
• 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-powershe...
• https://office365itpros.com/2018/10/22/longer-retention-office365-auditdata/
• https://docs.microsoft.com/en-us/powershell/module/exchange/policy-and-compliance-audit/search-unifi...
• 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_Aud...

Fabrice Romelard

French version:

• http://blogs.developpeur.org/fabrice69/archive/2019/01/28/office-365-script-powershell-pour-extraire...

[#2]

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

[#3]

@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? 

[#4]

Hi @ShrikantDatke,

Did you finally get it fixed or is it actually slow?