Microsoft SC-200: KQL for Threat Hunting and Detection

KQL is most useful in security operations when it answers a question, not when it demonstrates syntax. An SC-200 analyst uses Kusto Query Language to find evidence, correlate activity, build hunting hypotheses, support investigations, and turn validated logic into detections. On the Microsoft SC-200, that means reasoning from a security question to a query, then judging whether the result is strong enough to support investigation or detection.

As of October 5, 2026, SC-200 still follows the July 28 English skills outline, with another update staged for October 21. Microsoft continues to position KQL as a core tool for hunting and detection in Sentinel. The stable skill is not memorizing every operator. It is knowing how to move from a security question to trustworthy query results.

Begin with the question and expected evidence

Before opening a query window, state what you are trying to prove. “Find suspicious PowerShell” is vague. “Find PowerShell launched by Office processes on endpoints where the user has no history of administrative scripting” is a testable question. That wording suggests endpoint telemetry, process-parent fields, user context, and a comparison baseline.

This question-first habit keeps queries smaller and more interpretable. It also makes it easier to notice when the available logs cannot answer the question. If the required field or table is missing, adding operators will not create evidence. Change the question, collect better telemetry, or document the limitation.

Choose the table from the security event, not from habit

Sentinel workspaces can contain many tables with overlapping-looking fields. The correct table depends on where the event originated, how the connector normalizes it, and whether the data is in the analytics tier or another storage path. Start by confirming recent records, time fields, key identifiers, and the columns needed to join with other evidence.

Table selection errors create silent false negatives. A query can run perfectly against an empty or irrelevant table. Validate the data before composing complex logic. This aligns with the evidence-first approach in SC-200 evidence-first preparation: follow what the environment actually recorded instead of assuming the alert tells the whole story.

Control the time range deliberately

Security investigations are temporal. A five-minute window can miss precursor activity; a 30-day window can bury a simple question under volume. Decide whether the task is real-time triage, an incident timeline, baseline creation, or a historical hunt, then select a time range that fits the question and the retention available.

Be explicit when joining sources with different ingestion patterns. One data set may arrive quickly while another is delayed or batched. Time-range logic that ignores ingestion behavior can produce misleading “no evidence found” conclusions. For scheduled detections, the same thinking affects lookback and execution frequency.

Filter early and keep only useful columns

where is one of the most valuable tools because it reduces the working set to relevant rows. project and extend then shape the fields needed for analysis. Filtering early improves readability and often improves performance because later operators process less data.

A security query should expose the fields an analyst needs to reason: time, actor, host, source and destination, action, result, and relevant identifiers. Keeping dozens of unused columns can hide mistakes. Removing too much context can make the final table impossible to validate. Shape output for the investigation, not for visual neatness alone.

Use summarize to turn events into behavior

Individual events are often weak signals. summarize can convert rows into counts, distinct values, rates, or grouped patterns that reveal behavior: failed logins per account, rare process names per host, destinations per user, or events per time bucket. The grouping fields define the meaning of the result.

Aggregation can also hide important detail. If you summarize only by user, you may miss that failures come from several countries or devices. If you group too granularly, the pattern disappears into one-row groups. Validate the raw events behind an interesting aggregate before escalating a finding.

Join and union solve different correlation problems

union combines rows from tables that represent similar evidence, while join correlates rows based on matching keys. Both are common in security work, but joins are especially easy to misuse. Non-unique keys can multiply rows, timestamp mismatch can prevent useful matches, and empty fields can quietly remove evidence.

Before joining, inspect the key on both sides. Ask whether one row should match one row, one-to-many, or many-to-many. Reduce or summarize each side when necessary. After the join, compare row counts and spot-check known entities. A query that becomes dramatically larger may be telling you the correlation key is not as unique as assumed.

Let statements make complex hunts explainable

let statements let you name intermediate sets, constants, watchlists, or reusable pieces of logic. Used well, they turn a long query into a sequence of understandable steps: define a time window, identify privileged users, collect suspicious events, correlate sign-ins, and produce final evidence.

Used poorly, they can hide logic behind vague variable names. Name variables after the security concept they represent, and keep their boundaries testable. Run intermediate pieces independently during troubleshooting. Reusability is valuable only when the analyst can still explain what each part contributes to the conclusion.

Parsing is a last-mile task, not a substitute for good data

Security logs often contain JSON, dynamic objects, embedded strings, or vendor-specific fields. KQL can parse and extract the needed values, but heavy parsing everywhere may indicate that ingestion or normalization should be improved. A hunt can tolerate some ad hoc parsing; a long-lived detection deserves a more stable data contract.

When you parse, validate failure cases. Nulls, format changes, localization, and inconsistent delimiters can make an extraction work on sample rows while failing silently later. Keep an eye on how many records produce missing parsed values, especially before using those fields for entity mapping or joins.

Query efficiency matters when evidence scales

Performance is not only a cost issue. Slow queries discourage analysts from iterating and can make scheduled detections unreliable. Narrow the time range, filter early, avoid unnecessary wildcard operations, limit expensive joins, and summarize before correlation when the analytical question allows it.

Efficiency should not come at the cost of correctness. A shortcut that excludes relevant records or collapses necessary detail is not optimization. Measure query behavior on realistic data volumes and keep the reasoning readable enough for another analyst to review. The Microsoft security certifications show where SC-200 sits among adjacent security roles, while practical analyst judgment remains central.

A hunt is exploratory; a detection runs repeatedly and creates operational work. Before promoting logic into an analytics rule, test normal activity, known benign exceptions, historical attack examples if available, time behavior, result volume, and entity output. Decide what threshold or scheduling behavior turns the query into an actionable signal.

Then monitor the rule after deployment. If analysts constantly close alerts as benign, the logic needs context or tuning. If the rule is silent, confirm the data path before celebrating. KQL becomes detection engineering only when the query survives contact with production behavior and still produces evidence that supports a defensible response.

A hunting query should begin with the kind of event that would prove or disprove the hypothesis. Authentication questions need identity and sign-in evidence. Endpoint behavior needs process, file, registry, or device telemetry. Cloud-resource activity needs the relevant control-plane or workload logs. Starting from a familiar table and hoping the right evidence is there is a common way to create blind spots.

Inspect the schema and a small sample before building a large query. Confirm field meaning, timestamp semantics, identity normalization, nullable values, and whether the table has the time coverage you need. This early check prevents hours of tuning against a field that is sparse or interpreted incorrectly.

A join that returns results is not automatically correct. Many-to-many relationships can multiply rows, mismatched normalization can drop important matches, and large joins can make a query expensive enough to be impractical for repeated use. Before joining, understand the cardinality you expect and what key truly represents the same entity across both data sets.

Validate row counts before and after the join, inspect unmatched samples, and normalize case or identifiers only when the semantics justify it. If a smaller lookup, summarize, or pre-filter can answer the question, use it. Query efficiency is part of operational reliability when a hunt becomes a recurring detection.

A hunting query often starts broad because the analyst is exploring. A detection rule needs more discipline: bounded time, predictable fields, stable thresholds, useful entities, and known behavior under missing or delayed data. Remove exploratory columns and temporary filters before treating the query as production content.

Document what evidence caused the hunt to become a detection and what assumptions remain. That context helps future analysts understand why the logic exists and when it should be revisited. Reusable KQL is not only syntax reuse; it is preserved investigative reasoning.

Before promoting a query into a shared hunt or rule, save one or two representative results and the expected result count for a known time window. That small test fixture gives future editors a way to detect accidental semantic changes when fields, parsers, or filters are modified.

KQL performance matters because an investigation query can become a production detection or a repeated hunting workflow. Filter early on selective time and event conditions, project only fields that remain useful, summarize intentionally, and avoid expensive joins when a simpler lookup or pre-normalized field answers the question. Before optimizing, confirm that the result set is still semantically correct; a fast query that drops relevant events is not an improvement. When a query will run frequently, test it across representative data volumes and document any custom functions or parsers it depends on so later tuning does not break the analytical meaning.