KQL for SC-200: Writing Detection Queries That Actually Work

If you strip away the product screens and the incident-response theory, the thing that quietly decides your result on the Microsoft SC-200 (Security Operations Analyst Associate) exam is whether you can read and write Kusto Query Language. KQL is the language behind Microsoft Sentinel analytics rules, hunting queries, and the advanced hunting experience in Microsoft Defender XDR, and the exam expects you to interpret it, spot what a query returns, and know which operator does what. The good news is that KQL is small at its core. Four operators — where, summarize, project, and join — carry most of the work, and once they click, the rest of the language is variations on a theme. This article teaches those four properly, with the detection examples the SC-200 actually leans on.

The one idea that makes KQL make sense

KQL is a pipeline. You start with a table, and every | (pipe) hands the rows from the previous step into the next, which transforms them. Read a query left to right and picture a shrinking, reshaping stream of rows. That model matters more than any single function, because it explains why order is everything: filter early and the operators downstream have less to chew on; filter late and you have done expensive work on rows you were about to throw away. Everything below is just "what each stage does to the stream."

where: shrink the stream first

The where operator keeps only the rows matching a condition, and it is the operator you should reach for first in almost every detection. On real Sentinel and Defender data, tables hold billions of rows, so a good query narrows the time window and the columns before doing anything clever.

That reads: take sign-in logs from the last hour, then keep only the failed sign-ins (a ResultType of 0 means success). Two things the exam likes to test here: ago() is relative time, so ago(1h) means "one hour before now," and string comparisons such as == are case-sensitive while =~ is case-insensitive. Choosing has over contains also matters — has matches whole indexed terms and is fast, while contains matches any substring and is slower, so has is the performant default.

summarize: turn many rows into an answer

Filtering gives you the relevant rows; summarize aggregates them into counts, sums, and groupings, which is where detections actually get their signal. This is the operator that turns "here are all the failed logins" into "here are the accounts with a suspicious number of failed logins."

The by clause is the heart of it: you are grouping rows that share a user and an IP, then counting how many fell into each group. The result is one row per user-and-IP combination with a FailedAttempts column, and the final where keeps only the noisy ones — a classic brute-force or password-spray pattern. Beyond count(), expect to recognise dcount() for distinct counts (how many different IPs did this account fail from?), min() and max() for first and last seen, and make_set() to collect the distinct values into a list. A frequent exam nuance: any column not named in an aggregate function or in the by clause disappears from the output, because summarize only keeps what it grouped or computed. That is why your other columns "vanish." Building this kind of pattern recognition under pressure is exactly what timed drilling does, and working through practice questions for the SC-200 until you can predict a query's output at a glance is the single highest-yield thing you can do.

project: control the columns

Where where chooses rows, project chooses columns. Use it to keep only the fields an analyst needs, to reorder them, or to rename and compute new ones. It is the cosmetic-but-important step that makes a detection readable in an incident.

That surfaces encoded PowerShell command lines — a common obfuscation technique — and trims the output to four meaningful columns. Two cousins are worth knowing cold: project-away drops named columns and keeps the rest, and extend adds a new calculated column without discarding anything. The exam likes to contrast project (an explicit keep-list) with extend (add-only), so if a question asks how to add a computed field while preserving every existing one, the answer is extend, not project.

join: correlate two tables

The hardest of the four, and the one most people rush, is join. It correlates rows from two tables on a shared key — the mechanism behind detections such as "an account that failed to sign in and then succeeded from the same IP," or "a risky user who also ran a suspicious process." You put the left table first, pipe into join, and specify the kind and the key.

The detail examiners love is kind. An inner join returns only rows with a match in both tables; a leftouter join keeps every left-table row and fills nulls where the right side has no match; leftanti is the sneaky-useful one, returning left rows that have no match on the right — perfect for "show me accounts that were created but never signed in." Getting the join kind wrong is the most common way a plausible-looking query returns the wrong rows, so when a question hinges on "which accounts are missing from the second table," think leftanti immediately.

The mistakes that cost marks

A handful of errors show up again and again. Filtering after aggregating instead of before wastes performance and sometimes changes results. Forgetting that summarize drops ungrouped columns leads people to expect fields that are not there. Reaching for contains when has would be correct and faster. Mixing up case-sensitive == with case-insensitive =~ on usernames and hostnames. And treating every join as an inner join when the scenario clearly wants outer or anti behaviour. None of these are conceptually hard, but under a 100-plus-minute timer they are easy to fumble, which is why exposure to worked queries beats re-reading operator documentation.

Turning KQL fluency into a passing score

Reading about pipelines is not the same as recognising, in the moment, that a query with a summarize ... by followed by a where is a threshold detection. That fluency only comes from volume, and from reviewing the ones you get wrong. On ExamStudyApp, our adaptive practice notices which operators trip you up and keeps serving more KQL-heavy items until join kinds and aggregation behaviour feel automatic, while the full timed SC-200 exam simulations reproduce the real question mix and pass threshold so you experience the pacing before exam day. The readiness tracking then tells you when your scores across the exam's domains are consistently clearing the bar, and reviewing every missed question with its explanation closes the gaps that pure study leaves behind. When you can look at an unfamiliar Kusto query and describe its output without running it, you are ready — and the Microsoft Security Operations Analyst practice set is built to get you there.