KQL primer for Defender XDR — Solving Microsoft 365

Kusto Query Language (KQL) is the query language used in Microsoft Defender XDR's advanced hunting, Microsoft Sentinel, Azure Monitor, and Log Analytics. Knowing a small KQL subset unlocks dramatically more capability in the Microsoft security stack — and the basics are quick to pick up.

The basic shape

A KQL query starts with a table and pipes data through operators:

DeviceProcessEvents
| where Timestamp > ago(1d)
| where FileName == "powershell.exe"
| project Timestamp, DeviceName, AccountName, ProcessCommandLine
| order by Timestamp desc
| take 100

Read top to bottom: take the DeviceProcessEvents table, filter to the last day, filter to PowerShell launches, project four columns, sort, return 100 rows.

Key operators

A handful of operators cover most queries:

where — filter rows by a condition.
project — pick specific columns; rename them.
extend — add a new computed column.
summarize — group and aggregate.
join — combine with another table on a key.
order by — sort.
take / top — limit results.
distinct — deduplicate.
count — count rows.

Time filtering

Almost every query filters on time. Common patterns:

| where Timestamp > ago(1h)        // last hour
| where Timestamp > ago(7d)        // last 7 days
| where Timestamp between (datetime(2026-05-01) .. datetime(2026-05-05))

ago() is your friend. Tighter time filters are dramatically faster on huge log tables.

Counting and aggregation

DeviceLogonEvents
| where ActionType == "LogonFailed"
| where Timestamp > ago(1d)
| summarize count() by AccountName
| order by count_ desc

The summarize operator is where most security analytics happens — group by an attribute, count or otherwise aggregate, find anomalies.

String operations

| where ProcessCommandLine has "Invoke-Expression"   // case-insensitive contains
| where ProcessCommandLine contains "iex"            // also contains
| where ProcessCommandLine matches regex @"\bIEX\b"  // regex
| where FileName startswith "powershell"

has is the operator to use most often — case-insensitive whole-word match, indexed for speed.

Joining tables

A common pattern — start from one table, join another for richer context:

DeviceProcessEvents
| where FileName == "rundll32.exe"
| where Timestamp > ago(1d)
| join DeviceNetworkEvents on DeviceId, Timestamp

Defender XDR tables to know

A few of the most useful tables in Defender XDR advanced hunting:

DeviceProcessEvents — every process launch on managed endpoints.
DeviceNetworkEvents — outbound network connections.
DeviceFileEvents — file create/modify/delete events.
DeviceLogonEvents — sign-in attempts on endpoints.
EmailEvents, EmailUrlInfo, EmailAttachmentInfo — Defender for Office 365 email signals.
IdentityLogonEvents — Entra ID and on-prem AD sign-in events.
IdentityDirectoryEvents — directory changes (group membership, permission grants).
AlertEvidence — entities involved in alerts.

In Sentinel, table names vary (often SecurityEvent, Syslog, CommonSecurityLog).

Hunting templates

The fastest way to learn KQL is to read other people's queries:

Microsoft Defender XDR community-driven queries on GitHub.
Microsoft Sentinel content hub ships hundreds of detection rules — read them.
MITRE ATT&CK-mapped hunts show how analysts encode specific tactics.

Practical advice

Filter early — where clauses near the start make queries faster.
Always include a time filter — unbounded queries are slow and expensive.
project only what you need — narrow result sets are faster to manipulate.
Test on small time windows before running across weeks.
Save useful queries as functions or templates.

KQL is one of the highest-leverage skills for anyone doing security work in Microsoft 365. The basics take an afternoon; mastery is a journey worth taking.