Hunting with KQL in Defender XDR

What it is

Advanced hunting in the Microsoft Defender portal lets you search raw security data from endpoints, email, identities and cloud apps with Kusto Query Language (KQL). Instead of waiting for an alert, you form a hypothesis, such as "an attacker is running encoded PowerShell", and test it with a query.

Two skills decide how well you hunt: knowing which table holds the data you need, and writing KQL that filters, summarises and joins it.

How it works

Choosing the right table

Each table describes one kind of event. Picking the right one is half the work.

Remember the distinctions that exam questions test: DeviceProcessEvents records process creation, DeviceNetworkEvents records outbound and inbound connections of processes, DeviceNetworkInfo describes adapters and configuration, DeviceLogonEvents covers sign ins, and DeviceInfo holds per device details such as the operating system. In DeviceEvents, the ActionType column tells you the kind of event, for example AntivirusDetection, UsbDriveMounted, ExploitGuardNetworkProtectionBlocked or OpenProcessApiCall.

Core KQL operators

A query starts with a table, then pipes results through operators.

=~ is a case insensitive equals, and == is case sensitive.

Example queries

Find devices that launched PowerShell with an encoded command:

DeviceProcessEvents
| where Timestamp > ago(7d)
| where FileName =~ "powershell.exe" and ProcessCommandLine has_any ("-enc", "-encodedcommand")
| project Timestamp, DeviceName, AccountName, ProcessCommandLine
| order by Timestamp desc

Find devices with many failed logons:

DeviceLogonEvents
| where Timestamp > ago(1d) and ActionType == "LogonFailed"
| summarize Failures = count() by DeviceName, AccountName
| where Failures > 20

Find devices that saw more than 100 distinct file hashes in a day. Here dcount(SHA256) counts distinct hashes for each device and the final where keeps only the busy ones:

DeviceFileEvents
| where Timestamp > ago(1d)
| summarize FileCount = dcount(SHA256) by DeviceName
| where FileCount > 100

Find devices that ran a process and then wrote to a Run registry key within ten minutes, by joining two tables on DeviceId and filtering the time gap:

DeviceProcessEvents
| where FileName =~ "powershell.exe"
| join kind=inner (
    DeviceRegistryEvents
    | where RegistryKey has @"CurrentVersion\Run" and ActionType == "RegistryValueSet"
    | project DeviceId, RegistryTime = Timestamp
  ) on DeviceId
| where datetime_diff("minute", RegistryTime, Timestamp) between (0 .. 10)

Keep the latest record per device with arg_max, which returns one row for each device with its most recent record:

DeviceInfo
| where Timestamp > ago(7d)
| summarize arg_max(Timestamp, OSPlatform, DeviceName) by DeviceId
| where OSPlatform == "Windows10"

Because the filter comes after arg_max, it applies to each device's latest record only.

Hunting for beaconing

Malware that calls home often does so at regular intervals. A hunt for beaconing measures the time between consecutive connections for each source and destination pair, and ranks by low variance in those gaps. Counting ports finds scans, byte totals find heavy transfers, and busiest hosts find popular services, so none of them reveal periodic calls.

Limits and good practice

• Advanced hunting keeps 30 days of data. For older activity use Purview Audit or, with Sentinel, the longer retention tiers.
• Results are limited, so filter early with a time range and where before heavy operators.
• Use project to keep only the columns you need.
• Test on a short time range, then widen.
• Save useful queries to the shared queries, so the team can reuse them.

From a query to a detection

When a hunting query reliably finds something bad, select Create detection rule. The query must return Timestamp, ReportId and entity columns. The rule then runs on a schedule and raises alerts, as covered in the custom detections page. A query that joins two tables cannot run as continuous (NRT) frequency.

Choosing the table in a scenario

A worked scenario

A threat report describes an attacker who uses encoded PowerShell and writes a Run key for persistence. A hunter starts in DeviceProcessEvents, filters for powershell.exe with an encoded argument, and finds three devices. She joins DeviceProcessEvents to DeviceRegistryEvents on DeviceId, filters for a Run key write within ten minutes, and keeps two devices. After isolating them, she saves the query and creates a detection rule that runs every hour, because the join rules out continuous frequency.