KQL for reasoning about events | SC-200: investigation and security operations response

Concept and mechanism

A query should answer a defined question using appropriate tables, periods, and fields. Confirm the available schema instead of copying table names from another product. where filters rows; project selects columns; summarize aggregates by keys; bin creates explicit grouping intervals. Order matters: after removing TimeGenerated, you cannot use it in a later filter without retaining it or reorganizing the query. Aggregation changes the result unit. One row per account is not one row per attempt. Explain that unit in reports and rules using the result to decide thresholds or priorities.

Guided application

In a fictional exercise, group failures by AccountId and ten-minute intervals to observe time concentration. When joining tables, declare the intended join flavor: default innerunique is not equivalent to inner in every case. An inner join with two left and three right rows for one key can produce six pairs. That multiplicity does not prove six original events. To find inventory devices without recent telemetry, leftanti identifies missing matches but does not prove devices are offline. Filters, collection failures, or differing identifiers can explain gaps. Test relationships with small known datasets before applying conclusions to thousands of rows.

IN PRACTICE

AuthEvents | summarize count by AccountId, bin(TimeGenerated, 10m) uses a fictional table and requires prior failure filtering.

Common pitfalls

Copied table name without schema; fields removed too early; join as addition; missing match as unavailability.

Related topics: Hunting, coverage, and continuity · SOC platform: data and protection

Take this idea with you

Confirm what each row represents before counting or acting.

Create account

Reference: Advanced hunting KQL · SC-200 objectives effective 2026-07-28; Microsoft product documentation reviewed 2026-10-01; 2026-10-21 English update compared separately