Microsoft SC-500: KQL for Security Investigations
Kusto Query Language is useful when every operator answers a specific security question. Reliable Microsoft Defender and Sentinel investigations depend on table selection, time context, entity keys, bounded joins and reproducible evidence—not complex syntax for its own sake. A high-quality query uses the correct table, entity key and time window, and distinguishes a missing observation from proof that an event never happened. Filtering, joins and summaries should preserve the evidence needed for incident decisions rather than merely produce concise charts.
On this page
- Form a hypothesis before choosing a KQL table
- Filter early while preserving enough investigative context
- Use project and extend to produce readable evidence
- Summarize patterns and then return to individual events
- Join entities only through justified relationships
- Handle dynamic data and reusable functions cautiously
- Interpret current retention behavior
- Control query costs and resources
- Turn verified queries into reviewed detection logic
Form a hypothesis before choosing a KQL table
A useful query asks a falsifiable question such as whether a specific account signed in before a suspicious cloud resource operation or whether multiple devices launched the same unusual process. Start with the source that directly records the event. Defender XDR hunting schemas, Microsoft Sentinel data tables and custom application logs differ in fields and retention, so there is no universal first table for every investigation. Query design should name the expected entity, time window and evidence pattern before any join is written.
For a synthetic incident, write one sentence: ‘Did this account access the Key Vault within thirty minutes after the risky sign-in?’ List the identifier used by each table and where event time is stored. If the target resource never emits the necessary data, no KQL expression can fill the gap. Resolve the missing source first or mark the conclusion unsupported. This discipline prevents sophisticated-looking queries from producing plausible but irrelevant output.
Filter early while preserving enough investigative context
The where operator constrains time, identity, resource or event type so the query can be interpreted and run efficiently. Use a bounded interval appropriate to the hypothesis, then widen it when evidence suggests earlier preparation or later persistence. Prefer stable identifiers to ambiguous display names where supported. A user principal name can change; a device name can be reused; an IP address can represent many clients behind a proxy. Good filtering balances performance with the risk of excluding meaningful adjacent events.
Run a narrow query around a known simulated event and verify it returns the expected row. Expand the window to find setup and follow-up actions. Test what happens if the account casing or identifier format differs from the original filter. Keep the exact UTC boundaries in the incident record to avoid later confusion when analysts discuss local time or daylight saving. A ‘no rows’ result only means nothing matched those filters in the available data, not that the behavior never occurred.
Use project and extend to produce readable evidence
project selects useful columns, while extend derives fields needed for analysis without making raw evidence disappear. The goal is an event table another analyst can understand: timestamp, principal, device or resource, action, outcome and source. Keep original identifiers available when exporting findings so a later reviewer can connect transformed output to the underlying record. Renaming a field can improve readability but should not hide ambiguity about what the original schema actually means.
Take a verbose device-event table and project only the fields necessary to explain an unusual process execution. Add one derived classification if it supports the hypothesis, then compare the resulting row to the raw event. If a nested JSON property is sometimes missing, handle that explicitly instead of creating a misleading default value. A concise result is better for handoff and incident review than a screenshot of every column in the hunting portal.
Summarize patterns and then return to individual events
The summarize operator can group events by account, device, domain or action and calculate counts and time ranges. This helps identify unusual concentration, broad spread or repeated failures. Aggregation is not evidence that each event has the same malicious cause, however. A spike in failed sign-ins may reflect an expired app credential, password-spray attempts or a policy rollout. The distribution becomes useful only when analysts inspect representative raw events and compare them with a normal baseline.
For a test dataset, summarize failed sign-ins by principal and hour, then drill into one outlier. Check location, client application, authentication method and associated policy result. Compare with expected automated account activity before escalating. If time bins conceal a short attack burst, narrow the interval; if the result is noisy because one high-volume service dominates, separate that service deliberately rather than deleting rows that contradict the original theory.
Join entities only through justified relationships
join is powerful and easy to misuse. An account may appear as a UPN, object ID or SID, while devices have separate IDs and names. Joining on a nonunique display name or public IP can generate many-to-many matches that exaggerate activity. Before a join, inspect uniqueness and null behavior on both sides and choose a key that represents the relationship the hypothesis requires. Consider time correlation separately; two events sharing an identity days apart do not necessarily form one incident.
Use a small artificial dataset with two users sharing a network address to demonstrate why an IP-only join is unreliable. Compare it with a join on the identity or device identifier and a bounded time window. Count input and result rows so unexpected multiplicative growth is detected. Save an explanation of what the join proves and what it cannot prove. A reviewable relationship is more valuable than combining every available security table into one gigantic output.
Consider a sign-in table that contains several attempts per user and a device-events table with many process records per device. Joining only on a display name can multiply the result, mix different identities with similar labels and make a single event appear to be an attack campaign. Use stable identifiers available in the actual schemas, constrain both inputs to a justified time window and inspect the cardinality before accepting a many-to-many relationship. If one table uses a UPN and another uses an object ID, first verify the translation instead of assuming the strings are interchangeable.
A practical query review captures row counts before and after joining, at least two sampled matching records and one known nonmatching record. When a correlation is uncertain, annotate it as an investigative lead rather than conclusive evidence. That distinction becomes critical if the resulting query is later converted into a detection rule or automatic containment action, because a false join can otherwise target the wrong user or device.
Handle dynamic data and reusable functions cautiously
Security telemetry often contains JSON payloads, arrays and nested details. KQL parsing and operators such as mv-expand can extract those fields, but expansion changes row counts and can make one event look like many. let statements and shared functions can improve readability when analysts use the same trusted logic repeatedly. A reusable function still needs schema ownership and a review date because Microsoft Defender and Sentinel sources evolve. Copied community queries may contain assumptions about columns or retention that do not hold in the current tenant.
Build a small function that normalizes a known identifier used in two supported tables. Test records with missing fields, unexpected case and more than one nested value. Document input table requirements and output columns. If the function is used for a detection rather than exploratory hunting, test that its output remains stable under actual ingestion frequency and table volume. Query reuse reduces effort only when its behavior is explainable and regression-tested.
Use current retention semantics before making a negative claim
The Microsoft Defender hunting experience and Sentinel connected data may share one portal but not one historical window. Sentinel Analytics-tier tables support high-performance hunting and alerting within their configured retention, while Data Lake-tier storage is designed for lower-cost historical access with different query and feature behavior. An analyst searching only the default interactive window may miss activity that is available in a longer-retention tier or archived elsewhere. Conversely, a source absent from the relevant workspace cannot be recovered by changing the time filter alone.
Before closing an investigative hypothesis, record source tables, last-ingested timestamps, configured retention, query bounds and any connector gaps. If the required data is older than the interactive window, choose the supported long-term query or restoration workflow and note its cost and latency. Distinguish ‘not observed in available events’ from ‘confirmed not to have occurred’. This prevents the unified portal from creating false confidence about coverage across services and time ranges.
Control cost and query resource consumption in large workspaces
KQL efficiency matters when a query runs repeatedly over large cloud logs. Expensive unconstrained joins, broad wildcard searches or unnecessary dynamic expansion can slow investigation and generate avoidable processing cost. Set a time window, project relevant fields and filter as close to the source as feasible, then inspect result volume before introducing a larger join. Do not prematurely optimize a small accurate query, but do distinguish reproducibility from a one-time expensive search that only its author can afford to rerun.
For a synthetic dataset, compare the row count and elapsed execution of a broad query with a semantically equivalent scoped query. Verify the answer remains the same for known test events, including null identifiers and unusual timestamps. Record both correctness and approximate resource use where the platform exposes it. A hunting library should favor understandable patterns whose cost is proportional to the question, especially when an analyst might run them during a time-sensitive incident.
Turn verified queries into reviewed detection logic
A query that helped investigate one incident may be a useful candidate for a Sentinel analytics rule or Defender custom detection, but exploratory assumptions can make a scheduled rule noisy or unsafe. Define expected behavior, frequency, threshold, severity, owner, entity mapping and response actions. Test known benign and simulated malicious events and inspect false positives. Automated response should only follow when the detection reliably identifies the condition and the action has appropriate approval and rollback controls.
Store the exact final KQL, parameters, schema expectations, sample test results and date in a versioned library. Review when a connector, retention setting or product schema changes. During a future incident another analyst should be able to reproduce the query and understand its limitations without relying on the original author. The best KQL is not the most elaborate expression; it is the shortest trustworthy analysis that changes a security decision.