Agent Foskett Academy • Lesson 92 • Advanced KQL

Using let Statements to Build Reusable Hunting Queries.

The investigation was not complicated.

The query was.

The same values appeared again and again.

Agent Foskett knew there was a cleaner way.

Start Lesson 92 Back to Academy

Agent Foskett Academy lesson using let statements to build reusable hunting queries in KQL

Lesson overview

Learn how defenders use let statements in KQL to create reusable variables, simplify complex queries and build cleaner Microsoft Defender XDR threat hunting investigations.

Create reusable investigation variables

Avoid repeating the same values

Build cleaner hunting queries

Combine let with joins and lists

Why let statements matter

As investigations grow, queries can become difficult to read and maintain. let statements help defenders define values once, reuse them throughout the query and change investigation parameters without rewriting every line.

Variables make hunts easier to manageA user, device, time range, process list or indicator can be defined once and reused throughout the query.

Cleaner queries reduce mistakesWhen the same value appears many times, one typo can break an investigation. let statements keep important values consistent.

Reusable logic saves timeA well-built hunting query can be copied, adjusted and reused during future Microsoft Defender XDR investigations.

The reusable hunting workflow

Agent Foskett uses let statements to turn messy one-off searches into structured investigation queries.

1. Define the investigation targetStart with the user, device, hash, domain, IP address or process name that anchors the hunt.

2. Set the time rangeUse a reusable time window so every table is searched consistently.

3. Create indicator listsUse dynamic arrays for suspicious processes, domains, file extensions or accounts.

4. Build filtered evidence setsCreate small named datasets before joining, summarising or unioning results.

5. Reuse values across tablesApply the same account, device or IOC across DeviceProcessEvents, DeviceNetworkEvents and other tables.

6. Keep the output readableUse project and order by to produce clean evidence that can be explained during an investigation.

Step 1 — Create a simple investigation variable

A let statement can define a user, device or indicator once so it can be reused throughout the query.

let InvestigatedUser = "alex.wilson@contoso.com";
IdentityLogonEvents
| where Timestamp > ago(7d)
| where AccountUpn == InvestigatedUser
| project Timestamp,
          AccountUpn,
          DeviceName,
          ActionType,
          LogonType,
          RemoteIP
| order by Timestamp desc

Step 2 — Reuse a time range across the hunt

A shared time range helps keep process, network, file and logon searches aligned.

let TimeRange = 24h;
DeviceProcessEvents
| where Timestamp > ago(TimeRange)
| where FileName in~ ("powershell.exe", "cmd.exe", "rundll32.exe")
| project Timestamp,
          DeviceName,
          AccountUpn,
          FileName,
          ProcessCommandLine
| order by Timestamp desc

Step 3 — Create a reusable suspicious process list

Dynamic arrays are useful when a hunt needs to search for several values at once.

let SuspiciousProcesses = dynamic([
    "powershell.exe",
    "cmd.exe",
    "rundll32.exe",
    "mshta.exe",
    "regsvr32.exe",
    "certutil.exe"
]);
DeviceProcessEvents
| where Timestamp > ago(24h)
| where FileName in~ (SuspiciousProcesses)
| project Timestamp,
          DeviceName,
          AccountUpn,
          FileName,
          InitiatingProcessFileName,
          ProcessCommandLine
| order by Timestamp desc

Step 4 — Reuse one device across multiple tables

Use the same device variable to collect process, network and file evidence from one endpoint.

let InvestigatedDevice = "LAPTOP-042";
let TimeRange = 24h;
DeviceProcessEvents
| where Timestamp > ago(TimeRange)
| where DeviceName == InvestigatedDevice
| project Timestamp, DeviceName, EvidenceType="Process", Detail=FileName, Extra=ProcessCommandLine
| union (
    DeviceNetworkEvents
    | where Timestamp > ago(TimeRange)
    | where DeviceName == InvestigatedDevice
    | project Timestamp, DeviceName, EvidenceType="Network", Detail=RemoteUrl, Extra=RemoteIP
)
| union (
    DeviceFileEvents
    | where Timestamp > ago(TimeRange)
    | where DeviceName == InvestigatedDevice
    | project Timestamp, DeviceName, EvidenceType="File", Detail=FileName, Extra=FolderPath
)
| order by Timestamp asc

Step 5 — Combine let statements with joins

Named datasets make joins easier to read and explain.

let TimeRange = 24h;
let SuspiciousPowerShell =
    DeviceProcessEvents
    | where Timestamp > ago(TimeRange)
    | where FileName =~ "powershell.exe"
    | where ProcessCommandLine has_any ("-enc", "EncodedCommand", "DownloadString", "Invoke-WebRequest")
    | project ProcessTime=Timestamp, DeviceId, DeviceName, AccountUpn, ProcessCommandLine;
let NetworkConnections =
    DeviceNetworkEvents
    | where Timestamp > ago(TimeRange)
    | project NetworkTime=Timestamp, DeviceId, AccountUpn, RemoteUrl, RemoteIP, InitiatingProcessFileName;
SuspiciousPowerShell
| join kind=inner (NetworkConnections) on DeviceId, AccountUpn
| where NetworkTime between (ProcessTime .. ProcessTime + 10m)
| project ProcessTime, NetworkTime, DeviceName, AccountUpn, ProcessCommandLine, RemoteUrl, RemoteIP
| order by ProcessTime asc

Step 6 — Build an IOC hunt with reusable values

let statements are especially useful when hunting for the same indicators across several Defender XDR tables.

let TimeRange = 7d;
let SuspiciousDomains = dynamic([
    "login-contoso-support.example",
    "cdn-update-service.example",
    "secure-document-review.example"
]);
DeviceNetworkEvents
| where Timestamp > ago(TimeRange)
| where RemoteUrl has_any (SuspiciousDomains)
| project Timestamp,
          DeviceName,
          AccountUpn,
          RemoteUrl,
          RemoteIP,
          InitiatingProcessFileName
| order by Timestamp desc

How to read let-based queries

A good let-based query should read like an investigation plan.

The top defines the investigationThe first lines should show the time range, target account, target device or indicator list.

The middle builds evidence setsNamed datasets should make it clear which tables are being searched and why.

The bottom answers the questionThe final query should join, union, summarise or project the evidence needed for the investigation.

The output should be explainableIf the result is going into an incident report, it should be easy to understand without rewriting the query.

Real-world investigation

The alertA suspicious PowerShell command executed on one endpoint and included an encoded command line.

The first queryThe analyst searched for the same command manually across several tables and quickly repeated the same values many times.

The cleaner approachAgent Foskett defined the time range, device name and suspicious process list once using let statements.

The wider huntThe same variables were reused across process, network and file telemetry to build a cleaner device timeline.

The joined evidenceNamed datasets made it easier to join suspicious PowerShell activity to outbound network connections.

The conclusionThe final query was easier to explain, easier to modify and safer to reuse during the incident response process.

Investigation checklist

Variables definedCreate reusable values for users, devices, processes, domains, hashes or IP addresses.

Time range reusedUse one time range across all related evidence searches.

Indicator lists createdUse dynamic arrays when searching for multiple related values.

Evidence sets namedUse let to build small, readable datasets before joining or unioning tables.

Repeated values removedAvoid typing the same user, device or IOC throughout the query.

Output simplifiedProject the fields that help explain the investigation clearly.

Related Agent Foskett Academy lessons

Advanced join Techniques in KQLUse advanced joins to correlate evidence across Defender XDR tables.

Creating Investigation Parameters with KQLLearn how reusable values support repeatable investigation workflows.

Building an IOC HuntSearch indicators across multiple Microsoft Defender XDR tables.

Building an Incident TimelineCombine multiple evidence sources into one investigation timeline.

Investigating DeviceProcessEventsUse process telemetry as a foundation for endpoint investigation queries.

Working with Dynamic Data using parse_jsonPrepare for the next advanced KQL topic: dynamic data and JSON.

Advanced KQL milestone

Lesson 92 continues the advanced KQL block inside the Agent Foskett Academy.

Readable KQL mattersThreat hunting queries are not just written for the engine. They are written for analysts who need to understand the evidence.

Reusable hunting is fasterA query that is easy to adjust can support multiple investigations without being rebuilt from scratch.

Agent Foskett continuesThe next lessons go deeper into dynamic data, JSON fields and more advanced Microsoft Defender XDR hunting techniques.

Coming next

Lesson 93 — Working with Dynamic Data and JSON in KQLNext, Agent Foskett Academy explains how defenders use dynamic fields, parse_json and JSON evidence during Microsoft Defender XDR investigations.

Why this mattersMany Defender XDR fields contain structured data. Understanding dynamic values helps defenders extract evidence that simple columns may hide.

Final thought

A good let statement turns repeated values into reusable investigation logic.

Agent Foskett mindsetDo not let the same values clutter every line. Define them once, then focus on the evidence.

The query became readableWhen variables, evidence sets and outputs are clearly named, the investigation becomes easier to explain.

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

Using let Statements to Build Reusable Hunting Queries

Agent Foskett Academy Lesson 92 teaches defenders how to use let statements in KQL to create reusable Microsoft Defender XDR hunting queries.

KQL let statements for Microsoft Defender XDR hunting

This lesson explains how let statements, reusable variables, dynamic arrays, investigation parameters, DeviceProcessEvents, DeviceNetworkEvents, DeviceFileEvents, IdentityLogonEvents, joins, unions and IOC hunts help defenders write cleaner and more maintainable KQL queries.