ACSC Logo
© 2026 GEMXIT
GEMXIT Logo
Agent Foskett Academy • Lesson 11 • Combining KQL Operators into Real Investigations

Combining KQL Operators into Real Investigations

The first query returned thousands of results.
Too much noise.
So the defender filtered the time range, removed unnecessary columns, grouped activity, found unusual spikes and ranked the busiest devices.

Slowly, the investigation became clear.

In this final Agent Foskett Academy foundation lesson, you will learn how defenders combine where, project, summarize, count(), bin(), distinct, top and order by into real Microsoft Defender XDR and Sentinel investigations.

Start Lesson 11 Back to Academy
Agent Foskett Academy lesson explaining how to combine KQL operators into real Microsoft security investigations
Lesson overview

Learn how to combine KQL operators into a practical investigation workflow across Microsoft Defender XDR and Microsoft Sentinel telemetry.

Filtering and reducing noise
Grouping and ranking activity
Building investigation pivots
🧠 Real investigations are rarely solved with one query.
KQL becomes powerful when defenders combine operators together to remove noise, expose patterns and reveal the next pivot.
View KQL Hunting Guide →

Why combining operators matters

Earlier Academy lessons introduced individual KQL operators one at a time.

Real investigations do not work that way.

Defenders usually filter the dataset, choose the useful columns, count activity, group it over time, rank the busiest values and sort the evidence into a timeline.
Reduce the noiseUse where and project to narrow the investigation to the evidence that matters.
Find suspicious patternsUse summarize, count() and bin() to identify activity trends, spikes and unusual behaviour.
Create investigation pivotsUse distinct, top and order by to decide what to investigate next.

Investigation scenario

A security analyst notices unusual PowerShell activity overnight.

The first query returns thousands of events.

The goal is to reduce the noise and identify which devices, users and time windows are worth deeper investigation.

Step 1 — Filter the telemetry

Start with where to limit the investigation to recent PowerShell process activity.
filter-powershell-events.kql
  1. 1
  2. 2
  3. 3
  4. 4
  5. 5
DeviceProcessEvents
| where Timestamp > ago(24h)
| where FileName == "powershell.exe"
Plain-English translation:

Show PowerShell process events from the last 24 hours.

Step 2 — Remove unnecessary columns

project helps remove clutter so the investigation focuses on time, device, account and command line evidence.
project-important-columns.kql
  1. 1
  2. 2
  3. 3
  4. 4
  5. 5
  6. 6
DeviceProcessEvents
| where Timestamp > ago(24h)
| where FileName == "powershell.exe"
| project Timestamp, DeviceName, AccountName, ProcessCommandLine

Step 3 — Count activity by device

summarize and count() turn raw process events into grouped investigation evidence.
count-powershell-devices.kql
  1. 1
  2. 2
  3. 3
  4. 4
  5. 5
  6. 6
DeviceProcessEvents
| where Timestamp > ago(24h)
| where FileName == "powershell.exe"
| summarize ProcessCount=count() by DeviceName
Raw events become patternsInstead of reading every process event, count the activity by device.
Volume becomes a clueA device with unusually high PowerShell activity may need closer review.
Context still mattersHigh volume may be legitimate administration, automation or something suspicious.

Step 4 — Rank the busiest devices

top helps defenders quickly surface the devices generating the most PowerShell activity.
top-powershell-devices.kql
  1. 1
  2. 2
  3. 3
  4. 4
  5. 5
  6. 6
  7. 7
DeviceProcessEvents
| where Timestamp > ago(24h)
| where FileName == "powershell.exe"
| summarize ProcessCount=count() by DeviceName
| top 10 by ProcessCount

Step 5 — Identify time spikes

bin() groups activity into time windows, helping defenders spot bursts of suspicious behaviour.
powershell-time-spikes.kql
  1. 1
  2. 2
  3. 3
  4. 4
  5. 5
  6. 6
  7. 7
DeviceProcessEvents
| where Timestamp > ago(24h)
| where FileName == "powershell.exe"
| summarize ProcessCount=count() by bin(Timestamp, 15m)
| order by Timestamp asc
Plain-English translation:

Count PowerShell events in 15-minute windows so suspicious bursts become easier to see.

Step 6 — Find unique devices involved

distinct helps defenders understand how widespread the activity is.
distinct-affected-devices.kql
  1. 1
  2. 2
  3. 3
  4. 4
  5. 5
  6. 6
DeviceProcessEvents
| where Timestamp > ago(24h)
| where FileName == "powershell.exe"
| distinct DeviceName

Putting it together

Once you understand each operator, you can combine them into an investigation workflow.
combined-investigation-workflow.kql
  1. 1
  2. 2
  3. 3
  4. 4
  5. 5
  6. 6
  7. 7
  8. 8
DeviceProcessEvents
| where Timestamp > ago(24h)
| where FileName == "powershell.exe"
| project Timestamp, DeviceName, AccountName, ProcessCommandLine
| summarize ProcessCount=count() by DeviceName
| top 10 by ProcessCount
Filter firstStart by limiting the dataset to the event type, time range or behaviour you care about.
Shape the evidenceUse project and summarize to make the results easier to read and investigate.
Find the pivotUse top, distinct and order by to decide where the investigation should go next.

Investigator mindset

KQL investigations are rarely solved with one operator.

Real defenders build investigations step by step.

Each operator removes noise, adds context or reveals the next useful pivot.

The logs already knew.

The defender simply learned how to ask better questions.
What should I filter first?Reduce the dataset to the activity relevant to the investigation.
What patterns stand out?Use summarize, top and bin() to identify spikes, noisy systems and unusual behaviour.
Where should I pivot next?Every suspicious value can become the next investigation lead.
🎓 Agent Foskett Academy — Foundations Complete
You now understand the core KQL investigation techniques used across Microsoft Defender XDR and Microsoft Sentinel investigations. The investigation never really ends. The logs are still waiting.
Return to Academy

What you learned

In this final foundation lesson, you learned how KQL operators work together inside a real Microsoft security investigation workflow.
KQL operators work togetherReal investigations combine multiple operators instead of relying on one command.
Each operator reveals contextFiltering, grouping, ranking and sorting all help defenders reduce noise and uncover suspicious activity.
Investigations evolve step by stepEvery result becomes the next pivot in the investigation workflow.

Continue your investigation

The Agent Foskett Academy foundation path is complete, but the learning continues through practical KQL hunting guides and real-world investigations.
Agent Foskett Academy Return to the full Academy learning path and review earlier KQL foundation lessons.
KQL Threat Hunting Guide Continue into practical Microsoft Defender XDR and Sentinel hunting examples.

Continue learning with KQL Threat Hunting, EmailEvents KQL Guide, DMARC Failures KQL, Microsoft Security and the GEMXIT Security Review.

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

GEMXIT PTY LTD  |  GEMXIT UK LTD  |  © GEMXIT 2026

Combining KQL Operators into Real Investigations

Agent Foskett Academy Lesson 11 teaches defenders how to combine where, project, summarize, count(), bin(), distinct, top and order by inside Microsoft Defender XDR and Microsoft Sentinel investigations.

Learn KQL Investigation Workflow for Microsoft Defender XDR

KQL investigation workflows help defenders reduce noise, group activity, identify spikes, rank high-volume behaviour and build practical investigation pivots across Microsoft security telemetry.

KQL Foundation Lesson for Microsoft Security Investigations

This final Agent Foskett Academy foundation lesson connects KQL operators into a real investigation workflow for Microsoft Defender XDR, Sentinel, DeviceProcessEvents, PowerShell activity, devices, accounts and process telemetry.