0.7 How to Learn KQL: the Mastery Ladder

Introduction

KQL is quick to start and slow to master. A first query takes minutes; writing queries that combine tables, unpack messy data, reuse logic, analyze time and relationships, and can be trusted when nobody checks them takes practice spread across many questions.

This course arranges that practice as a ladder of eight rungs, mapped onto its ten teaching modules, one or two modules each, where every rung uses everything below it. This sub shows the ladder by climbing it with a single, deliberately ordinary question: why do sign-ins fail?

You'll ask it at each rung and watch the answer deepen, with no attack details needed, from one number to a cause, an organization, a missing value, a named threshold, a day that does not fit, and finally a check that the answer adds up.

Then the sub sets out the habits that make the climb work, at any pace: predicting before running, changing one thing at a time, breaking queries on purpose and keeping the ones worth keeping.

The diagram is the course's structure, read from bottom left to top right. Each rung is a group of operators and the module that teaches them, and each is a way of asking a question more deeply than the rung below. Climbing it is the course.

The lowest rungs are reading a table, filtering it, shaping its columns and summarizing its rows, the work Sections 0.1 to 0.5 have already begun. They answer most everyday questions, and they are where every later query starts.

SigninLogs
| where ResultType != "0"
| count

338 failed sign-ins in the month: one filter and a count, the answer Section 0.1 found. Modules 1 and 2 teach this rung thoroughly, more thoroughly than its simplicity seems to need, the structure of a query, the types of its columns and every way of filtering, because everything above depends on it.

SigninLogs
| where ResultType != "0"
| summarize Failures = count() by ResultType
| top 3 by Failures desc

At the third rung the question deepens from how many to why. Grouping the failures by their result code says why they failed: wrong passwords most often, then sign-ins that needed a second factor, then locked accounts. Modules 3 and 4 teach shaping and summarizing, and most of the course's investigations begin with a summary like this one.

The first rungs are also where speed comes from. An analyst who can filter, shape and summarize without thinking about syntax spends their attention on the question, and the upper rungs depend on that fluency. Practice at the bottom is never wasted.

The second rung, shaping, is easy to underrate. Choosing which columns to keep, renaming them, computing new values and ordering rows is what turns a correct result into a readable one, and a result nobody can read answers nothing.

Summaries also teach a habit that runs through the whole course: reading a result as a distribution rather than a list. Three codes account for most failures; the rest are a long tail. Knowing what the top of a distribution looks like, and how quickly it tails off, is what lets an unusual entry stand out later.

The result codes themselves are worth learning gradually. Microsoft documents what each Entra code means, and the handful that appear most in a workspace become familiar quickly: 50126 a wrong username or password, 50076 a sign-in that needed a second factor, 50053 a locked account. Knowing them turns a summary into a story.

Some answers are not in any one table, and some are inside columns that need unpacking before they can be counted or compared. The fourth and fifth rungs handle both.

SigninLogs
| where ResultType != "0"
| join kind=leftouter (IdentityInfo
    | project UserPrincipalName = AccountUPN, Department) on UserPrincipalName
| summarize Failures = count() by Department
| top 4 by Failures desc

A join to the identity table, kept leftouter so that no failure is lost, turns account names into departments, and the failures turn out to be concentrated: Marketing has 113, more than the next three departments together. Module 5 teaches combining tables, with union and every kind of join, and with it the habit, from Section 10.4, of checking what a join does to the number of rows.

SigninLogs
| where ResultType != "0"
| summarize Failures = count() by OS = tostring(DeviceDetail.operatingSystem)
| top 3 by Failures desc

Reaching into the device details, a dynamic column, shows that 263 of the 338 failures carry no operating system at all. Module 6 teaches unpacking text and nested data, from parsing free text to expanding arrays, and nested data is often where an answer, or an absence, is hiding.

These two rungs are where the course's data starts to resemble real data most. Real security questions rarely live in one table, and real columns are often nested, inconsistent or partly empty. The operators of Modules 5 and 6 exist because data is messy, and learning them on a month that is realistically messy prepares for data that is more so.

A join's result also depends on the quality and coverage of the table it joins to. The identity table describes 97 accounts, and failures by accounts outside it need somewhere to go; a leftouter join keeps them with an empty department, which is honest, where an inner join would drop them, which is not. The Fix exercise later in this sub shows the difference in numbers.

The upper rungs make queries reusable and let them see patterns over time and across relationships, which is where many of the course's findings come from.

let MinFailures = 10;
SigninLogs
| where ResultType != "0"
| summarize Failures = count() by UserPrincipalName
| where Failures >= MinFailures
| count

A threshold named with let, at the top of the query, makes the query say what it means in its first line: accounts with at least ten failures, of which there are four. Module 7 teaches let, functions, lookups and external data, which turn one-off queries into tools.

The completed query below names the threshold.

Four accounts, from a query whose threshold can now be changed in one place. Try 5 and the count rises to 18; that is how thresholds are chosen, by watching what each value keeps.

SigninLogs
| where ResultType != "0"
| summarize Failures = count() by Day = startofday(TimeGenerated)
| summarize Days = count(), Median = percentile(Failures, 50), Busiest = max(Failures)

Over time, the question finds something no total could, by comparing each day with the others. A typical day, the median of thirty, has 9 failures; the busiest has 88, nearly ten times as many. Modules 8 and 9 teach time series, window functions and graphs, which find days, sequences and connections that stand out from everything around them, however much ordinary data happens to surround them in a month.

The upper rungs are where KQL becomes analysis rather than retrieval. Below them, queries fetch and count what is there; at them, queries compare each value with its neighbors, its history or its connections, and find what does not fit. That is the work that turns a month of records into findings, and it is where the course spends its last modules.

Each rung also changes what a question can be. At the bottom, questions are about rows: how many, which ones. Higher up, they are about relationships: which accounts share an address, which process started which. The ladder is as much about the questions it makes possible as about the operators it teaches.

Across the sub so far, one question about failed sign-ins was asked at seven rungs, and each rung added something the one below could not: a cause, an organization, an absence, a threshold, a day. The eighth rung, verification, comes next.

The record collects the habits that make learning KQL work, for the course and after it.

The first two rows are about running queries; the third about choosing them. The third row is the one this sub has been demonstrating. Asking a familiar question with new operators is how each rung is learned, because the answer to the familiar question is already known, so what the new operator adds is easy to see.

The first two rungs are reading and predicting; the last two are where learning sticks. The third rung is the one learners skip. Breaking a query on purpose, giving an operator the wrong type or a column that does not exist, shows what its errors look like while nothing depends on it, so that the same errors are familiar when they appear in real work.

The first two mistakes are about effort, the third about structure. The third mistake is common in courses organized by operator. This course is organized by question as much as by operator, and the exercises ask questions the subs did not, because using an operator unprompted is the test of having learned it.

Pace is worth a word. The rungs are not equally steep: the first four come quickly for many learners, the fifth and sixth take longer because their operators are more varied, and the seventh asks for a different way of thinking about data. Moving on with a rung half-learned is fine, because every later module uses the earlier ones and practice continues there.

There is no deadline in the course and no time estimate on any module. The right pace is the one at which predictions start coming out right more often than not.

Learning in a group helps too, where that is possible. Comparing two people's queries for the same question shows two ways to think about it, and explaining a query to someone else is the quickest way to find the part of it you do not fully understand yourself.

Notes help as well. A short note after each module, of the operators learned, the mistake made most often and one query worth keeping, takes a few minutes and makes the next module easier, because the habits from the last one are written down rather than half-remembered.

The top rung is a habit rather than a new operator, applied to every rung below: checking that the answer is right before anyone relies on it.

SigninLogs
| where ResultType != "0"
| join kind=leftouter (IdentityInfo
    | project UserPrincipalName = AccountUPN, Department) on UserPrincipalName
| summarize Failures = count() by Department
| summarize Groups = count(), Total = sum(Failures)

Summing the department groups, as Section 0.5 summed its applications, gives the check. The department groups add back up to 338, so the join neither dropped nor multiplied failures. Module 10 teaches this rung in depth: how queries run, what they cost, where the limits are, and how to check answers from queries borrowed, generated or written in a hurry.

The exercise below finds what an unchecked join loses.

338 again, against 324 with the inner join. With an inner join, failures by accounts the identity table does not list disappear; the check on the top rung catches it, and the fix on the fourth rung restores them.

Verification sits at the top because it is the rung that makes the others safe to use. Every rung below can produce a plausible wrong answer: a filter that matches too little, a summary grouped too finely, a join that drops or multiplies rows. Checking is what separates an answer from a guess that looks like one, and it is usually one more short query.

Verification also scales with the stakes. A query run once to satisfy curiosity needs a glance at its total; a query whose answer goes into a report needs its groups summed and its joins checked; a query that becomes a scheduled rule needs a known case, as Section 10.8 showed. Knowing which level a query needs is part of the top rung.

Every sub in the course is built the same way, this one included, and knowing the pattern helps use it. Each opens with an introduction and a diagram of its subject, as this one did, then teaches in sections that each carry something to run, read or keep.

Queries marked with a prediction ask for your answer before they show theirs. Each sub has three exercises, graded in the lab: one to complete, one to fix and one to write from a blank query. Each ends with a set of practice questions and a task for your own data.

Each module ends with a summary, a knowledge check of eight scenarios and a challenge set of questions the module did not answer directly. The challenge sets are the best measure of progress, better than any score on a knowledge check: they ask you to use the module's operators on the month's data without a sub to follow.

SigninLogs
| where ResultType != "0"
| summarize Failures = count() by ResultType
| where ResultType > 50000

Breaking a query deliberately is part of the method, and the safest time to do it is now. Comparing the result code, which is text, with a number by size fails with an error naming the type mismatch. Seeing that message now, on purpose, means recognizing it instantly when it appears by accident in real work.

The predictions matter more than they seem, and they cost nothing. Writing down what a query will return, before running it, turns every query into a small test of your understanding; a wrong prediction shows exactly where the understanding was wrong, which a correct answer read passively never would.

The exercises are graded on the result your query returns, its rows, its named columns and the key values in them, rather than on the text of the query, so any correct query that names its columns as asked passes.

That is deliberate: KQL usually offers several ways to answer a question, and the course rewards the answer rather than one particular phrasing of it. When your query differs from a sub's, compare the two; the difference is often instructive.

The knowledge checks at the end of each module test judgment rather than syntax. Each describes a situation, a slow query, a suspicious result, a borrowed detection, and asks what to do. They are the closest the course comes to the decisions real work presents.

Breaking queries on purpose is worth making systematic, a few minutes per new operator learned. For each new operator, try it on the wrong type, on a column that does not exist, on an empty table and on a value with different capitalization. Four short experiments show its edges, and the edges are where real mistakes happen.

Microsoft's KQL documentation, on Microsoft Learn, describes every operator and function in the language, with syntax, parameters and examples. The course teaches the operators security work uses most and how to combine them; the documentation is where the rest live, and reading an operator's page after learning it in a sub fills in the details a sub leaves out.

// Question: which departments have the most failed sign-ins, keeping every failure?
// Period: the sample month. Check: departments sum to all failures (338).
SigninLogs
| where ResultType != "0"
| join kind=leftouter (IdentityInfo
    | project UserPrincipalName = AccountUPN, Department) on UserPrincipalName
| summarize Failures = count() by Department
| top 3 by Failures desc

A query library is the other habit worth starting now, from the first module. The entry above shows the form, which the Project will ask for: two comment lines stating the question, the period and the check, then the query.

Every query that answers a question you might ask again, saved with a comment stating the question, the period and any check it passed, becomes a tool. The course's Project, at the end, asks for exactly that: a library of twenty queries on your own data, each checked, with one developed to the standard of a scheduled rule.

Learning is also faster with real data alongside the lab, where it is available. Running a sub's queries against your own workspace, adjusted for its tables and period as Section 10.6 describes, shows how the techniques behave at full scale and on data you know.

The library also changes how the course itself is used. A query written for an exercise and saved with its question becomes a reference for the next time the same kind of question comes up, in the course or at work, and building the library a sub at a time makes the Project at the end a matter of choosing and refining rather than starting from nothing.

Finally, a word on getting stuck. Every learner meets a query that will not work and an error that makes no sense.

The method that resolves almost all of them is the one this course repeats: remove lines until the query works, add them back one at a time, and run after each. The line that breaks it is the line to read about, in the sub and then in Microsoft's documentation.

The course's challenge sets are a natural place to start the library. Each asks questions that need a query of their own, and saving each answer with its question builds, module by module, a collection of tested queries covering every rung of the ladder.

The exercise asks the failure question at three rungs in one query: a filter, a join that keeps every failure, and a summary of failures and accounts per department.

The grader checks for Marketing's 113 among the results. If the departments sum to less than 338, the join is dropping rows; check that it is leftouter.

The query uses three rungs at once: a filter, a join and a summary. Read the accounts column as the next rung's question. A department with many failures and many accounts has a widespread problem, such as a password policy change; one with many failures and few accounts has something concentrated, which deserves a closer look. Telling those apart is a question for the modules ahead.

Practice

Read what an operator does, predict its output, break it on purpose, and use it on a question nobody asked you.

The next sub, Section 0.8, sets out what the course builds, module by module.