Agent Foskett Academy • Lesson 129 • Advanced KQL

Advanced KQL: Building Enterprise Hunting Queries

The first query found a suspicious sign-in.

The second query found PowerShell. The third query found an email. The fourth query found a cloud application event.

Each query was useful, but none of them told the whole story.

Agent Foskett knew enterprise hunting was different. In a large environment, the best investigations do not stop at one table. They connect identity, endpoint, email and cloud evidence into one scalable hunting workflow.

Start Lesson 129 Back to Academy

Agent Foskett Academy lesson building enterprise KQL hunting queries for Microsoft Defender XDR

Lesson overview

Learn how to build enterprise-scale hunting queries that correlate Microsoft Defender XDR telemetry across identity, endpoint, email, cloud applications and alert evidence.

Combine multiple Defender XDR tables

Normalise evidence into investigation timelines

Use joins, unions, parsing and summarisation together

Design scalable hunting workflows for large tenants

Why enterprise hunting queries matter

Enterprise hunting is not just a bigger version of a small query. It requires careful scoping, consistent evidence fields, efficient joins and a clear investigation objective before the query ever runs.

One table rarely tells the whole storyA phishing email, suspicious sign-in, endpoint process and cloud action may all be part of the same incident.

Scale changes query designQueries that work against one user can become slow or noisy across thousands of users, devices and events.

Enterprise hunts need repeatabilityA good hunting query should be clear enough to run again, tune later and share with other analysts.

The enterprise hunting mindset

Before building a large query, define the question you are trying to answer. Enterprise hunting starts with intent, then uses KQL to prove or disprove that hypothesis.

Start with a hypothesisFor example: did a suspicious email lead to identity compromise and endpoint execution?

Choose anchor evidenceUse the strongest starting point, such as a user, device, NetworkMessageId, IP address, URL, file hash or alert ID.

Normalise fields earlyConvert different table schemas into shared fields such as TimeGenerated, Account, Device, Source and Evidence.

Filter before expandingUse the Lesson 124 performance mindset. Reduce data before using joins, mv-expand or expensive parsing.

Build in layersCreate small verified evidence sets first, then join or union them into a broader investigation.

Return investigation-ready outputThe final query should help an analyst make a decision, not just produce thousands of raw rows.

Step 1 — Define the investigation scope

Start by creating reusable scope variables. Time range, target user and target device should be easy to change without rewriting the query.

let Lookback = 14d;
let TargetUser = "user@contoso.com";
let TargetDevice = "device01.contoso.com";
let StartTime = ago(Lookback);
print StartTime = StartTime, EndTime = now(), TargetUser = TargetUser, TargetDevice = TargetDevice

Step 2 — Build an identity evidence set

Identity evidence often provides the first clue in a compromise investigation. Keep the output consistent so it can be combined with other evidence later.

let Lookback = 14d;
let TargetUser = "user@contoso.com";
let IdentityEvidence =
    IdentityLogonEvents
    | where Timestamp > ago(Lookback)
    | where AccountUpn =~ TargetUser
    | project Timestamp,
              Source = "IdentityLogonEvents",
              Account = AccountUpn,
              DeviceName,
              EvidenceType = ActionType,
              EvidenceDetail = strcat(IPAddress, " ", Location, " ", FailureReason);
IdentityEvidence
| order by Timestamp asc

Step 3 — Add endpoint process evidence

Endpoint telemetry can show what happened after identity compromise. The same Account, DeviceName and EvidenceDetail fields make later union operations easier. Match the device to the investigation before interpreting its activity as related to the user.

let Lookback = 14d;
let TargetDevice = "device01.contoso.com";
let EndpointEvidence =
    DeviceProcessEvents
    | where Timestamp > ago(Lookback)
    | where DeviceName =~ TargetDevice
    | where FileName in~ ("powershell.exe", "cmd.exe", "rundll32.exe", "mshta.exe")
       or ProcessCommandLine has_any ("-enc", "downloadstring", "iex", "http", "bypass")
    | project Timestamp,
              Source = "DeviceProcessEvents",
              Account = InitiatingProcessAccountName,
              DeviceName,
              EvidenceType = FileName,
              EvidenceDetail = ProcessCommandLine;
EndpointEvidence
| order by Timestamp asc

Step 4 — Add email evidence

Email evidence gives the investigation its delivery context. NetworkMessageId is often the best pivot for connecting messages, URLs and attachments.

let Lookback = 14d;
let TargetUser = "user@contoso.com";
let EmailEvidence =
    EmailEvents
    | where Timestamp > ago(Lookback)
    | where RecipientEmailAddress =~ TargetUser
    | where ThreatTypes has_any ("Phish", "Malware", "Spam")
    | project Timestamp,
              Source = "EmailEvents",
              Account = RecipientEmailAddress,
              DeviceName = "",
              EvidenceType = DeliveryAction,
              EvidenceDetail = strcat(SenderFromAddress, " | ", Subject, " | ", NetworkMessageId);
EmailEvidence
| order by Timestamp asc

Step 5 — Correlate email clicks

UrlClickEvents can show whether the user interacted with a message after delivery. This is where email evidence becomes behaviour evidence.

let Lookback = 14d;
let TargetUser = "user@contoso.com";
EmailEvents
| where Timestamp > ago(Lookback)
| where RecipientEmailAddress =~ TargetUser
| project NetworkMessageId, EmailTime = Timestamp, SenderFromAddress, Subject
| join kind=leftouter (
    UrlClickEvents
    | where Timestamp > ago(Lookback)
    | where AccountUpn =~ TargetUser
    | project NetworkMessageId, ClickTime = Timestamp, Url, ActionType, IsClickedThrough
) on NetworkMessageId
| project EmailTime, ClickTime, AccountUpn = TargetUser, SenderFromAddress, Subject, Url, ActionType, IsClickedThrough
| order by EmailTime asc

Step 6 — Add cloud application activity

CloudAppEvents can reveal activity associated with a cloud account, including downloads, file access and application behaviour. Confirm how the account is represented in your tenant before filtering.

let Lookback = 14d;
let TargetUser = "user@contoso.com";
let CloudEvidence =
    CloudAppEvents
    | where Timestamp > ago(Lookback)
    | where AccountId =~ TargetUser or AccountDisplayName =~ TargetUser
    | project Timestamp,
              Source = "CloudAppEvents",
              Account = AccountDisplayName,
              DeviceName = tostring(RawEventData.DeviceName),
              EvidenceType = ActionType,
              EvidenceDetail = strcat(Application, " | ", ObjectName, " | ", IPAddress);
CloudEvidence
| order by Timestamp asc

Step 7 — Build a combined enterprise timeline

Union lets the analyst view identity, email, endpoint and cloud evidence in one timeline. This example scopes endpoint events by device; confirm the relationship between the device and user before treating the events as one incident.

let Lookback = 14d;
let TargetUser = "user@contoso.com";
let IdentityEvidence = IdentityLogonEvents
    | where Timestamp > ago(Lookback)
    | where AccountUpn =~ TargetUser
    | project Timestamp, Source="IdentityLogonEvents", Account=AccountUpn, DeviceName, EvidenceType=ActionType, EvidenceDetail=strcat(IPAddress, " ", Location);
let EmailEvidence = EmailEvents
    | where Timestamp > ago(Lookback)
    | where RecipientEmailAddress =~ TargetUser
    | project Timestamp, Source="EmailEvents", Account=RecipientEmailAddress, DeviceName="", EvidenceType=DeliveryAction, EvidenceDetail=strcat(SenderFromAddress, " | ", Subject);
let TargetDevice = "device01.contoso.com";
let EndpointEvidence = DeviceProcessEvents
    | where Timestamp > ago(Lookback)
    | where DeviceName =~ TargetDevice
    | where FileName in~ ("powershell.exe", "cmd.exe", "rundll32.exe", "mshta.exe")
    | project Timestamp, Source="DeviceProcessEvents", Account=InitiatingProcessAccountName, DeviceName, EvidenceType=FileName, EvidenceDetail=ProcessCommandLine;
let CloudEvidence = CloudAppEvents
    | where Timestamp > ago(Lookback)
    | where AccountId =~ TargetUser or AccountDisplayName =~ TargetUser
    | project Timestamp, Source="CloudAppEvents", Account=AccountId, DeviceName="", EvidenceType=ActionType, EvidenceDetail=strcat(Application, " | ", ObjectName);
union IdentityEvidence, EmailEvidence, EndpointEvidence, CloudEvidence
| order by Timestamp asc

Step 8 — Join alerts to evidence

AlertInfo and AlertEvidence can add Defender XDR context to hunting output, helping analysts connect raw events to detections and incidents.

let Lookback = 14d;
AlertInfo
| where Timestamp > ago(Lookback)
| project AlertId, AlertTime = Timestamp, Title, Severity, Category
| join kind=leftouter (
    AlertEvidence
    | where Timestamp > ago(Lookback)
    | project AlertId, EntityType, EvidenceRole, DeviceName, AccountName, FileName, RemoteUrl
) on AlertId
| project AlertTime, Title, Severity, Category, EntityType, EvidenceRole, DeviceName, AccountName, FileName, RemoteUrl
| order by AlertTime desc

Step 9 — Summarise enterprise exposure

Enterprise hunts should often end with prioritisation. Summaries help analysts see affected users, devices and first/last activity quickly.

let Lookback = 30d;
DeviceProcessEvents
| where Timestamp > ago(Lookback)
| where FileName in~ ("powershell.exe", "cmd.exe", "rundll32.exe", "mshta.exe")
| where ProcessCommandLine has_any ("-enc", "downloadstring", "iex", "http", "bypass", "hidden")
| summarize Events = count(),
            Devices = dcount(DeviceName),
            Users = dcount(InitiatingProcessAccountName),
            FirstSeen = min(Timestamp),
            LastSeen = max(Timestamp),
            ExampleCommand = any(ProcessCommandLine)
      by FileName
| order by Events desc

Step 10 — Build a reusable enterprise hunting query

The final pattern combines scoping, normalisation, correlation and summary output into a query that can be reused and tuned over time.

let Lookback = 14d;
// Example-only heuristic: geography and MFA failures need tenant-specific baselines.
let SuspiciousUsers =
    IdentityLogonEvents
    | where Timestamp > ago(Lookback)
    | where Location !in ("Australia", "New Zealand") or FailureReason has "MFA"
    | summarize IdentityEvents=count(), FirstIdentity=min(Timestamp), LastIdentity=max(Timestamp) by AccountUpn;
let SuspiciousProcesses =
    DeviceProcessEvents
    | where Timestamp > ago(Lookback)
    | where ProcessCommandLine has_any ("-enc", "downloadstring", "iex", "bypass")
    | summarize ProcessEvents=count(), Devices=dcount(DeviceName), ExampleCommand=any(ProcessCommandLine) by Account = InitiatingProcessAccountName;
SuspiciousUsers
| join kind=leftouter SuspiciousProcesses on $left.AccountUpn == $right.Account
| extend TotalSignal = IdentityEvents + coalesce(ProcessEvents, 0)
| project AccountUpn, TotalSignal, IdentityEvents, ProcessEvents, Devices, FirstIdentity, LastIdentity, ExampleCommand
| order by TotalSignal desc

Enterprise hunting design checklist

Use this checklist before promoting a large query into a hunting pack, scheduled hunt or investigation workbook.

Is the objective clear?The query should answer a specific hunting question rather than collecting everything just in case.

Have you filtered early?Time, user, device and table filters should happen before joins, parsing and expansion.

Are fields normalised?Use consistent names such as Timestamp, Source, Account, DeviceName, EvidenceType and EvidenceDetail.

Are joins controlled?Avoid joining large unfiltered datasets. Build small evidence sets first, then correlate.

Can another analyst read it?Readable KQL is more useful than clever KQL when the investigation is urgent.

Does the output support action?The final result should help identify affected users, devices, alerts, URLs, files or next steps.

Common enterprise hunting patterns

Enterprise hunts usually combine several proven KQL patterns rather than relying on one operator.

Identity to endpointCorrelate suspicious sign-ins with device activity, PowerShell, LOLBins and file changes.

Email to click to deviceFollow a message from EmailEvents into UrlClickEvents and then into endpoint or cloud activity.

Alert to evidenceUse AlertInfo and AlertEvidence to enrich hunting output with detection context.

Cloud action to userPivot from CloudAppEvents into identity activity to understand whether a session or token was abused.

Time series to timelineUse trends and anomaly findings as anchors, then build a detailed timeline around the suspicious window.

Function library to hunt packWrap trusted components into reusable functions so enterprise hunts remain consistent.

Related Agent Foskett Academy lessons

These lessons provide the building blocks needed for enterprise-grade Microsoft Defender XDR hunting.

Lesson 121 — Advanced KQL: Mastering Join OperationsUse joins to connect users, devices, emails, URLs, alerts and cloud activity.

Lesson 122 — Advanced KQL: Working with Dynamic DataUnderstand dynamic objects before normalising complex Defender XDR fields.

Lesson 123 — Advanced KQL: Mastering mv-expandExpand arrays carefully when working with entities, evidence and multi-value telemetry.

Lesson 124 — Advanced KQL: Optimising Query PerformanceEnterprise hunts must be fast, filtered and scalable across large environments.

Lesson 125 — Advanced KQL: Building Reusable KQL FunctionsTurn repeated hunting components into reusable functions and query libraries.

Lesson 128 — Advanced KQL: Advanced Time Series AnalysisUse time-based analytics to find the suspicious windows that enterprise hunts should investigate.

Coming next

The Advanced KQL series concludes by turning enterprise hunting logic into a complete investigation workbook.

Lesson 130 — Advanced KQL: Building a Complete Hunting WorkbookNext, Agent Foskett brings together parameters, visualisations, KQL queries and investigation sections into a complete Defender XDR hunting workbook.

Why this mattersA strong enterprise hunting query becomes even more powerful when analysts can run it through a reusable workbook with guided sections and visual output.

Final thought

Enterprise hunting is not about writing the biggest query. It is about building the clearest path from signal to evidence to action.

Agent Foskett mindsetDo not hunt in isolated tables forever. Connect the evidence, normalise the story and let the timeline reveal what really happened.

Advanced KQL SeriesLesson 129 brings together joins, dynamic data, parsing, regex, functions and time series analysis into enterprise-scale hunting logic.

Develop IT. Protect IT.GEMXIT PTY LTD | GEMXIT UK LTD