ACSC Logo
© 2026 GEMXIT
GEMXIT Logo
Agent Foskett Academy • Lesson 94 • Advanced KQL

Optimising KQL Queries for Performance.

The query worked.

But it was slow.

Too much data. Too many columns. Too many joins.

Agent Foskett knew the answer was not more KQL. It was better KQL.

Start Lesson 94 Back to Academy
Agent Foskett Academy lesson optimising KQL queries for performance in Microsoft Defender XDR
Lesson overview

Learn how to make Microsoft Defender XDR hunting queries faster, cleaner and easier to troubleshoot by reducing data early, selecting useful columns, joining carefully and avoiding expensive search patterns.

Filter early with Timestamp and where
Project only useful columns
Reduce data before joins
Build faster hunting queries

Why KQL performance matters

A query can be technically correct and still be difficult to use during a real investigation. Performance matters because defenders often need answers quickly.
Slow queries delay investigationsWhen a query scans too much data, analysts wait longer before they can confirm scope, impact and next actions.
Large tables need disciplineMicrosoft Defender XDR tables can contain a lot of telemetry. Filtering and projecting early helps keep queries focused.
Readable queries are easier to fixOptimised KQL is not only faster. It is usually easier to read, troubleshoot and reuse during future investigations.

The performance workflow

Agent Foskett starts with the smallest useful dataset, then expands only when the investigation needs more evidence.
1. Start with timeUse a tight Timestamp filter before doing anything else.
2. Filter earlyApply where conditions before joins, unions and summarisation.
3. Project only needed fieldsKeep the columns required for investigation and remove the rest.
4. Reduce before joiningSummarise or filter each side of a join before combining tables.
5. Avoid broad text scansUse targeted operators instead of searching everything everywhere.
6. Validate each stageRun smaller query blocks first so you know where the cost appears.

Step 1 — Filter by time first

Always reduce the time window before scanning a large Defender XDR table.
step-1-filter-time-first.kql
  1. 1
  2. 2
  3. 3
  4. 4
  5. 5
  6. 6
  7. 7
  8. 8
  9. 9
  10. 10
  11. 11
DeviceProcessEvents
| where Timestamp > ago(24h)
| where FileName =~ "powershell.exe"
| project Timestamp,
          DeviceName,
          AccountName,
          FileName,
          ProcessCommandLine
| order by Timestamp desc

Step 2 — Project only useful columns

Projecting useful fields makes output easier to read and keeps later operations focused.
step-2-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
DeviceNetworkEvents
| where Timestamp > ago(24h)
| where RemoteUrl has_any (".ru", ".top", ".xyz")
| project Timestamp,
          DeviceName,
          InitiatingProcessFileName,
          RemoteUrl,
          RemoteIP,
          ActionType
| order by Timestamp desc

Step 3 — Reduce data before joining

Filter each table before joining. This avoids joining unnecessary records and makes the query easier to reason about.
step-3-reduce-before-join.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
  18. 18
  19. 19
  20. 20
  21. 21
  22. 22
  23. 23
  24. 24
  25. 25
  26. 26
  27. 27
  28. 28
  29. 29
  30. 30
  31. 31
  32. 32
  33. 33
  34. 34
let SuspiciousProcesses =
    DeviceProcessEvents
    | where Timestamp > ago(24h)
    | where FileName in~ ("powershell.exe", "cmd.exe", "rundll32.exe", "mshta.exe")
    | project ProcessTime = Timestamp,
              DeviceId,
              DeviceName,
              FileName,
              ProcessCommandLine;
let ExternalConnections =
    DeviceNetworkEvents
    | where Timestamp > ago(24h)
    | where isnotempty(RemoteUrl) or isnotempty(RemoteIP)
    | project NetworkTime = Timestamp,
              DeviceId,
              DeviceName,
              InitiatingProcessFileName,
              RemoteUrl,
              RemoteIP;
SuspiciousProcesses
| join kind=inner ExternalConnections on DeviceId
| where NetworkTime between (ProcessTime .. ProcessTime + 10m)
| project ProcessTime,
          NetworkTime,
          DeviceName,
          FileName,
          ProcessCommandLine,
          RemoteUrl,
          RemoteIP
| order by ProcessTime desc

Step 4 — Avoid unnecessary wildcard searches

Broad searches can be expensive. Use more specific operators and columns where possible.
step-4-avoid-expensive-searches.kql
  1. 1
  2. 2
  3. 3
  4. 4
  5. 5
  6. 6
  7. 7
  8. 8
  9. 9
  10. 10
  11. 11
DeviceProcessEvents
| where Timestamp > ago(7d)
| where ProcessCommandLine has_any ("EncodedCommand", "DownloadString", "Invoke-WebRequest", "IEX")
| project Timestamp,
          DeviceName,
          AccountName,
          FileName,
          ProcessCommandLine
| order by Timestamp desc

Step 5 — Summarise before expanding the investigation

Use summarize to understand scope before opening every detail record.
step-5-summarise-first.kql
  1. 1
  2. 2
  3. 3
  4. 4
  5. 5
  6. 6
  7. 7
  8. 8
  9. 9
  10. 10
  11. 11
DeviceFileEvents
| where Timestamp > ago(24h)
| where ActionType in ("FileCreated", "FileModified", "FileRenamed")
| summarize FileEvents = count(),
            FileNames = make_set(FileName, 20)
      by DeviceName,
         InitiatingProcessAccountUpn
| where FileEvents > 100
| order by FileEvents desc

Step 6 — Test query blocks separately

When a complex query is slow, test each let block or table section on its own before running the full query.
step-6-test-query-blocks.kql
  1. 1
  2. 2
  3. 3
  4. 4
  5. 5
  6. 6
  7. 7
  8. 8
  9. 9
  10. 10
  11. 11
let TargetDevice = "LAPTOP-001";
let TimeRange = 24h;
DeviceProcessEvents
| where Timestamp > ago(TimeRange)
| where DeviceName == TargetDevice
| summarize ProcessCount = count(),
            UniqueProcesses = dcount(FileName),
            ExampleProcesses = make_set(FileName, 20)
      by DeviceName

Performance patterns to remember

Filter firstUse Timestamp and where early so later operations work on less data.
Project earlyKeep only the columns needed for the next investigation step.
Join carefullyReduce both sides of a join before combining large tables.
Summarise scopeUse summarize to understand volume before reviewing raw events.
Avoid vague searchesTarget known fields and terms instead of scanning every column.
Use let for readabilityBreak complex queries into named blocks that can be tested independently.

Real-world investigation

The slow huntAn analyst searches for suspicious PowerShell activity across 30 days and the query takes too long to return.
The first fixAgent Foskett narrows the time range to 24 hours and filters for specific command-line indicators.
The second fixHe projects only the fields needed for the investigation before joining process and network evidence.
The resultThe optimised query returns faster and clearly shows which PowerShell process contacted suspicious infrastructure.
The lessonPerformance is not just about speed. Faster queries help analysts make decisions while the incident is still active.
The habitBuild small, focused query blocks first, then combine them once the evidence path is clear.

Performance checklist

Use a tight time rangeStart with the smallest realistic Timestamp window.
Filter before joiningApply where conditions before join or union operations.
Project required columnsRemove unnecessary fields before later query stages.
Avoid broad contains logicUse has, in, in~ or has_any when they fit the investigation.
Summarise before detailFind scope and outliers before reviewing every row.
Test in stagesRun each block separately when troubleshooting slow queries.

Related Agent Foskett Academy lessons

Advanced join Techniques in KQLCorrelate evidence across multiple Microsoft Defender XDR tables.
Using let StatementsCreate reusable variables and readable query blocks.
Working with Dynamic Data and JSONExtract useful values from complex Defender XDR telemetry.
Counting and Grouping with summarizeUse summarize to reduce large datasets into useful investigation evidence.
Building an IOC HuntApply efficient hunting techniques across multiple indicators.
Building an Incident TimelineUse focused queries to reconstruct attack activity over time.

Coming next

Lesson 95 — Advanced summarize Techniques in KQLNext, Agent Foskett Academy explores advanced summarize patterns including arg_max(), dcount(), make_set(), make_list() and investigation-focused grouping.
Why this mattersOnce queries run efficiently, defenders need stronger ways to group, compare and explain the evidence they find.

Final thought

The best hunting query is not always the longest one.
Agent Foskett mindsetStart narrow, prove the evidence, then expand only when the investigation needs it.
Performance supports clarityA faster query is often a clearer query because each step has a purpose.
Develop IT. Protect IT.GEMXIT PTY LTD | GEMXIT UK LTD
ACSC Logo

GEMXIT PTY LTD  |  GEMXIT UK LTD  |  © GEMXIT 2026