3.7 Investigating with Audit Log Search

Investigating with Audit Log Search

Introduction

Subsection 3.6 mapped the audit log architecture and the Standard vs Premium capabilities. This subsection puts that knowledge to work: building effective audit searches, interpreting results, reconstructing user activity timelines, and running the specific queries that answer the post-compromise questions your CISO asks.

The audit log is the evidence source of last resort — when no other log contains the data you need, the audit log usually does. But it is also the most challenging log to search effectively because of its volume (millions of events per day in a mid-size organization), its latency (30 minutes to 24 hours), and its heterogeneous structure (events from different services have different field structures). This subsection teaches you to navigate these challenges.

Building effective audit searches in the Purview portal

The Purview portal audit search interface (purview.microsoft.com → Audit → Search) provides a form-based search with the following parameters:

Date range narrows the search to a specific time window. Always set the tightest possible date range. Searching the full 180-day retention window when you only need data from one day wastes time and returns overwhelming results. For post-compromise investigations, set the date range to the compromise window (from the first suspicious sign-in to the containment time) plus a buffer of 2-4 hours on each side to capture preparation and post-containment activity.

Activities filter to specific event types. This is the most powerful filter because it eliminates irrelevant events entirely. Rather than searching all activities and filtering the results, select only the activities relevant to your investigation question. For mailbox access investigation, select MailItemsAccessed. For file exfiltration, select FileDownloaded and FileCopied. For admin compromise, select “Added member to role” and “Added user.”

Users filter to specific accounts. Always filter to the specific user(s) under investigation. Searching without a user filter in a large organization returns millions of events that are impossible to process.

File, folder, or site filters to specific content locations. Use this when you know the data location — “was anything downloaded from the Finance Reports SharePoint site during the compromise window?”

Search name provides a label for the search that helps you organize multiple searches during a complex investigation. Name searches descriptively: “INC-2026-0321_morrison_mailbox_access” is findable later; “search1” is not.

The five essential post-compromise audit queries

Every compromised account investigation should include these five audit searches. They answer the questions that your incident report must address.

Query 1: What emails did the attacker read? (Requires Premium audit)

Activity: MailItemsAccessed. User: compromised account. Date range: compromise window.

In the results, filter for OperationType = “Bind” (actively opened) and look for IP addresses that do not match the user’s known locations. Each Bind event represents an email the attacker explicitly opened and read. The ItemCount field shows how many items were accessed in each Bind operation.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13

// MailItemsAccessed — compromised mailbox investigation
CloudAppEvents
| where Timestamp between (datetime(2026-03-18 09:00) .. datetime(2026-03-18 12:00))
| where AccountId =~ "j.morrison@northgateeng.com"
| where ActionType == "MailItemsAccessed"
| extend OpType = tostring(RawEventData.OperationProperties[0].Value)
| where OpType == "Bind"
| extend ClientIP = tostring(RawEventData.ClientIPAddress)
| extend ClientApp = tostring(RawEventData.ClientInfoString)
| summarize EmailsAccessed = count(),
    SessionCount = dcount(tostring(RawEventData.SessionId))
    by ClientIP, ClientApp, bin(Timestamp, 30m)
| order by Timestamp asc

Query 2: What inbox rules did the attacker create?

Activity: New-InboxRule, Set-InboxRule, Enable-InboxRule. User: compromised account. Date range: compromise window.

Inbox rule manipulation is the most common post-compromise action in BEC attacks. The attacker creates a rule that forwards incoming email to an external address (for ongoing intelligence collection), deletes emails from specific senders (to hide notifications about suspicious activity), or moves emails to obscure folders (to prevent the user from seeing them).

1
2
3
4
5
6
7
8
9

// Inbox rule changes — detect attacker persistence
CloudAppEvents
| where Timestamp between (datetime(2026-03-18 09:00) .. datetime(2026-03-18 12:00))
| where AccountId =~ "j.morrison@northgateeng.com"
| where ActionType in ("New-InboxRule", "Set-InboxRule", "Enable-InboxRule",
    "New-TransportRule", "Set-Mailbox")
| extend RuleDetails = tostring(RawEventData)
| project Timestamp, ActionType, IPAddress, RuleDetails
| order by Timestamp asc

Query 3: What files did the attacker download?

Activity: FileDownloaded, FileCopied, FileAccessed. User: compromised account. Date range: compromise window.

This query shows every file the attacker downloaded from SharePoint and OneDrive. Each event includes the file name, the site URL (which SharePoint site or OneDrive), the file size, and the IP address. Cross-reference file names against sensitivity labels to determine whether sensitive data was downloaded.

1
2
3
4
5
6
7
8

// File downloads from SharePoint/OneDrive during compromise
OfficeActivity
| where TimeGenerated between (datetime(2026-03-18 09:00) .. datetime(2026-03-18 12:00))
| where UserId =~ "j.morrison@northgateeng.com"
| where Operation in ("FileDownloaded", "FileCopied", "FileAccessed")
| where ClientIP has "203.0.113"  // Filter to attacker IP only
| project TimeGenerated, Operation, OfficeObjectId, ClientIP, Site_Url
| order by TimeGenerated asc

Query 4: What admin changes did the attacker make?

Activity: “Add member to role”, “Add user”, “Update user”, “Add application”, “Consent to application”. Date range: compromise window.

If the compromised account had admin privileges (or if the attacker escalated privileges), this query reveals configuration changes: new admin role assignments, new user accounts created, OAuth application registrations, and consent grants. Each of these is a persistence mechanism that survives password reset and session revocation.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10

// Admin actions during compromise — privilege escalation and persistence
CloudAppEvents
| where Timestamp between (datetime(2026-03-18 09:00) .. datetime(2026-03-18 12:00))
| where ActionType in ("Add member to role.", "Add user.", "Update user.",
    "Add application.", "Consent to application.",
    "Add delegated permission grant.", "Add app role assignment grant to user.")
| project Timestamp, ActionType, AccountDisplayName, IPAddress,
    Target = tostring(RawEventData.Target[0].ID),
    Detail = tostring(RawEventData)
| order by Timestamp asc

Query 5: What did the attacker search for? (Requires Premium audit)

Activity: SearchQueryInitiatedExchange, SearchQueryInitiatedSharePoint. User: compromised account. Date range: compromise window.

This Premium-only event records every search query the user (or attacker) executed in Exchange (mailbox search) and SharePoint (document search). The search terms reveal the attacker’s objectives: searching for “wire transfer,” “bank account,” “invoice,” or “password” reveals financial fraud intent. Searching for “merger,” “acquisition,” or specific company names reveals corporate espionage.

1
2
3
4
5
6
7
8

// Attacker search queries — reveals objectives
CloudAppEvents
| where Timestamp between (datetime(2026-03-18 09:00) .. datetime(2026-03-18 12:00))
| where AccountId =~ "j.morrison@northgateeng.com"
| where ActionType in ("SearchQueryInitiatedExchange", "SearchQueryInitiatedSharePoint")
| extend SearchQuery = tostring(RawEventData.SearchQuery)
| project Timestamp, ActionType, SearchQuery, IPAddress
| order by Timestamp asc

Reconstructing a complete user activity timeline from audit data

The five queries above answer specific questions. To build a complete investigation timeline, combine them into a single union query that shows everything the compromised account did, chronologically.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22

// Complete audit timeline for a compromised account
let targetUser = "j.morrison@northgateeng.com";
let startTime = datetime(2026-03-18 09:00);
let endTime = datetime(2026-03-18 12:00);
union
    (CloudAppEvents
        | where Timestamp between (startTime .. endTime)
        | where AccountId =~ targetUser
        | project TimeGenerated = Timestamp,
            Source = "CloudApp",
            Action = ActionType,
            Detail = strcat(Application, " | ", tostring(RawEventData.ClientIPAddress)),
            SourceIP = IPAddress),
    (OfficeActivity
        | where TimeGenerated between (startTime .. endTime)
        | where UserId =~ targetUser
        | project TimeGenerated,
            Source = "Office",
            Action = Operation,
            Detail = strcat(OfficeWorkload, " | ", OfficeObjectId),
            SourceIP = ClientIP)
| order by TimeGenerated asc

This produces a chronological timeline showing every action the compromised account took across all M365 services. Read it like a narrative: the attacker signed in (Auth), searched the mailbox for financial terms (SearchQuery), read 52 emails (MailItemsAccessed), created an inbox forwarding rule (New-InboxRule), downloaded financial documents from SharePoint (FileDownloaded), and sent a fraudulent wire transfer email (Send). The timeline is the story of the attack, told through audit evidence.

Audit investigation pitfalls and how to avoid them

Experienced analysts encounter these recurring problems when working with audit data. Knowing them in advance saves hours of debugging during an active investigation.

Pitfall 1: MailItemsAccessed volume throttling. When a mailbox sync operation accesses hundreds of emails simultaneously (Outlook starting up and syncing the inbox), Microsoft throttles MailItemsAccessed events to prevent audit log overload. Instead of logging each email individually, the system logs a single throttled event representing the batch. During investigation, a throttled event means “the user/attacker accessed multiple emails in this window, but the individual items are not listed.” The ItemCount field in the throttled event shows the approximate count. This is not a data gap — it is a volume management feature. If you need the specific emails accessed during a throttled window, cross-reference with the Mailbox Audit Log (a separate, more detailed log accessible via Exchange PowerShell: Search-MailboxAuditLog).

Pitfall 2: Duplicate events from multiple audit pipelines. The same action may appear in both CloudAppEvents (via the Defender XDR connector) and OfficeActivity (via the M365 connector in Sentinel). If you union both tables in a timeline query without deduplication, you count the same event twice. Either query one table only (CloudAppEvents is recommended as it has richer schema), or deduplicate by operation ID: | distinct TimeGenerated, ActionType, AccountId, IPAddress.

Pitfall 3: Time zone confusion. All audit log timestamps are in UTC. If your investigation report says “the attacker accessed the mailbox at 09:22” and the CISO interprets that as 09:22 local time (BST, which is UTC+1), the timeline is off by one hour. Always specify the time zone in your reports. Better: include both UTC and local time: “09:22 UTC (10:22 BST).”

Pitfall 4: “No results” does not mean “no activity.” Beyond the latency issue (subsection 3.6), there are other reasons for empty results: the audit log was not enabled for the user’s mailbox (check Get-Mailbox -Identity user | fl AuditEnabled), the specific activity type is not audited at the user’s license level (MailItemsAccessed requires E5), or the retention period has expired (Standard = 180 days, data older than that is gone). Always verify audit enablement before concluding that no activity occurred.

Worked example: SharePoint permission change investigation

Beyond mailbox and file investigations, audit data is critical for detecting unauthorized permission changes — a common persistence technique where attackers grant themselves (or a backdoor account) access to sensitive SharePoint sites.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11

// Detect SharePoint permission changes during compromise window
OfficeActivity
| where TimeGenerated between (datetime(2026-03-18 09:00) .. datetime(2026-03-18 12:00))
| where UserId =~ "j.morrison@northgateeng.com"
| where Operation in ("SharingSet", "SharingInvitationCreated",
    "AddedToGroup", "SiteCollectionAdminAdded",
    "FileSyncDownloadedFull", "AnonymousLinkCreated")
| project TimeGenerated, Operation, OfficeObjectId,
    TargetUser = tostring(parse_json(tostring(ModifiedProperties))[0].NewValue),
    Site_Url, ClientIP
| order by TimeGenerated asc

This pattern — permission changes as persistence — is one of the most overlooked post-compromise findings. Analysts who check only inbox rules and file downloads miss the SharePoint permissions that give the attacker ongoing access even after the account is secured. Include SharePoint permission changes in every compromised account audit.

Knowledge check