Agent Foskett Academy • Lesson 130 • Advanced KQL

Advanced KQL: Building a Complete Hunting Workbook.

One alert was not enough.

The sign-in looked suspicious, the endpoint showed strange process activity, the email trail raised more questions, and the cloud activity did not line up with the user's normal behaviour.

Agent Foskett realised the answer was not hidden in one query. It was spread across identity, endpoint, email, cloud and network telemetry.

The investigation needed more than a hunt. It needed a complete hunting workbook that could tell the full story.

Start Lesson 130 Back to Academy

Agent Foskett Academy lesson building a complete Microsoft Defender XDR hunting workbook

Lesson overview

Learn how to combine advanced KQL techniques into a complete Microsoft Defender XDR hunting workbook for enterprise-scale investigations.

Design a complete hunting workflow

Correlate identity, endpoint, email and cloud evidence

Build reusable workbook query sections

Turn telemetry into investigation-ready findings

Why a complete hunting workbook matters

A complete hunting workbook is more than a collection of saved queries. It is an investigation framework that guides an analyst from the first alert through identity, endpoint, email, cloud and timeline evidence.

One alert rarely tells the full storyA sign-in alert may only be the first clue. The supporting evidence often lives in endpoint, email, network and cloud telemetry.

Workbooks create repeatable investigationsA well-designed workbook gives analysts a consistent path to follow instead of relying on memory during a live incident.

Enterprise hunts need structureLarge tenants generate too much telemetry for ad hoc investigation. A workbook organises queries, pivots and summaries into a scalable workflow.

The complete workbook mindset

By Lesson 130, students have learned the building blocks. This capstone lesson shows how those building blocks become a complete investigation experience.

Start with the incident questionEvery workbook should begin with a clear question: who was affected, what changed, where did activity occur and when did it happen?

Build from broad to specificStart with high-level summaries, then provide drilldowns for users, devices, messages, files, processes, URLs and cloud actions.

Reuse trusted logicUse reusable functions, consistent columns and known-good joins so the workbook remains maintainable over time.

Preserve investigation contextCarry the same user, device, timeframe, IP address or message ID across every workbook section.

Optimise for large tenantsFilter early, project useful columns, avoid unnecessary expansion and keep expensive operations controlled.

Tell the storyThe final workbook should help the analyst explain what happened, when it happened and what evidence supports the conclusion.

Workbook section 1 — Investigation parameters

Begin every workbook with shared parameters. This keeps every query aligned to the same timeframe and entity scope.

let Lookback = 7d;
let InvestigationUser = "user@contoso.com";
let InvestigationDevice = "device01.contoso.com";
let InvestigationSender = "sender@example.com";
let InvestigationIP = "203.0.113.10";
let StartTime = ago(Lookback);
let EndTime = now();
print
    Lookback = Lookback,
    InvestigationUser = InvestigationUser,
    InvestigationDevice = InvestigationDevice,
    InvestigationSender = InvestigationSender,
    InvestigationIP = InvestigationIP,
    StartTime = StartTime,
    EndTime = EndTime

Workbook section 2 — Identity investigation summary

The identity section establishes whether the user account showed suspicious sign-in, risk, MFA, location or Conditional Access activity.

IdentityLogonEvents
| where Timestamp between (StartTime .. EndTime)
| where AccountUpn =~ InvestigationUser or IPAddress == InvestigationIP
| summarize
    LogonEvents = count(),
    SuccessfulLogons = countif(ActionType has "LogonSuccess"),
    FailedLogons = countif(ActionType has "LogonFailed"),
    Locations = make_set(Location, 10),
    IPAddresses = make_set(IPAddress, 20),
    FirstSeen = min(Timestamp),
    LastSeen = max(Timestamp)
  by AccountUpn
| order by LastSeen desc

Workbook section 3 — Identity drilldown timeline

After the summary, provide a chronological identity view that can be correlated with email, endpoint and cloud activity.

IdentityLogonEvents
| where Timestamp between (StartTime .. EndTime)
| where AccountUpn =~ InvestigationUser
| project
    Timestamp,
    Source = "IdentityLogonEvents",
    Entity = AccountUpn,
    Activity = ActionType,
    IPAddress,
    Location,
    DeviceName,
    FailureReason
| order by Timestamp asc

Workbook section 4 — Email investigation summary

The email section identifies messages that may have started the incident or contributed to user compromise.

EmailEvents
| where Timestamp between (StartTime .. EndTime)
| where RecipientEmailAddress =~ InvestigationUser
   or SenderFromAddress =~ InvestigationSender
   or SenderMailFromAddress =~ InvestigationSender
| summarize
    Messages = count(),
    Quarantined = countif(DeliveryAction =~ "Quarantined"),
    Delivered = countif(DeliveryAction =~ "Delivered"),
    ThreatTypes = make_set(ThreatTypes, 10),
    Subjects = make_set(Subject, 10),
    NetworkMessageIds = make_set(NetworkMessageId, 20)
  by SenderFromAddress, SenderMailFromAddress
| order by Messages desc

Workbook section 5 — URL and attachment pivots

A complete workbook should let analysts pivot from email metadata into URLs and attachments without manually rebuilding joins.

let RelatedMessages =
    EmailEvents
    | where Timestamp between (StartTime .. EndTime)
    | where RecipientEmailAddress =~ InvestigationUser
       or SenderFromAddress =~ InvestigationSender
       or SenderMailFromAddress =~ InvestigationSender
    | project NetworkMessageId, EmailTimestamp = Timestamp, SenderFromAddress, RecipientEmailAddress, Subject;
RelatedMessages
| join kind=leftouter (
    UrlClickEvents
    | where Timestamp between (StartTime .. EndTime)
    | project NetworkMessageId, UrlClickTimestamp = Timestamp, Url, ActionType, IsClickedThrough
) on NetworkMessageId
| join kind=leftouter (
    EmailAttachmentInfo
    | where Timestamp between (StartTime .. EndTime)
    | project NetworkMessageId, FileName, SHA256, FileType
) on NetworkMessageId
| project EmailTimestamp, SenderFromAddress, RecipientEmailAddress, Subject, Url, ActionType, IsClickedThrough, FileName, SHA256
| order by EmailTimestamp desc

Workbook section 6 — Endpoint process investigation

The endpoint section checks whether the target device executed suspicious processes, LOLBins or encoded commands during the investigation window.

DeviceProcessEvents
| where Timestamp between (StartTime .. EndTime)
| where DeviceName =~ InvestigationDevice
| where FileName in~ ("powershell.exe", "pwsh.exe", "cmd.exe", "rundll32.exe", "mshta.exe", "certutil.exe", "regsvr32.exe")
   or ProcessCommandLine has_any ("-enc", "-encodedcommand", "downloadstring", "iex", "bypass", "hidden")
| project
    Timestamp,
    DeviceName,
    InitiatingProcessAccountName,
    FileName,
    ProcessCommandLine,
    InitiatingProcessFileName,
    InitiatingProcessCommandLine
| order by Timestamp asc

Workbook section 7 — Network behaviour and beaconing

The network section looks for repeated outbound activity, rare domains, suspicious ports or process-to-network pivots.

DeviceNetworkEvents
| where Timestamp between (StartTime .. EndTime)
| where DeviceName =~ InvestigationDevice
| where isnotempty(RemoteUrl) or isnotempty(RemoteIP)
| summarize
    Connections = count(),
    FirstSeen = min(Timestamp),
    LastSeen = max(Timestamp),
    Processes = make_set(InitiatingProcessFileName, 10),
    Ports = make_set(RemotePort, 10)
  by DeviceName, RemoteUrl, RemoteIP
| extend DurationMinutes = datetime_diff("minute", LastSeen, FirstSeen)
| order by Connections desc

Workbook section 8 — Cloud application activity

Cloud activity can reveal file access, OAuth behaviour, app activity, downloads, sharing and post-compromise actions outside the endpoint.

CloudAppEvents
| where Timestamp between (StartTime .. EndTime)
| where AccountDisplayName =~ InvestigationUser
   or AccountObjectId =~ InvestigationUser
   or IPAddress == InvestigationIP
| project
    Timestamp,
    AccountDisplayName,
    Application,
    ActionType,
    ObjectName,
    IPAddress,
    UserAgent,
    RawEventData
| order by Timestamp asc

Workbook section 9 — Build a unified investigation timeline

The timeline is the heart of the workbook. It normalises identity, email, endpoint, network and cloud events into one chronological view.

let IdentityTimeline =
    IdentityLogonEvents
    | where Timestamp between (StartTime .. EndTime)
    | where AccountUpn =~ InvestigationUser
    | project Timestamp, Source = "Identity", Entity = AccountUpn, Activity = ActionType, Detail = strcat(IPAddress, " ", Location);
let EmailTimeline =
    EmailEvents
    | where Timestamp between (StartTime .. EndTime)
    | where RecipientEmailAddress =~ InvestigationUser or SenderFromAddress =~ InvestigationSender
    | project Timestamp, Source = "Email", Entity = RecipientEmailAddress, Activity = DeliveryAction, Detail = strcat(SenderFromAddress, " | ", Subject);
let EndpointTimeline =
    DeviceProcessEvents
    | where Timestamp between (StartTime .. EndTime)
    | where DeviceName =~ InvestigationDevice
    | project Timestamp, Source = "Endpoint", Entity = DeviceName, Activity = FileName, Detail = ProcessCommandLine;
let CloudTimeline =
    CloudAppEvents
    | where Timestamp between (StartTime .. EndTime)
    | where AccountDisplayName =~ InvestigationUser
    | project Timestamp, Source = "Cloud", Entity = AccountDisplayName, Activity = ActionType, Detail = strcat(Application, " | ", ObjectName);
union IdentityTimeline, EmailTimeline, EndpointTimeline, CloudTimeline
| order by Timestamp asc

Workbook section 10 — Executive investigation summary

A complete workbook should end with an analyst-friendly summary that can support reporting, handover and incident review.

let Timeline =
    union
    (IdentityLogonEvents
        | where Timestamp between (StartTime .. EndTime)
        | where AccountUpn =~ InvestigationUser
        | project Timestamp, Category = "Identity"),
    (EmailEvents
        | where Timestamp between (StartTime .. EndTime)
        | where RecipientEmailAddress =~ InvestigationUser
        | project Timestamp, Category = "Email"),
    (DeviceProcessEvents
        | where Timestamp between (StartTime .. EndTime)
        | where DeviceName =~ InvestigationDevice
        | project Timestamp, Category = "Endpoint"),
    (CloudAppEvents
        | where Timestamp between (StartTime .. EndTime)
        | where AccountDisplayName =~ InvestigationUser
        | project Timestamp, Category = "Cloud");
Timeline
| summarize Events = count(), FirstSeen = min(Timestamp), LastSeen = max(Timestamp) by Category
| order by Events desc

Workbook design checklist

Use this checklist before publishing a hunting workbook for analysts or SOC teams.

Are the inputs reusable?The workbook should use clear parameters for timeframe, user, device, sender, IP address, URL, file hash or message ID.

Does each section answer a question?Avoid adding queries just because they are interesting. Every section should support a decision or investigation pivot.

Can analysts move from summary to detail?Start with counts and summaries, then provide drilldown views for entities, events and timelines.

Are joins controlled?Join only the columns and timeframes needed. Enterprise workbooks should not join huge datasets unnecessarily.

Are dynamic fields parsed safely?Use parse_json(), tostring(), mv-expand and missing-value handling where dynamic telemetry contains important evidence.

Can the evidence be explained?The final output should help an analyst explain the incident clearly to technical and non-technical stakeholders.

Common workbook mistakes

Complete hunting workbooks fail when they become confusing, slow or disconnected from the investigation question.

Starting with too much dataBroad searches across every table can be slow and noisy. Start with strong parameters and expand only when needed.

Ignoring timelinesA workbook without a timeline often misses the sequence of compromise, execution, access and persistence.

Forgetting entity pivotsUsers, devices, IPs, URLs, files and message IDs should flow naturally from one workbook section to the next.

Copying untested queriesEvery query section should be tested with realistic tenants, empty results and large event volumes.

Using inconsistent columnsNormalise names such as Timestamp, Source, Entity, Activity and Detail so results are easy to union and review.

Building for one incident onlyThe best workbooks solve a repeatable investigation pattern, not only a single case.

Related Agent Foskett Academy lessons

Lesson 130 brings together the key skills from the Advanced KQL series.

Lesson 121 — Advanced KQL: Mastering Join OperationsUse joins to correlate identity, endpoint, email, cloud and alert evidence.

Lesson 123 — Advanced KQL: Mastering mv-expandExpand arrays and multi-value fields safely inside workbook pivots.

Lesson 124 — Advanced KQL: Optimising Query PerformanceKeep workbook queries fast enough for large Microsoft Defender XDR environments.

Lesson 125 — Advanced KQL: Building Reusable KQL FunctionsTurn common workbook sections into reusable functions and investigation components.

Lesson 126 — Advanced KQL: Advanced parse_json()Extract nested dynamic evidence from Defender XDR telemetry.

Lesson 127 — Advanced KQL: Advanced Regular ExpressionsDetect complex patterns in command lines, URLs, filenames and message fields.

Lesson 128 — Advanced KQL: Advanced Time Series AnalysisIdentify spikes, trends, baselines and anomalies over time.

Lesson 129 — Advanced KQL: Building Enterprise Hunting QueriesBuild the enterprise hunting logic that powers complete workbooks.

Advanced KQL series milestone

Lesson 130 completes this Advanced KQL pathway by turning individual techniques into a practical investigation framework.

From syntax to strategyThe Academy has moved from individual KQL operators to complete Defender XDR investigation design.

From queries to workflowsA workbook gives analysts a repeatable way to investigate identity, endpoint, email and cloud activity.

The logs already knewThe purpose of the workbook is to organise the evidence so the attack story becomes visible.

Final thought

Every query tells part of the story. Every table reveals another clue. A complete hunting workbook turns millions of events into a clear, evidence-based investigation.

Agent Foskett mindsetDo not rely on one alert. Build the workbook, follow the evidence and let the timeline explain what really happened.

Advanced KQL SeriesLesson 130 brings joins, dynamic data, mv-expand, performance, functions, parse_json(), regex and time series analysis together.

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

Advanced KQL Building a Complete Hunting Workbook in Microsoft Defender XDR

Agent Foskett Academy Lesson 130 teaches defenders how to build a complete Microsoft Defender XDR hunting workbook using advanced KQL, reusable parameters, identity telemetry, endpoint telemetry, email evidence, cloud activity and investigation timelines.

Microsoft Defender XDR hunting workbook tutorial

This lesson explains how to design complete hunting workflows, correlate Defender XDR tables, use reusable KQL sections, build timelines and produce investigation-ready evidence for enterprise security teams.

Enterprise threat hunting workbook with KQL

Security analysts can use advanced KQL techniques such as joins, parse_json(), mv-expand, regular expressions, time series analysis and reusable functions to build scalable hunting workbooks across identity, endpoint, email, cloud and network telemetry.