Agent Foskett Academy • Lesson 100 • Advanced KQL

Building Reusable Hunting Queries in KQL.

Every investigation started the same way.

Same time filter. Same suspicious indicators. Same tables. Same joins.

Agent Foskett realised the answer was not to write the same query again.

The answer was to build hunting queries that could be reused, adapted and trusted.

Start Lesson 100 Back to Academy

Agent Foskett Academy lesson building reusable hunting queries in KQL

Lesson overview

Learn how to turn individual KQL techniques into reusable Microsoft Defender XDR hunting queries using variables, let statements, modular query blocks, joins, summaries and investigation workflows.

Build reusable time and indicator variables

Create modular hunting query blocks

Combine joins, summaries and dynamic data

Create an investigation library

Why reusable hunting queries matter

Reusable KQL helps defenders move faster because the core logic has already been written, tested and refined.

Consistency improves investigationsWhen analysts use trusted query patterns, they are less likely to miss key filters, joins or fields during a live incident.

Reusable queries save timeInstead of starting from a blank query window, defenders can adjust variables and immediately focus on the evidence.

Modular queries are easier to maintainSmaller query blocks are easier to test, explain and update as Microsoft Defender XDR telemetry changes.

The reusable hunting workflow

Agent Foskett builds reusable hunts by separating the investigation into repeatable building blocks.

1. Define the time windowUse one variable for the time range so every table uses the same investigation window.

2. Define indicatorsStore suspicious domains, file names, hashes, IP addresses or commands in reusable dynamic lists.

3. Build table modulesCreate small let blocks for email, endpoint, identity, URL and alert evidence.

4. Reduce each datasetFilter and project each module before joining or summarising.

5. Correlate evidenceUse joins and shared entity fields to connect activity across tables.

6. Produce a clean resultReturn a readable investigation view with only the evidence needed for triage and response.

Step 1 — Start with reusable variables

Use variables for values that change often. This makes the query easier to reuse across different incidents.

let TimeFrame = 7d;
DeviceProcessEvents
| where Timestamp > ago(TimeFrame)
| where FileName in~ ("powershell.exe", "cmd.exe", "rundll32.exe")
| project Timestamp,
          DeviceName,
          AccountName,
          FileName,
          ProcessCommandLine
| order by Timestamp desc

Step 2 — Store indicators in a dynamic list

A reusable indicator list lets defenders update the hunt without rewriting the query logic.

let TimeFrame = 7d;
let SuspiciousDomains = dynamic(["example-bad-domain.com", "login-check.example", "update-service.example"]);
DeviceNetworkEvents
| where Timestamp > ago(TimeFrame)
| where RemoteUrl has_any (SuspiciousDomains)
| project Timestamp,
          DeviceName,
          InitiatingProcessFileName,
          RemoteUrl,
          RemoteIP,
          ActionType
| order by Timestamp desc

Step 3 — Build a reusable email evidence block

Named query blocks make it clear what each section is responsible for.

let TimeFrame = 7d;
let SuspiciousSenderDomains = dynamic(["contoso-security.example", "account-alert.example"]);
let EmailEvidence =
    EmailEvents
    | where Timestamp > ago(TimeFrame)
    | where SenderFromDomain has_any (SuspiciousSenderDomains)
    | project EmailTime = Timestamp,
              NetworkMessageId,
              SenderFromAddress,
              RecipientEmailAddress,
              Subject,
              DeliveryAction,
              DeliveryLocation;
EmailEvidence
| order by EmailTime desc

Step 4 — Build a reusable endpoint evidence block

A separate endpoint block can be reused in phishing, malware, persistence and lateral movement investigations.

let TimeFrame = 7d;
let SuspiciousProcessNames = dynamic(["powershell.exe", "mshta.exe", "rundll32.exe", "wscript.exe"]);
let EndpointEvidence =
    DeviceProcessEvents
    | where Timestamp > ago(TimeFrame)
    | where FileName in~ (SuspiciousProcessNames)
    | project ProcessTime = Timestamp,
              DeviceId,
              DeviceName,
              AccountName,
              FileName,
              ProcessCommandLine;
EndpointEvidence
| order by ProcessTime desc

Step 5 — Summarise reusable evidence

Reusable hunts should help explain scope, not just return thousands of raw events.

let TimeFrame = 7d;
DeviceNetworkEvents
| where Timestamp > ago(TimeFrame)
| where isnotempty(RemoteUrl) or isnotempty(RemoteIP)
| summarize ConnectionCount = count(),
            Devices = make_set(DeviceName, 20),
            Processes = make_set(InitiatingProcessFileName, 20),
            FirstSeen = min(Timestamp),
            LastSeen = max(Timestamp)
      by RemoteUrl,
         RemoteIP
| order by ConnectionCount desc

Step 6 — Reuse arg_max() for latest activity

arg_max() is useful when the investigation needs the latest known event for each user, device or entity.

let TimeFrame = 30d;
IdentityLogonEvents
| where Timestamp > ago(TimeFrame)
| summarize arg_max(Timestamp, *) by AccountUpn
| project Timestamp,
          AccountUpn,
          ActionType,
          IPAddress,
          DeviceName,
          FailureReason
| order by Timestamp desc

Step 7 — Combine reusable modules into a phishing hunt

This pattern combines variables, modules, joins and summaries into a reusable investigation query.

let TimeFrame = 7d;
let SuspiciousTerms = dynamic(["invoice", "payment", "password", "verify", "urgent"]);
let SuspiciousExtensions = dynamic([".html", ".htm", ".js", ".vbs", ".lnk"]);
let EmailEvidence =
    EmailEvents
    | where Timestamp > ago(TimeFrame)
    | where Subject has_any (SuspiciousTerms)
    | project EmailTime = Timestamp,
              NetworkMessageId,
              SenderFromAddress,
              RecipientEmailAddress,
              Subject,
              DeliveryAction,
              DeliveryLocation;
let AttachmentEvidence =
    EmailAttachmentInfo
    | where Timestamp > ago(TimeFrame)
    | where FileName has_any (SuspiciousExtensions)
    | project NetworkMessageId,
              FileName,
              SHA256;
EmailEvidence
| join kind=leftouter AttachmentEvidence on NetworkMessageId
| summarize Recipients = make_set(RecipientEmailAddress, 50),
            AttachmentNames = make_set(FileName, 20),
            MessageCount = count(),
            FirstSeen = min(EmailTime),
            LastSeen = max(EmailTime)
      by SenderFromAddress,
         Subject,
         DeliveryAction,
         DeliveryLocation
| order by MessageCount desc

Reusable query patterns to remember

Use variables for changeable valuesTime ranges, users, domains, hashes and file names should be easy to update at the top of the query.

Name each evidence block clearlyNames such as EmailEvidence, EndpointEvidence and IdentityEvidence make the query easier to read.

Filter before joiningEach reusable block should reduce data before it is combined with another table.

Project useful fieldsKeep entity fields, timestamps and investigation evidence. Remove noisy columns early.

Summarise for scopeUse count(), dcount(), make_set(), min() and max() to explain scale and timeline.

Keep a hunting librarySave trusted queries so future investigations start from tested logic rather than memory.

Real-world investigation

The repeated investigationA phishing alert arrives and the analyst starts writing the same email, attachment and endpoint queries again.

The better approachAgent Foskett opens a reusable phishing hunt with variables for time range, sender domains and suspicious attachment types.

The evidence modulesThe query separates email evidence, attachment evidence, URL evidence and endpoint evidence into clear let blocks.

The correlationJoins connect the message, attachment and device activity so the analyst can see who received it and what happened next.

The summarymake_set(), dcount(), min() and max() turn raw telemetry into a clear investigation summary.

The resultThe query becomes part of the hunting library, ready for the next phishing case with only small changes needed.

Investigation checklist

Can the time range be changed once?A reusable hunt should not require editing the same time filter in multiple places.

Are indicators easy to update?Keep suspicious values near the top of the query in dynamic lists or variables.

Are query blocks named clearly?Readable block names help other analysts understand the investigation flow.

Are joins reduced and intentional?Join only the fields and rows needed to answer the investigation question.

Does the output explain the case?The final table should help with triage, scoping and response decisions.

Can this be reused next month?If the query can be reused with minor edits, it belongs in the hunting library.

Related Agent Foskett Academy lessons

Using let StatementsCreate reusable variables and named query blocks for Defender XDR hunting.

Advanced join Techniques in KQLCorrelate reusable evidence blocks across multiple Microsoft Defender XDR tables.

Using arg_max() in KQLFind the latest activity for each user, device or investigation entity.

Using make_set() in KQLBuild unique lists of users, devices, domains, IP addresses and other evidence.

Using mv-expand in KQLSplit multi-value evidence into individual rows for investigation.

Using Regular Expressions regex in KQLUse pattern matching to find suspicious commands, URLs and file names.

Coming next

Lesson 101 — Hunting for Impossible Travel in Microsoft Defender XDRNext, Agent Foskett Academy moves from individual KQL techniques into real-world investigation walkthroughs that combine everything learned in the first 100 lessons.

Why this mattersReusable hunting queries are the bridge between learning KQL and running mature, repeatable security investigations.

Final thought

Great defenders do not start every investigation from zero.

Agent Foskett mindsetWrite the query once, test it carefully, then turn it into a tool for the next investigation.

CongratulationsYou have reached Lesson 100 of the Agent Foskett Academy and built a strong foundation in KQL, Defender XDR and threat hunting.

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

Building Reusable Hunting Queries in KQL

Agent Foskett Academy Lesson 100 teaches defenders how to build reusable KQL hunting queries using let statements, variables, modular query blocks, joins and Microsoft Defender XDR investigation patterns.

Learn reusable KQL hunting queries for Microsoft Defender XDR

Reusable KQL queries help Microsoft security analysts investigate phishing, endpoint activity, identity events, URLs, attachments and suspicious indicators with consistent and repeatable threat hunting workflows.

KQL reusable hunting query tutorial for Microsoft security analysts

This lesson explains how to combine let statements, dynamic lists, joins, arg_max(), make_set(), mv-expand, mv-apply and regex into practical Microsoft Defender XDR hunting queries.