0.1 What KQL Is and Where It Runs

Introduction

Security work is mostly questions. Who signed in from that address? Which process started the encryptor? What did this account do after its password was guessed? The answers are in records that security products collect by the million, and the Kusto Query Language, KQL, is how Microsoft's security products let you ask questions of those records.

This sub introduces the language before any of its details, so that the details have somewhere to fit: what a query looks like, what it returns, how to read one, and where queries run.

You'll run a few short queries against the course's sample month, each of which you can change and rerun freely, see why every answer is a table, and meet the first of KQL's habits that catch newcomers, exact names. By the end, the shape of every query in the rest of the course will be familiar, even where its operators are new.

The diagram is the whole idea in one line, and every later sub adds detail to it rather than changing it: a question becomes a query, and the query returns a table of rows. Below it, the same language runs in several Microsoft products, and what you learn here works in each of them.

Every KQL query in this course starts with a table name and continues with steps, and the pattern never changes, each written after a pipe character, |. The table supplies rows; each step takes the rows from the step before it, does one thing to them, and passes the result on. The last step's output is the answer.

SigninLogs
| take 3
| project TimeGenerated, UserPrincipalName, IPAddress, ResultType

A table can hold millions of rows, so the first thing to do with an unfamiliar one is look at a handful.

The simplest useful query takes a few rows and picks a few columns, so you can see what a table holds. Each row of SigninLogs, the table of interactive sign-ins to Microsoft Entra, is one sign-in: when it happened, which account, which address it came from, and a result code where 0 means success.

Every security table in this course has the same character, one row per event, with columns describing it, and a time column saying when it happened.

The result code deserves a word, because it appears throughout the course. A sign-in either succeeds, with code 0, or fails, with a code that says why: a wrong password, a blocked location, a missing second factor. The codes are stored as text, which is why the queries compare them with quoted values.

Reading a query means reading its steps in order, top to bottom. take 3 keeps three rows, any three at all; project keeps four columns and drops the rest. The order of steps matters, as later modules show at length, but at this stage the habit to form is simply that each line does one thing to the rows above it.

The pipe is the key to reading KQL, and it is the one symbol worth understanding before learning any operator at all. It means: take everything produced so far and hand it to the next step.

A query is therefore a sequence of small transformations, and each one can be understood on its own. Three short steps that each do something obvious are easier to read, check and change than one long step that does everything at once, and KQL is designed to be written the first way.

Line breaks are only for the reader. The engine would accept the whole query on one line, but by convention each step starts on a new line with its pipe, and every query in this course is laid out that way, so that the steps can be read down the page.

A short glossary helps from here on. A table is a named collection of rows. A row is one record, here one event. A column is one named value that every row carries, such as the time or the account.

An operator is a step such as where, take or summarize, and an expression is a calculation inside a step, such as ResultType != "0". The rest of the course uses these words consistently.

Whatever a query does, its answer is a table: rows and columns. That consistency is what makes KQL easy to build on, because every step can treat whatever came before it the same way. A list of events is a table; a count is a table with one row and one column; a summary by account is a table with a row per account.

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

The next query has four steps and asks a real question, the kind an analyst might ask on any morning: which accounts failed to sign in most often. Read it a line at a time.

The first step keeps failures, the second counts them per account, and the third keeps the top three. Each step's output is the next step's input, so the count only ever sees failures, and top only ever sees the counts.

The answer is a three-row table, ranked from most failures down: r.scott with 89, d.foster with 14, d.thompson with 10. Why one account failed 89 times is a question for later modules; for now it is enough that four short lines asked the question and got an answer.

print Answer = 6 * 7

A query does not even need a table, although almost every query in this course has one. print returns one row with whatever values it is given, named as you choose, which is useful for trying out an expression before using it on real data. 6 times 7 comes back as 42, in a column called Answer, because even this answer is a table.

Because every answer is a table, answers can be fed into further steps, without any conversion in between. The three-row result above could be sorted differently, joined to another table that holds each account's department, or counted again. That composability is what lets long investigations be built from short, simple steps, and it is the reason the course spends its first modules on single steps before combining them.

The column names in an answer come from the query. Failures was named by the summarize step; Answer by print. Naming results clearly, rather than accepting a default, makes a table readable to whoever receives it, including yourself a week later.

KQL queries read data; they do not change it. Azure Data Explorer also has management commands, which begin with a dot and can create or change tables; they are a separate thing from queries, and this course writes only queries.

Adding a filter, removing a column or counting rows changes what the query returns, never what is stored. That makes the language safe to experiment with: a wrong query returns a wrong answer, and the next query starts from the same data as before.

SigninLogs
| count

The sample month holds 8,203 sign-ins, about a month of a company of 810 people signing in to its cloud services. Counting a table before asking anything else of it is a good habit, and it costs one line, because the size says what kind of questions are practical and what an answer's numbers should be compared with.

The completed query below counts the table.

SigninLogs
| where ResultType != "0"
| count

Adding one step, a filter on failures, gives 338, about four in every hundred. The table still holds 8,203 rows; the query asked a narrower question of it, and removing the step would ask the wider one again. Every query in the course works this way, and every step can be added, removed or reordered to see what it does.

Read-only also means that the data in the practice lab is the same for everyone and every time, however many queries have been run against it. The numbers in this course, 8,203 sign-ins, 338 failures, can be reproduced exactly by anyone who runs the same query, which is what makes them useful for learning and checking.

In a real workspace, the data keeps arriving, so the same query run tomorrow covers different rows unless it names a fixed period. The lab's month never changes, which is one of the reasons it suits a course.

KQL is exact about names, and this is where most first errors come from. Microsoft's documentation describes it as case-sensitive for table names, column names, operators and functions. SigninLogs and signinlogs are different names, and only one of them is a table here.

signinlogs
| count

The lower-case version fails with an error that names the problem, here an unknown table. That is a very common first mistake in KQL, and the easiest to fix: copy names exactly as the table's schema writes them. Section 0.2 shows how to list a table's columns, so you never need to guess a name.

The exercise below fixes a name.

338 failed sign-ins. Operators such as where, summarize and count are written in lower case throughout this course, as KQL expects, and table and column names are written exactly as the schema has them.

Errors in KQL are worth reading rather than fearing. They usually name what is wrong: an unknown table, a column the engine cannot find, a step that expected something else. Fixing one error at a time, rerunning after each fix, is faster than rewriting a query from scratch.

Error messages also show where in the query the problem is, and in a long query that is half the work. Reading the message before reading the query usually points straight at the line to change.

Across the sub, a handful of short queries, none longer than four steps, showed the language's shape. A table name, then steps joined by pipes. An answer that is always a table. Queries that read and never change. Names that must match exactly.

The record collects KQL in brief, the six facts every later sub relies on.

The last row is the next section's subject. Learning KQL once is enough for every product in it, Sentinel, Defender and the rest, because the language is the same.

The first two rungs are how to start on any query; the last two are what to check as you go. The third rung is the habit that helps most as queries grow. Picturing the rows between steps, how many and with which columns, is how experienced analysts read long queries, and when the picture is unclear, a count after a step settles it.

The first mistake costs a minute and the second costs understanding. The third mistake is common when a question feels urgent. Asking for everything returns thousands of rows that answer nothing; one step at a time, each checked, reaches an answer faster.

Section 0.2 shows getschema, which lists a table's columns with their exact names and types. Running it on a new table before writing anything else is the habit that prevents most name errors, and it costs a single line.

KQL is not a security product's private language. It was built for Azure Data Explorer, Microsoft's service for analyzing large volumes of log and event data, and the same language now runs in several Microsoft products. Microsoft's KQL documentation lists them on each page: Azure Data Explorer, Microsoft Fabric, Azure Monitor and Microsoft Sentinel among them.

union withsource = SourceTable SigninLogs, DeviceProcessEvents, EmailEvents, AuditLogs
| summarize Rows = count() by SourceTable

The practice lab holds tables from several sources, as a real workspace does: sign-ins and directory changes from Microsoft Entra, process starts from Defender for Endpoint, mail from Defender for Office 365. One query can read and count them all, because the language does not change with the product that collected the data.

For security work, two places matter most. Microsoft Sentinel, Microsoft's cloud security information and event management service, stores data in a Log Analytics workspace and queries it with KQL in its Logs view and its analytics rules. Microsoft Defender's Advanced Hunting runs KQL over the data Defender collects from endpoints, mail, identities and cloud applications, and over Sentinel's data when the two are connected. Section 0.4 compares them in detail.

The language is the same in each, from the first table name to the last step. The tables differ, because each product collects different data, and some limits differ, as Module 10 sets out. A query written for one usually needs its table names and a few columns checked before it runs in another, which Section 10.6 shows how to do.

The tables themselves differ between products in ways that matter later. Sentinel's tables mostly use TimeGenerated for the time of an event; Defender's own tables use Timestamp. Some tables exist only in one product. None of that changes how a query is built, only the names it uses, and the course introduces each table where it first appears.

Wherever you run KQL, the practice lab in this course runs the operators taught here on a fixed month of data, and behaves like a workspace for nearly all of them. Section 0.6 sets out where it differs, such as its tolerance of some names a workspace would reject, so that nothing learned in the lab has to be unlearned in a workspace.

It is fair to ask why analysts should learn a language when security products already have screens for most tasks. Security products come with dashboards, alert pages and search boxes, and those cover the questions their designers expected. Investigations rarely stay inside them.

The question that matters is usually specific to the incident: this account, this address, this hour, compared with that table. A query language answers questions nobody anticipated, in the form the investigation needs, which is why analysts who can write KQL can investigate further than those who cannot.

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

Which days had the most failed sign-ins is a question no dashboard was built to answer in exactly this form, grouped by day and ranked, and four lines answer it: 2 March, with 88 failures, far above any other day, then 5 March and 19 February. A day that stands out like that is where an investigation would look next.

KQL suits that work because its queries read like the steps of an investigation. Start with the data, keep what matters, count, compare, join to another source, sort the result. Each step is one line, so a query can be built by adding lines as the investigation learns more, and read later by someone else who needs to know exactly what was done.

The same queries also become detections. A query that finds an attack once can run on a schedule and raise an alert every time the pattern returns, which is how much of the detection in Sentinel and Defender works.

Writing good queries is the first skill of detection engineering as well as of investigation, and this course treats the two together: every technique is shown on the month's data as an investigation, and many are noted as the basis of a rule.

None of this needs programming experience. KQL has no loops to write and no variables to manage at the start; a query is a description of what to keep and how to arrange it. People who have used spreadsheet filters or written a search query already know the ideas, and the course builds from there.

The questions also change as an investigation proceeds, and a language keeps up with them. A first query finds an unusual address; the second asks which accounts it reached; the third asks what those accounts did next. Each is a few lines, and each is built from the answer before it, which is how the course's own investigations proceed across its ten modules.

The exercise asks you to write the question from Section 02 yourself, starting from a completely blank query: the three accounts with the most failed sign-ins in the month.

The grader checks for three rows with r.scott among them, the account at the top. If you got more rows, the last step may be missing; if you got an error, check the case of the table and column names.

The query is the one Section 02 ran, written this time by you, which is the point: the shape of a question becomes the shape of a query. Read the result as a beginning. In a month of 338 failures, three accounts stand out, one of them far ahead of the others, and the next questions follow naturally from that.

A real investigation would ask when the failures happened, from where, and with what result at the end, and each of those is another step or another query. The rest of the course builds the language to ask them.

Practice

Find the table, read each step in order, picture the rows between steps, and read the last step for the shape of the answer.

The next sub, Section 0.2, looks at how the data a query reads is stored: tables, rows, columns and types.