ACSC Logo
© GEMXIT
GEMXIT Logo
Agent Foskett Academy • Lesson 124 • Advanced KQL

Advanced KQL: Optimising Query Performance.

The query worked perfectly.

Then it was run across 90 days of telemetry from thousands of endpoints, identities, emails and cloud events.

Suddenly it was slow. Then it timed out.

Microsoft Defender XDR was not broken. The query simply had not been designed to scale. Agent Foskett realised that writing correct KQL is only half the job. Great security analysts also know how to write fast KQL.

Start Lesson 124 Back to Academy
Agent Foskett Academy Lesson 124 optimising KQL query performance in Microsoft Defender XDR
Lesson overview

Learn how experienced Microsoft Defender XDR analysts make KQL queries faster, cleaner and more scalable across large security environments.

Filter early and reduce scanned data
Project only the columns you need
Optimise joins, parsing and mv-expand
Build scalable enterprise hunting queries

Why query performance matters

After joins, dynamic data and mv-expand, the next skill is performance. A query can be technically correct and still be unsuitable for a large Microsoft Defender XDR environment.
Large environments generate huge telemetryEndpoint, identity, email and cloud tables can contain millions or billions of events. A query that works in a lab may struggle across a full tenant.
Slow queries slow investigationsDuring an incident, analysts need to test ideas quickly. Efficient KQL helps defenders move from suspicion to evidence faster.
Expensive operators multiply quicklyJoins, mv-expand, parse_json, regex and broad searches can create heavy workloads when they are used too early or too often.

The performance mindset

Experienced analysts do not only ask whether KQL can return the answer. They ask how much data KQL must process to get there.
Can I reduce the dataset first?Time filters, table selection and targeted where clauses should usually appear before expensive transformations.
Can I carry fewer columns?Every unnecessary column adds weight to the pipeline. Projection keeps later operations smaller and cleaner.
Can I avoid expanding everything?Dynamic arrays and JSON fields should only be expanded or parsed when the investigation actually needs that detail.
Can I test on a smaller window?Build and validate the logic on a short timeframe before increasing the scope for production hunting.
Can I join small to large?Pre-filter and summarize one side of the join so the engine has less work to do.
Can I reuse clean logic?Let statements and reusable functions make complex hunting queries easier to tune, test and maintain.

Rule 1 — Filter early

The earlier you reduce the dataset, the less work every later operator needs to perform.
rule-1-filter-early.kql
  1. 1
  2. 2
  3. 3
  4. 4
  5. 5
  6. 6
  7. 7
  8. 8
  9. 9
  10. 10
  11. 11
  12. 12
// Poor pattern: expensive work before filtering
DeviceProcessEvents
| summarize EventCount = count() by DeviceName, FileName, Timestamp
| where Timestamp > ago(7d)

// Better pattern: reduce rows first
DeviceProcessEvents
| where Timestamp > ago(7d)
| where FileName has_any ("powershell.exe", "cmd.exe", "wscript.exe")
| summarize EventCount = count() by DeviceName, FileName

Rule 2 — Target the right table

Avoid broad searches when you already know which Defender XDR table contains the evidence.
rule-2-target-specific-table.kql
  1. 1
  2. 2
  3. 3
  4. 4
  5. 5
  6. 6
  7. 7
  8. 8
  9. 9
  10. 10
// Avoid this during normal hunting unless you truly need a global search
search "invoice.zip"

// Better: target the relevant table
EmailAttachmentInfo
| where Timestamp > ago(14d)
| where FileName has "invoice"
| project Timestamp, RecipientEmailAddress, SenderFromAddress, FileName, SHA256

Rule 3 — Project only useful columns

Carry the fields required for investigation, correlation and reporting. Drop the rest early.
rule-3-project-useful-columns.kql
  1. 1
  2. 2
  3. 3
  4. 4
  5. 5
  6. 6
  7. 7
  8. 8
  9. 9
  10. 10
  11. 11
  12. 12
  13. 13
  14. 14
let TimeFrame = 7d;
DeviceNetworkEvents
| where Timestamp > ago(TimeFrame)
| where RemoteUrl has_any ("pastebin", "ngrok", "duckdns")
| project Timestamp,
          DeviceName,
          InitiatingProcessAccountName,
          InitiatingProcessFileName,
          RemoteUrl,
          RemoteIP,
          ActionType
| order by Timestamp desc

Rule 4 — Use precise time windows

Large time ranges are sometimes required, but they should be intentional. Start narrow, then widen when the evidence supports it.
rule-4-time-windows.kql
  1. 1
  2. 2
  3. 3
  4. 4
  5. 5
  6. 6
  7. 7
  8. 8
  9. 9
  10. 10
// Start with the suspected compromise window
let StartTime = datetime(2026-07-01 08:00:00);
let EndTime = datetime(2026-07-01 18:00:00);
DeviceProcessEvents
| where Timestamp between (StartTime .. EndTime)
| where DeviceName =~ "WORKSTATION-17"
| project Timestamp, DeviceName, AccountName, FileName, ProcessCommandLine
| order by Timestamp asc

Rule 5 — Be careful with mv-expand

mv-expand is powerful, but it can multiply the number of rows. Expand only after filtering to the smallest useful dataset.
rule-5-careful-mv-expand.kql
  1. 1
  2. 2
  3. 3
  4. 4
  5. 5
  6. 6
  7. 7
  8. 8
  9. 9
  10. 10
let TimeFrame = 7d;
AlertEvidence
| where Timestamp > ago(TimeFrame)
| where Title has_any ("Suspicious", "Malware", "Phishing")
| project Timestamp, AlertId, Title, EntityType, EvidenceRole, AdditionalFields
| extend ParsedFields = parse_json(AdditionalFields)
| mv-expand ParsedFields
| project Timestamp, AlertId, Title, EntityType, EvidenceRole, ParsedFields

Rule 6 — Parse JSON once

Repeated parse_json calls make queries harder to read and can add unnecessary work. Parse once, then reuse the extracted object.
rule-6-parse-once.kql
  1. 1
  2. 2
  3. 3
  4. 4
  5. 5
  6. 6
  7. 7
  8. 8
  9. 9
  10. 10
  11. 11
let TimeFrame = 7d;
CloudAppEvents
| where Timestamp > ago(TimeFrame)
| where ActionType has_any ("FileDownloaded", "FileAccessed", "MailItemsAccessed")
| extend Parsed = parse_json(RawEventData)
| extend UserAgent = tostring(Parsed.UserAgent),
         Operation = tostring(Parsed.Operation),
         Workload = tostring(Parsed.Workload)
| project Timestamp, AccountDisplayName, ActionType, Application, Workload, Operation, UserAgent, IPAddress

Rule 7 — Optimise join order

Join smaller, pre-filtered datasets to larger datasets. Do not join two massive raw tables unless there is no better option.
rule-7-optimise-join-order.kql
  1. 1
  2. 2
  3. 3
  4. 4
  5. 5
  6. 6
  7. 7
  8. 8
  9. 9
  10. 10
  11. 11
  12. 12
  13. 13
let TimeFrame = 7d;
let SuspiciousHashes =
    DeviceFileEvents
    | where Timestamp > ago(TimeFrame)
    | where FileName endswith ".ps1" or FileName endswith ".js"
    | summarize by SHA256;
DeviceProcessEvents
| where Timestamp > ago(TimeFrame)
| where SHA256 in (SuspiciousHashes)
| project Timestamp, DeviceName, AccountName, FileName, ProcessCommandLine, SHA256
| order by Timestamp desc

Rule 8 — Summarize with purpose

Summarize is excellent for reducing noisy telemetry, but it should usually happen after the dataset has been filtered.
rule-8-summarize-with-purpose.kql
  1. 1
  2. 2
  3. 3
  4. 4
  5. 5
  6. 6
  7. 7
  8. 8
  9. 9
  10. 10
  11. 11
  12. 12
  13. 13
  14. 13
let TimeFrame = 24h;
DeviceLogonEvents
| where Timestamp > ago(TimeFrame)
| where ActionType == "LogonSuccess"
| where LogonType has_any ("RemoteInteractive", "Network")
| summarize LogonCount = count(),
            Devices = dcount(DeviceName),
            FirstSeen = min(Timestamp),
            LastSeen = max(Timestamp)
      by AccountName, RemoteIP
| where LogonCount > 10 or Devices > 3
| order by LogonCount desc

Rule 9 — Use top and take while testing

During query development, limit result sets so you can validate logic quickly before running the full hunt.
rule-9-test-with-limits.kql
  1. 1
  2. 2
  3. 3
  4. 4
  5. 5
  6. 6
  7. 7
  8. 8
let TimeFrame = 3d;
DeviceProcessEvents
| where Timestamp > ago(TimeFrame)
| where ProcessCommandLine has_any ("EncodedCommand", "FromBase64String", "DownloadString")
| project Timestamp, DeviceName, AccountName, FileName, ProcessCommandLine
| top 100 by Timestamp desc

Rule 10 — Build reusable performance patterns

Use let statements to define timeframes, users, devices and suspect indicators once. This makes queries easier to read, tune and reuse.
rule-10-reusable-patterns.kql
  1. 1
  2. 2
  3. 3
  4. 4
  5. 5
  6. 6
  7. 7
  8. 8
  9. 9
  10. 10
  11. 11
  12. 12
  13. 13
  14. 14
  15. 15
let TimeFrame = 14d;
let SuspectUser = "user@contoso.com";
let SuspectDevices =
    DeviceLogonEvents
    | where Timestamp > ago(TimeFrame)
    | where AccountUpn =~ SuspectUser
    | summarize by DeviceName;
DeviceProcessEvents
| where Timestamp > ago(TimeFrame)
| where DeviceName in (SuspectDevices)
| where FileName has_any ("powershell.exe", "cmd.exe", "rundll32.exe")
| project Timestamp, DeviceName, AccountName, FileName, ProcessCommandLine
| order by Timestamp desc

Performance comparison

Use this quick guide when reviewing slow hunting queries.
Avoid search *Prefer a specific table such as DeviceProcessEvents, EmailEvents, IdentityLogonEvents or CloudAppEvents.
Avoid parsing every rowFilter first, then parse only the smaller subset that needs dynamic field inspection.
Avoid expanding too earlyUse mv-expand only after reducing the dataset to relevant alerts, entities or rows.
Avoid large-to-large joinsCreate a small lookup set first, then join or filter the larger table.
Avoid huge default timeframesUse the incident window first. Expand the timeframe when the question requires it.
Avoid carrying everythingUse project to keep the useful fields and reduce noise in the pipeline.

Real-world optimisation walkthrough

This example starts with a broad encoded PowerShell hunt and then improves it into a faster, more targeted Defender XDR query.
real-world-optimised-powershell-hunt.kql
  1. 1
  2. 2
  3. 3
  4. 4
  5. 5
  6. 6
  7. 7
  8. 8
  9. 9
  10. 10
  11. 11
  12. 12
  13. 13
  14. 14
  15. 15
  16. 16
  17. 17
// Optimised version
let TimeFrame = 7d;
let SuspiciousTerms = dynamic(["EncodedCommand", "FromBase64String", "DownloadString", "IEX", "WebClient"]);
DeviceProcessEvents
| where Timestamp > ago(TimeFrame)
| where FileName =~ "powershell.exe"
| where ProcessCommandLine has_any (SuspiciousTerms)
| project Timestamp,
          DeviceName,
          AccountName,
          InitiatingProcessFileName,
          FileName,
          ProcessCommandLine,
          ReportId
| order by Timestamp desc

What changed in the optimised version?

The improved query reduces work before applying the expensive logic and returns only the columns needed for triage.
Specific tableThe query uses DeviceProcessEvents instead of searching across every table.
Time filter firstThe query narrows the dataset to the investigation window before matching suspicious command-line strings.
Process filter earlyThe query focuses on powershell.exe before inspecting command-line content.
Reusable indicatorsSuspicious terms are defined once in a dynamic list so the logic is easy to tune.
Clean projectionThe output contains triage fields, not every available column.
Readable structureThe query is easier to test, explain and convert into reusable hunting logic.

Query performance checklist

Before running a large hunting query, Agent Foskett asks these questions.
Have I filtered by time first?Start with the shortest useful timeframe and expand only when required.
Am I using the right table?Target the data source that is most likely to hold the evidence.
Can I project fewer columns?Keep the fields needed for investigation, enrichment and reporting.
Do I really need mv-expand?Only expand arrays after the dataset has been reduced.
Can I parse once?Extract dynamic fields once and reuse the parsed values.
Is my join small enough?Pre-filter or summarize one side before correlation.
Can I test with top or take?Validate query logic quickly before running the full dataset.
Will this scale tomorrow?A good hunting query should still work when telemetry volume increases.

Related Agent Foskett Academy lessons

These lessons connect directly to query performance, scalability and advanced KQL design.
Advanced KQL: Mastering Join OperationsOptimise how datasets are correlated across Defender XDR tables.
Advanced KQL: Working with Dynamic DataUnderstand dynamic fields before parsing, extracting or expanding them.
Advanced KQL: Mastering mv-expandLearn when expanding arrays is useful and when it can create unnecessary volume.
KQL summarize OperatorUse aggregation to reduce noisy security telemetry into meaningful investigation results.
KQL project OperatorControl which fields move through the query pipeline.
KQL where OperatorFilter Defender XDR telemetry before applying heavier transformations.
Building Reusable Hunting QueriesTurn tuned query logic into repeatable hunting patterns.
Investigating DeviceProcessEventsApply performance-focused KQL to process creation and command-line hunting.

Coming next

The Advanced KQL series continues by turning optimised query logic into reusable functions.
Lesson 125 — Building Reusable KQL FunctionsNext, Agent Foskett shows how to package repeatable hunting logic into cleaner, reusable KQL patterns for Microsoft Defender XDR.
Why this mattersOptimised queries are easier to convert into reusable functions, workbook logic and enterprise-grade hunting workflows.

Final thought

Fast KQL is not about writing less code. It is about asking Microsoft Defender XDR to process less unnecessary data.
Agent Foskett mindsetA correct query answers the question. A great query answers the question quickly, clearly and at scale.
Advanced KQL SeriesLesson 124 connects joins, dynamic data and mv-expand into the performance habits needed for enterprise hunting.
Develop IT. Protect IT.GEMXIT PTY LTD | GEMXIT UK LTD

Advanced KQL Optimising Query Performance in Microsoft Defender XDR

Agent Foskett Academy Lesson 124 teaches Microsoft security analysts how to optimise KQL query performance in Microsoft Defender XDR by filtering early, projecting useful columns, reducing dynamic parsing, optimising joins and avoiding unnecessary mv-expand operations.

Learn KQL query optimisation for Microsoft Defender XDR hunting

This advanced KQL lesson explains how to write faster and more scalable Defender XDR hunting queries across endpoint, identity, email and cloud telemetry tables.

Microsoft Defender XDR KQL performance tuning tutorial

Security analysts can use this lesson to improve KQL query speed, reduce scanned data, build reusable performance patterns and prepare for enterprise hunting workflows.

ACSC Logo

GEMXIT PTY LTD  |  GEMXIT UK LTD  |  © GEMXIT