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

Microsoft 365 - PowerShell script - To search audit logs
Microsoft 365 - PowerShell script - To search audit logs

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 executeSearch-UnifiedAuditLog – to fetch the audit logs user should be part of group which has role assigned either “Audit Logs” OR “View-Only Audit Logs”
Microsoft 365 – Exchange Online – Verifying the permission to view the required permission to execute CMDLET – Search-UnifiedAuditLog
fig : Microsoft 365 – Exchange Online – Verifying the permission to view the required permission to execute CMDLET – Search-UnifiedAuditLog

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 moduleExchangeOnlineManagement

#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

ValueMember nameDescription
1ExchangeAdminEvents from the Exchange admin audit log.
2ExchangeItemEvents from an Exchange mailbox audit log for actions that are performed on a single item, such as creating or receiving an email message.
3ExchangeItemGroupEvents 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.
4SharePointSharePoint events.
6SharePointFileOperationSharePoint file operation events.
7OneDriveOneDrive for Business events.
8AzureActiveDirectoryAzure Active Directory events.
9AzureActiveDirectoryAccountLogonAzure Active Directory OrgId logon events (deprecated).
10DataCenterSecurityCmdletData Center security cmdlet events.
11ComplianceDLPSharePointData loss protection (DLP) events in SharePoint and OneDrive for Business.
13ComplianceDLPExchangeData loss protection (DLP) events in Exchange, when configured via Unified DLP Policy. DLP events based on Exchange Transport Rules are not supported.
14SharePointSharingOperationSharePoint sharing events.
15AzureActiveDirectoryStsLogonSecure Token Service (STS) logon events in Azure Active Directory.
16SkypeForBusinessPSTNUsagePublic Switched Telephone Network (PSTN) events from Skype for Business.
17SkypeForBusinessUsersBlockedBlocked user events from Skype for Business.
18SecurityComplianceCenterEOPCmdletAdmin actions from the Security & Compliance Center.
19ExchangeAggregatedOperationAggregated Exchange mailbox auditing events.
20PowerBIAuditPower BI events.
21CRMDynamics 365 events.
22YammerYammer events.
23SkypeForBusinessCmdletsSkype for Business events.
24DiscoveryEvents for eDiscovery activities performed by running content searches and managing eDiscovery cases in the Security & Compliance Center.
25MicrosoftTeamsEvents from Microsoft Teams.
28ThreatIntelligencePhishing and malware events from Exchange Online Protection and Microsoft Defender for Office 365.
29MailSubmissionSubmission events from Exchange Online Protection and Microsoft Defender for Office 365.
30MicrosoftFlowMicrosoft Power Automate (formerly called Microsoft Flow) events.
31AeDAdvanced eDiscovery events.
32MicrosoftStreamMicrosoft Stream events.
33ComplianceDLPSharePointClassificationEvents related to DLP classification in SharePoint.
34ThreatFinderCampaign-related events from Microsoft Defender for Office 365.
35ProjectMicrosoft Project events.
36SharePointListOperationSharePoint List events.
37SharePointCommentOperationSharePoint comment events.
38DataGovernanceEvents related to retention policies and retention labels in the Security & Compliance Center
39KaizalaKaizala events.
40SecurityComplianceAlertsSecurity and compliance alert signals.
41ThreatIntelligenceUrlSafe links time-of-block and block override events from Microsoft Defender for Office 365.
42SecurityComplianceInsightsEvents related to insights and reports in the Office 365 security and compliance center.
43MIPLabelEvents related to the detection in the Transport pipeline of email messages that have been tagged (manually or automatically) with sensitivity labels.
44WorkplaceAnalyticsWorkplace Analytics events.
45PowerAppsAppPower Apps events.
46PowerAppsPlanSubscription plan events for Power Apps.
47ThreatIntelligenceAtpContentPhishing and malware events for files in SharePoint, OneDrive for Business, and Microsoft Teams from Microsoft Defender for Office 365.
48LabelContentExplorerEvents related to data classification content explorer.
49TeamsHealthcareEvents related to the Patients application in Microsoft Teams for Healthcare.
50ExchangeItemAggregatedEvents related to the MailItemsAccessed mailbox auditing action.
51HygieneEventEvents related to outbound spam protection.
52DataInsightsRestApiAuditData Insights REST API events.
53InformationBarrierPolicyApplicationEvents related to the application of information barrier policies.
54SharePointListItemOperationSharePoint list item events.
55SharePointContentTypeOperationSharePoint list content type events.
56SharePointFieldOperationSharePoint list field events.
57MicrosoftTeamsAdminTeams admin events.
58HRSignalEvents related to HR data signals that support the Insider risk management solution.
59MicrosoftTeamsDeviceTeams device events.
60MicrosoftTeamsAnalyticsTeams analytics events.
61InformationWorkerProtectionEvents related to compromised user alerts.
62CampaignEmail campaign events from Microsoft Defender for Office 365.
63DLPEndpointEndpoint DLP events.
64AirInvestigationAutomated incident response (AIR) events.
65QuarantineQuarantine events.
66MicrosoftFormsMicrosoft Forms events.
67ApplicationAuditApplication audit events.
68ComplianceSupervisionExchangeEvents tracked by the Communication compliance offensive language model.
69CustomerKeyServiceEncryptionEvents related to the customer key encryption service.
70OfficeNativeEvents related to sensitivity labels applied to Office documents.
71MipAutoLabelSharePointItemAuto-labeling events in SharePoint.
72MipAutoLabelSharePointPolicyLocationAuto-labeling policy events in SharePoint.
73MicrosoftTeamsShiftsTeams Shifts events.
75MipAutoLabelExchangeItemAuto-labeling events in Exchange.
76CortanaBriefingBriefing email events.
78WDATPAlertsEvents related to alerts generated by Windows Defender for Endpoint.
82SensitivityLabelPolicyMatchEvents generated when the file labeled with a sensitivity label is opened or renamed.
83SensitivityLabelActionEvent generated when sensitivity labels are applied, updated, or removed from a file.
84SensitivityLabeledFileActionEvents generated when a file labeled with a sensitivity label is opened or renamed.
85AttackSimAttack simulator events.
86AirManualInvestigationEvents related to manual investigations in Automated investigation and response (AIR).
87SecurityComplianceRBACSecurity and compliance RBAC events.
88UserTrainingAttack simulator training events in Microsoft Defender for Office 365.
89AirAdminActionInvestigationEvents related to admin actions in Automated investigation and response (AIR).
90MSTICThreat intelligence events in Microsoft Defender for Office 365.
91PhysicalBadgingSignalEvents related to physical badging signals that support the Insider risk management solution.
93AipDiscoverAzure Information Protection (AIP) scanner events.
94AipSensitivityLabelActionAIP sensitivity label events.
95AipProtectionActionAIP protection events.
96AipFileDeletedAIP file deletion events.
97AipHeartBeatAIP heartbeat events.
98MCASAlertsEvents corresponding to alerts triggered by Microsoft Cloud App Security.
99OnPremisesFileShareScannerDlpEvents related to scanning for sensitive data on file shares.
100OnPremisesSharePointScannerDlpEvents related to scanning for sensitive data in SharePoint.
101ExchangeSearchEvents related to using Outlook on the web (OWA) to search for mailbox items.
102SharePointSearchEvents related to searching an organization’s SharePoint home site.
103PrivacyInsightsPrivacy insight events.
105MyAnalyticsSettingsMyAnalytics events.
106SecurityComplianceUserChangeEvents related to modifying or deleting a user.
107ComplianceDLPExchangeClassificationExchange DLP classification events.
109MipExactDataMatchExact Data Match (EDM) classification events.
113MS365DCustomDetectionEvents related to custom detection actions in Microsoft 365 Defender.
147CoreReportingSettingsReports settings events.
148ComplianceConnectorEvents 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

Microsoft 365 - PowerShell script - To search audit logs
fig : Microsoft 365 – PowerShell script – To search audit logs

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

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

Prasham Sabadra

LIFE IS VERY BEAUTIFUL :) ENJOY THE WHOLE JOURNEY :) Founder of Knowledge Junction and live-beautiful-life.com, Author, Learner, Passionate Techie, avid reader. Certified Professional Workshop Facilitator / Public Speaker. Scrum Foundation Professional certificated. Motivational, Behavioral , Technical speaker. Speaks in various events including SharePoint Saturdays, Boot camps, Collages / Schools, local chapter. Can reach me for Microsoft 365, Azure, DevOps, SharePoint, Teams, Power Platform, JavaScript.

You may also like...

This site uses Akismet to reduce spam. Learn how your comment data is processed.

%d bloggers like this: