Microsoft 365 – PowerShell script – To search unified audit logs and export to CSV file – Detailed article

Hi All,

Greetings for the day!!!

Today one more PowerShell script – PowerShell script to search audit logs

Take away from this article

• Prerequisites required for executing CMDLET – Search-UnifiedAuditLog
• How to know the permissions required to execute Exchange Online PowerShell CMDLET
• How to export / store audit log result into CSV file

Prerequisites

• Latest version of “ExchangeOnlineManagement” module is installed
• Permissions required to execute – Search-UnifiedAuditLog – to fetch the audit logs user should be part of group which has role assigned either “Audit Logs” OR “View-Only Audit Logs”

Details

• I am writing PowerShell script for searching unified audit logs using CMDLET – Search-UnifiedAuditLog
• Unified audit logs contain events from
  • Exchange Online
  • SharePoint Online
  • OneDrive for Business
  • Azure Active Directory
  • Microsoft Teams
  • Power BI and other M365 services
• Search-UnifiedAuditLog cmdlet is available in Exchange Online PowerShell
• Syntax of Search-UnifiedAuditLog

Search-UnifiedAuditLog
      -EndDate <ExDateTime>
      -StartDate <ExDateTime>
      [-Formatted]
      [-FreeText <String>]
      [-IPAddresses <String[]>]
      [-ObjectIds <String[]>]
      [-Operations <String[]>]
      [-RecordType <AuditRecordType>]
      [-ResultSize <Int32>]
      [-SessionCommand <UnifiedAuditSessionCommand>]
      [-SessionId <String>]
      [-SiteIds <String[]>]
      [-UserIds <String[]>]
      [<CommonParameters>]

Steps

• Following are the parameters used for fetching audit logs in this script, we can add / remove respective parameters as per our requirement
  • Start Date – Start date from when the audit logs need to fetch
  • End Date – Date till when we need to fetch the audit logs
  • Site URL –
    • URL of the site, path to file or folder
    • For this URL entries are fetched
    • We could specify multiple values separated by comma (,) like "Value1","Value2",..."ValueN"
    • If there are spaces in between values, we need to use “double quotes”
  • Activity
    • Record Types for which log entries are fetched

Param
(
    #start date from when we need logs
    [Parameter(Mandatory = $true)]
    [DateTime]$startDate,
    #end date till what date
    [Parameter(Mandatory = $true)]
    [DateTime]$endDate,
    [Parameter(Mandatory = $true)]
    [string] $siteUrl,
    #activity for which we need logs
    [Parameter(Mandatory = $false)]
    [string]$activity
)

• Import module – ExchangeOnlineManagement

#import exchange online management module
Import-Module ExchangeOnlineManagement

• Connect to ExchangeOnline

#connect to exchangeonline
Connect-ExchangeOnline

• Check if activity is specified or not and formulate the command

#PowerShell CMDLET for fetching search audit logs
if($activity -eq $null -or $activity -eq ""){

  $results = Search-UnifiedAuditLog -StartDate $startDate -EndDate $endDate -  
  SessionId $sessionID -SessionCommand ReturnLargeSet -ResultSize 5000 -ObjectIds 
  $siteUrl 
}
else{
        $results = Search-UnifiedAuditLog -StartDate $startDate -EndDate $endDate  
        -RecordType $activity -SessionId $sessionID -SessionCommand ReturnLargeSet 
        -ResultSize 5000 -ObjectIds $siteUrl
    }

• Convert data into JSON format and select the columns which we need to export into CSV file

$AuditData = $results | Select-Object -ExpandProperty AuditData | ConvertFrom-Json

$AuditData = $ConvertAudit | Select-Object CreationTime,UserId,Operation,Workload,ItemType,ListId,ListItemUniqueId,Site,WebId,ObjectID,ClientIP,UserAgent

• Export converted result into CSV file

  #adding result to CSV file
        $AuditData | export-csv -Path $outputFile -Append -NoTypeInformation

• Few points related to start and end dates :
  • In audit logs, entries are stored in Coordinated Universal Time (UTC)
  • If we specify date/time value without time zone, default is UTC
  • We could specify the date/time value in UTC: For example, "2018-06-03 14:30:00z"
  • If we dont include time stamp in date, default time stamp will be 12:00 AM (midnight) on specified date

Audit Log Record Type – Taken from MS site – https://docs.microsoft.com/en-us/office/office-365-management-api/office-365-management-activity-api-schema#auditlogrecordtype

Value	Member name	Description
1	ExchangeAdmin	Events from the Exchange admin audit log.
2	ExchangeItem	Events from an Exchange mailbox audit log for actions that are performed on a single item, such as creating or receiving an email message.
3	ExchangeItemGroup	Events from an Exchange mailbox audit log for actions that can be performed on multiple items, such as moving or deleted one or more email messages.
4	SharePoint	SharePoint events.
6	SharePointFileOperation	SharePoint file operation events.
7	OneDrive	OneDrive for Business events.
8	AzureActiveDirectory	Azure Active Directory events.
9	AzureActiveDirectoryAccountLogon	Azure Active Directory OrgId logon events (deprecated).
10	DataCenterSecurityCmdlet	Data Center security cmdlet events.
11	ComplianceDLPSharePoint	Data loss protection (DLP) events in SharePoint and OneDrive for Business.
13	ComplianceDLPExchange	Data loss protection (DLP) events in Exchange, when configured via Unified DLP Policy. DLP events based on Exchange Transport Rules are not supported.
14	SharePointSharingOperation	SharePoint sharing events.
15	AzureActiveDirectoryStsLogon	Secure Token Service (STS) logon events in Azure Active Directory.
16	SkypeForBusinessPSTNUsage	Public Switched Telephone Network (PSTN) events from Skype for Business.
17	SkypeForBusinessUsersBlocked	Blocked user events from Skype for Business.
18	SecurityComplianceCenterEOPCmdlet	Admin actions from the Security & Compliance Center.
19	ExchangeAggregatedOperation	Aggregated Exchange mailbox auditing events.
20	PowerBIAudit	Power BI events.
21	CRM	Dynamics 365 events.
22	Yammer	Yammer events.
23	SkypeForBusinessCmdlets	Skype for Business events.
24	Discovery	Events for eDiscovery activities performed by running content searches and managing eDiscovery cases in the Security & Compliance Center.
25	MicrosoftTeams	Events from Microsoft Teams.
28	ThreatIntelligence	Phishing and malware events from Exchange Online Protection and Microsoft Defender for Office 365.
29	MailSubmission	Submission events from Exchange Online Protection and Microsoft Defender for Office 365.
30	MicrosoftFlow	Microsoft Power Automate (formerly called Microsoft Flow) events.
31	AeD	Advanced eDiscovery events.
32	MicrosoftStream	Microsoft Stream events.
33	ComplianceDLPSharePointClassification	Events related to DLP classification in SharePoint.
34	ThreatFinder	Campaign-related events from Microsoft Defender for Office 365.
35	Project	Microsoft Project events.
36	SharePointListOperation	SharePoint List events.
37	SharePointCommentOperation	SharePoint comment events.
38	DataGovernance	Events related to retention policies and retention labels in the Security & Compliance Center
39	Kaizala	Kaizala events.
40	SecurityComplianceAlerts	Security and compliance alert signals.
41	ThreatIntelligenceUrl	Safe links time-of-block and block override events from Microsoft Defender for Office 365.
42	SecurityComplianceInsights	Events related to insights and reports in the Office 365 security and compliance center.
43	MIPLabel	Events related to the detection in the Transport pipeline of email messages that have been tagged (manually or automatically) with sensitivity labels.
44	WorkplaceAnalytics	Workplace Analytics events.
45	PowerAppsApp	Power Apps events.
46	PowerAppsPlan	Subscription plan events for Power Apps.
47	ThreatIntelligenceAtpContent	Phishing and malware events for files in SharePoint, OneDrive for Business, and Microsoft Teams from Microsoft Defender for Office 365.
48	LabelContentExplorer	Events related to data classification content explorer.
49	TeamsHealthcare	Events related to the Patients application in Microsoft Teams for Healthcare.
50	ExchangeItemAggregated	Events related to the MailItemsAccessed mailbox auditing action.
51	HygieneEvent	Events related to outbound spam protection.
52	DataInsightsRestApiAudit	Data Insights REST API events.
53	InformationBarrierPolicyApplication	Events related to the application of information barrier policies.
54	SharePointListItemOperation	SharePoint list item events.
55	SharePointContentTypeOperation	SharePoint list content type events.
56	SharePointFieldOperation	SharePoint list field events.
57	MicrosoftTeamsAdmin	Teams admin events.
58	HRSignal	Events related to HR data signals that support the Insider risk management solution.
59	MicrosoftTeamsDevice	Teams device events.
60	MicrosoftTeamsAnalytics	Teams analytics events.
61	InformationWorkerProtection	Events related to compromised user alerts.
62	Campaign	Email campaign events from Microsoft Defender for Office 365.
63	DLPEndpoint	Endpoint DLP events.
64	AirInvestigation	Automated incident response (AIR) events.
65	Quarantine	Quarantine events.
66	MicrosoftForms	Microsoft Forms events.
67	ApplicationAudit	Application audit events.
68	ComplianceSupervisionExchange	Events tracked by the Communication compliance offensive language model.
69	CustomerKeyServiceEncryption	Events related to the customer key encryption service.
70	OfficeNative	Events related to sensitivity labels applied to Office documents.
71	MipAutoLabelSharePointItem	Auto-labeling events in SharePoint.
72	MipAutoLabelSharePointPolicyLocation	Auto-labeling policy events in SharePoint.
73	MicrosoftTeamsShifts	Teams Shifts events.
75	MipAutoLabelExchangeItem	Auto-labeling events in Exchange.
76	CortanaBriefing	Briefing email events.
78	WDATPAlerts	Events related to alerts generated by Windows Defender for Endpoint.
82	SensitivityLabelPolicyMatch	Events generated when the file labeled with a sensitivity label is opened or renamed.
83	SensitivityLabelAction	Event generated when sensitivity labels are applied, updated, or removed from a file.
84	SensitivityLabeledFileAction	Events generated when a file labeled with a sensitivity label is opened or renamed.
85	AttackSim	Attack simulator events.
86	AirManualInvestigation	Events related to manual investigations in Automated investigation and response (AIR).
87	SecurityComplianceRBAC	Security and compliance RBAC events.
88	UserTraining	Attack simulator training events in Microsoft Defender for Office 365.
89	AirAdminActionInvestigation	Events related to admin actions in Automated investigation and response (AIR).
90	MSTIC	Threat intelligence events in Microsoft Defender for Office 365.
91	PhysicalBadgingSignal	Events related to physical badging signals that support the Insider risk management solution.
93	AipDiscover	Azure Information Protection (AIP) scanner events.
94	AipSensitivityLabelAction	AIP sensitivity label events.
95	AipProtectionAction	AIP protection events.
96	AipFileDeleted	AIP file deletion events.
97	AipHeartBeat	AIP heartbeat events.
98	MCASAlerts	Events corresponding to alerts triggered by Microsoft Cloud App Security.
99	OnPremisesFileShareScannerDlp	Events related to scanning for sensitive data on file shares.
100	OnPremisesSharePointScannerDlp	Events related to scanning for sensitive data in SharePoint.
101	ExchangeSearch	Events related to using Outlook on the web (OWA) to search for mailbox items.
102	SharePointSearch	Events related to searching an organization’s SharePoint home site.
103	PrivacyInsights	Privacy insight events.
105	MyAnalyticsSettings	MyAnalytics events.
106	SecurityComplianceUserChange	Events related to modifying or deleting a user.
107	ComplianceDLPExchangeClassification	Exchange DLP classification events.
109	MipExactDataMatch	Exact Data Match (EDM) classification events.
113	MS365DCustomDetection	Events related to custom detection actions in Microsoft 365 Defender.
147	CoreReportingSettings	Reports settings events.
148	ComplianceConnector	Events related to importing non-Microsoft data using data connectors in the Microsoft 365 compliance center.

Complete Script

<#
=============================================================================================
Name:           Fetching the Audit Logs using PowerShell report
Description:    This script searches in Audit logs based on required input
Version:        1.0
============================================================================================
#>


Param
(
    #start date from when we need logs
    [Parameter(Mandatory = $true)]
    [DateTime]$startDate,
    
    #end date till what date
    [Parameter(Mandatory = $true)]
    [DateTime]$endDate,
    
    #sharepoint site URL
    [Parameter(Mandatory = $true)]
    [string] $siteUrl,

    #activity for which we need logs - its not mandatory. If not specified all 
    #activities are considered
    [Parameter(Mandatory = $false)]
    [string]$activity
)

#import exchange online management module
Import-Module ExchangeOnlineManagement

#connect to exchangeonline
Connect-ExchangeOnline

#log file path
$logFile = "AuditLogSearchLog.txt"

#output file path
$outputFile = "AuditLogRecords.csv"

#Function to write into Log-File
Function Write-LogFile ([String]$Message)
{
    $final = [DateTime]::Now.ToUniversalTime().ToString("s") + ":" + $Message
    $final | Out-File $logFile -Append
}#Write-LogFile

Write-LogFile "BEGIN: Retrieving audit records between $($startDate) and $($endDate), RecordType=$($activity), PageSize=5000"

Write-Host "Retrieving audit records for the date range between $($startDate) and $($endDate), RecordType=$($activity), PageSize=5000."
    
$sessionID = [Guid]::NewGuid().ToString() + "_" +  "ExtractLogs" + (Get-Date).ToString("yyyyMMddHHmmssfff")

  
#PowerShell CMDLET for fetching search audit logs
if($activity -eq $null -or $activity -eq ""){

   $results = Search-UnifiedAuditLog -StartDate $startDate -EndDate $endDate - 
   SessionId $sessionID -SessionCommand ReturnLargeSet -ResultSize 5000 -ObjectIds 
   $siteUrl 
} #if
else{
   $results = Search-UnifiedAuditLog -StartDate $startDate -EndDate $endDate -
   RecordType $activity -SessionId $sessionID -SessionCommand ReturnLargeSet -
   ResultSize 5000 -ObjectIds $siteUrl
}#else

$AuditData = $results | Select-Object -ExpandProperty AuditData | ConvertFrom-Json
$AuditData = $ConvertAudit | Select-Object CreationTime,UserId,Operation,Workload,ItemType,ListId,ListItemUniqueId,Site,WebId,ObjectID,ClientIP,UserAgent


if (($results | Measure-Object).Count -ne 0){
        #adding result to CSV file
        $AuditData | export-csv -Path $outputFile -Append -NoTypeInformation
    } #if

Write-Host "Script complete! Finished retrieving audit records for the date range between $($startDate) and $($endDate)" -foregroundColor Green

Thanks for reading!!! Please feel free to discuss in case any questions / suggestions / thoughts !!!

HAVE A GREAT TIME AHEAD !!! LIFE IS BEAUTIFUL 🙂