Reading width
Wide uses the full column for everything, text, diagrams, code, and exercises. Narrow keeps the standard reading width.
Text size
Scales the body text. Headings and code blocks keep their size.
In this section
KQL Foundations: How a KQL Query Is Built, Column Types and Where KQL Runs
Introduction
KQL, the Kusto Query Language, is how a security analyst asks questions of the data in Microsoft Sentinel and Microsoft Defender XDR. A query is a request to read data and return a table. It cannot change the data it reads. That makes it safe to experiment with, and it means the only thing that can go wrong is the answer.
This lesson takes one question from words to a checked answer: how many sign-ins failed in the month the course's lab holds? The answer is one line of KQL long. Getting it right needs most of what Module 1 teaches: knowing which table to ask, what a row in it means, what type each column holds, and how to check that the number is the answer to the question asked.
A query is a table followed by steps; each step receives the table the one before it produced.
Every lesson in this module lays the ground for one later module, on the same month of data the whole course uses. This one is Module 1's, on how a query is built and what the values in a table really are. Every query on the page runs in your browser against that month, so run each one, then change it.
A Table, Then Steps
The pipe hands each step the table before itA KQL query starts with a table and then lists steps, each joined to the one before by a pipe, the vertical bar. Each step receives a table and returns a table, so the data flows down the query and narrows as it goes. Here is the question in that form:
SigninLogs
| where ResultType != '0'
| count
Read it top to bottom. SigninLogs is the table of interactive sign-ins to Entra ID, and on its own it is all 8,203 of them. The where step keeps the rows whose ResultType is not '0', the code for success. The count step turns whatever it receives into one row holding a number. Run it and that number comes back:
Count
-----
338
338 failed sign-ins in the month. The habit worth taking from this first query is to read every pipe as a hand-over. The where never sees the count, and the count never sees the rows the where dropped.
The meaning of a row changes as it moves down: after the first line a row is a sign-in, after the where it is a failed sign-in, and after the count it is a number with no sign-ins in it at all.
It also helps to say what the query does not ask. It does not say which users failed, from where, or when, and it counts every failure code alike, the mistyped password and the blocked-by-policy sign-in. A number is only as precise as the question behind it, and the next section shows a richer question of the same rows.
Because each step only sees what it is handed, order matters. A step that removes columns before a where that needs one of them makes the where fail, and a step that keeps a sample of rows before a count makes the count describe the sample. KQL runs both without complaint, which is why Module 1 spends a whole lesson on operator order.
The language is case-sensitive for table names, column names, operators and functions. SigninLogs and signinlogs are not the same name, and a column typed in the wrong case is a column that does not exist. The console's autocomplete helps, and the error message names the column it could not find, which is usually the fastest way to catch it.
Every Answer Is a Table
Even a single number comes back as one rowThe count above looks like a number, but it came back as a table with one column and one row. Everything KQL returns is a table, and that is why a result can always be handed to another step. Ask a richer question of the same rows and the table gets wider and longer:
SigninLogs
| summarize SignIns = count() by ResultType
| order by SignIns desc
Summarize groups the rows by the value of ResultType and counts each group. One row comes back for each code the month contains:
ResultType SignIns
---------- -------
0 7865
50126 172
50076 59
50053 37
53003 25
50074 20
500121 14
50058 8
50055 3
The first row is success, 7,865 sign-ins with code 0. The other 8 rows are failure codes, led by 50126 with 172 sign-ins. Microsoft documents each code. 50126, for example, is an invalid user name or password, 50076 is a sign-in that had to use multifactor authentication, and 50074 is one whose multifactor challenge was not passed.
Add up the failure rows and you get the 338 from the first query, which is the first check this course asks of every answer: does it reconcile with something you counted another way?
Here is the method as five steps. Every query in the course follows it, from this one to the last:
Turning a question into KQL
Every query in this courseThe table this query returned is also more useful than the number. It says how many sign-ins failed and why, and it is a table the next step can work on: sort it, filter it to the codes that mean a wrong password, or join it to a list of descriptions.
That is the reason KQL answers in tables. A question is rarely finished after one step, and a table is something the next step can work on.
Grouping also changes what a row means, the same way the where did in the first query. Before the summarize a row is one sign-in; after it, a row is one result code with a count beside it, and nothing about any individual sign-in survives.
If the next question is which accounts produced the 50126 failures, this table cannot answer it, because the accounts were grouped away. The query has to go back a step and group by account as well, which is exactly the kind of decision Module 4 is about.
The order of the result is the query's choice too. Without the order by line, KQL makes no promise about which group comes first, and two runs can return the same rows in a different order. A result that looks sorted without a sort is luck, not a property of the data.
Know the Table First
getschema before the first filterEvery query above assumed a column called ResultType, that it holds codes, and that success is '0'. None of that should be assumed of a table you have not queried before. The first thing to run against an unfamiliar table is getschema, which returns the table's columns and their types instead of its rows. Counted by type, SigninLogs in the lab looks like this:
SigninLogs
| getschema
| summarize Columns = count() by ColumnType
The result is a small table about the table, one row per type, with a count of the columns that hold it:
ColumnType Columns
---------- -------
datetime 1
string 23
dynamic 4
bool 2
30 columns: one datetime, 23 strings, 4 dynamic and 2 bool. Production SigninLogs has many more columns than the lab's; the lab keeps the ones the course's questions need. The practice at the end of this lesson gives you the full list to read.
Each type says something about how the column can be used. The datetime column, TimeGenerated, is the one every time filter in Sentinel works on. The strings are compared as text, which is fast for exact values and needs care with case. The bools hold true or false.
The dynamic columns hold nested data, a bag of named values or an array, so the city of a sign-in is not a column of its own. It is a field inside LocationDetails, reached with a dot, and Module 6 is about working inside data like that.
A schema also tells you what a table does not have. SigninLogs has no column for the device's name in the way the Defender tables do; the device it knows about is inside DeviceDetail, and often empty for a sign-in from an unmanaged device. Knowing that before writing a query saves the query that filters a column the table never had.
The column names matter as much as the types. ResultType and ResultDescription carry the outcome, UserPrincipalName and IPAddress say who and from where, and the risk columns record what Entra ID Protection thought of the sign-in at the time. Read down a schema like this once and the questions you can ask of the table become obvious.
Module 1's lesson on learning a table you have never seen is this step done thoroughly, and it climbs in four rungs before the first real filter:
Most wrong answers on a new table come from skipping a rung: filtering a column whose values were never looked at, or trusting a name that means something else in this table.
What a Value Really Is
The same idea, two types, in two tablesResultType looks like a number and is a string. That is not a quirk of the lab: Microsoft's reference for SigninLogs lists it as a string holding the five- or six-digit error code, with 0 for success. The Defender table that records the same sign-ins stores the same idea differently. Put the two schemas side by side:
union
(SigninLogs | getschema | extend Table = 'SigninLogs'),
(AADSignInEventsBeta | getschema | extend Table = 'AADSignInEventsBeta')
| where ColumnName in ('TimeGenerated', 'Timestamp', 'ResultType', 'ErrorCode')
| project Table, ColumnName, ColumnType
One union of the two getschema results, filtered to the four columns that matter here:
Table ColumnName ColumnType
------------------- ------------- ----------
SigninLogs TimeGenerated datetime
SigninLogs ResultType string
AADSignInEventsBeta Timestamp datetime
AADSignInEventsBeta ErrorCode long
Two differences matter. The time column is TimeGenerated in the Sentinel table and Timestamp in the Defender table. The result code is ResultType, a string, in one and ErrorCode, a long, in the other.
A query written for one table and pasted against the other fails on the column name, which is the easy case, because the error says so. The harder case is a comparison that runs but means something different.
That is why the first query wrote '0' in quotes. Comparing a string column with a string is comparing like with like. Comparing it with the number 0 asks the engine to reconcile two types, and a query should never depend on how an engine chooses to do that. The Defender table is compared the other way, with the number:
AADSignInEventsBeta
| summarize SignIns = count(), Failed = countif(ErrorCode != 0)
It returns the same month from the Defender side, with the failures counted on its numeric code:
SignIns Failed
------- ------
8203 338
8,203 sign-ins and 338 failures, the same figures as SigninLogs, because in the lab both tables describe the same events. Two tables that agree are a check, and two that disagree are a finding. In a real tenant the two can disagree for ordinary reasons, such as ingestion delay or a connector that was switched off for a week, and the disagreement is worth reading before either number is trusted.
Types matter beyond comparisons. A datetime can be binned into days and subtracted from another datetime to give a duration; a string holding a date cannot, until it is converted. A long can be summed and averaged; a string of digits can only be counted.
Every function in KQL expects particular types, and the schema is where you find out which you have. Module 1 teaches the types in full, including the empty strings and nulls that make an incomplete value look like a missing one.
Where KQL Runs
Sentinel Logs and Defender Advanced HuntingSecurity analysts meet KQL in two places. Microsoft Sentinel keeps its data in a Log Analytics workspace and queries it from the Logs page; its tables, such as SigninLogs and SecurityEvent, use TimeGenerated for time.
Microsoft Defender XDR queries its own data from Advanced Hunting; its tables, such as DeviceProcessEvents and EmailEvents, use Timestamp. The two are converging: a Sentinel workspace onboarded to the Defender portal can be queried from Advanced Hunting alongside Defender's own tables.
One language, two places it runs, and the practice lab that holds tables from both.
The places differ in what they keep. Advanced Hunting searches up to 30 days of raw Defender data by default, and Microsoft documents up to 90 days with an eligible license. Sentinel tables keep what the workspace's retention is set to.
A question about last quarter can be answerable in one and not the other, so the first thing to know about any hunt is which place, and therefore how far back, it can see.
Here is the Defender form of a time filter, against the table of process starts on managed devices, counting the last day before the lab's present:
DeviceProcessEvents
| where Timestamp > ago(1d)
| count
The column is Timestamp, because DeviceProcessEvents is a Defender table, and ago(1d) counts back from the lab's fixed present:
Count
-----
13
13 processes started in that day. Swap Timestamp for TimeGenerated and the query fails, naming the column it could not find. That error is a good one to meet early, because it says exactly what is wrong.
The language is the same in both, and nearly every operator in this course runs in either. The difference a query feels is the table names and the time column. A query that filters TimeGenerated against a Defender table fails, and the fix is the column, not the logic.
The lab holds tables from both families, so you will write both kinds of time filter; the query that counted sign-ins in the Defender table is a small example of a Defender query.
Detection rules are another place the same KQL runs. A Sentinel analytics rule and a Defender custom detection are both a query plus a schedule, so a query you learn to trust here is close to a query that can raise an alert.
The course stays with the query; the rules are the subject of other courses on this platform. What it gives you is the part a rule cannot do without: a query that answers the question it claims to answer, on the table it claims to read.
The Sample Month
What the lab holds, and its fixed presentThe course's lab is one month of records from an engineering company. It has identity, endpoint, mail, cloud-app and network tables, 49 in all, and every query in every lesson runs in your browser against it. The month's edges are themselves a query:
SigninLogs
| summarize Earliest = min(TimeGenerated), Latest = max(TimeGenerated),
Days = dcount(bin(TimeGenerated, 1d))
The earliest sign-in, the latest, and the number of days that have one, from a single summarize:
Earliest Latest Days
------------------- ------------------- ----
2026-02-13 02:02:22 2026-03-15 11:57:09 31
The month runs from 13 February to 15 March 2026, 31 days with sign-ins. The lab's present is fixed at noon on 15 March, so ago(7d) in any query means seven days before that moment, whenever you run it. A query you write today returns the same rows next year, which is what lets a lesson print a result and promise that your run will match it.
The tables fall into five families, and most questions start in one of them. Identity tables such as SigninLogs and AuditLogs record who signed in and what changed in the directory. Endpoint tables, the Device tables, record what ran, what was written and what connected on managed machines. Mail tables record what arrived, what was in it and what was clicked.
Cloud-app tables such as OfficeActivity and CloudAppEvents record what users did in Microsoft 365. Network and server tables such as CommonSecurityLog, Syslog and SecurityEvent record firewalls, Linux hosts and Windows security events. Choosing the family is the first decision in every query, because a question asked of the wrong family has no right answer in it. Later lessons in this module visit each family at least once.
Each query on the page has a Run button and opens in the lab's console, where it can be edited and run again. Use both. Reading a query teaches less than changing one condition and predicting what the change will do to the result, then running it to find out whether you were right.
Baselines Before Findings
The ordinary month, and what Module 1 buildsSeveral real attack patterns run through the month: a phishing campaign that steals a session, a password spray, an endpoint compromise, ransomware staging and attacks on Active Directory. They sit in ordinary activity the way they would in a real workspace, and later modules find them with the operators they teach.
This module stays with the ordinary month, so it names those incident types and leaves their details to the modules that find them.
The lab differs from a real workspace in a few ways worth knowing. It is smaller, so queries come back in a moment. Its tables keep the columns the course uses rather than every column Microsoft ships. And it never changes, so a figure printed in a lesson stays true.
Everything else, from the table names to the way the engine treats types, follows the real service, and every query in the course is written to run in Sentinel or Advanced Hunting unchanged.
That is also why every lesson in this module asks baseline questions: how many, of what kind, on how many devices. A baseline is the thing every hunt is measured against, and an analyst who knows what a normal day of sign-ins looks like reads an abnormal one faster.
The figures in this lesson are already a small baseline. In a month of 8,203 interactive sign-ins, 338 failed, and most failures were a mistyped password or a multifactor step.
An analyst who has that picture in mind sees immediately when a day's failures are ten times the usual, or when one code that is normally rare dominates. The count is cheap and it turns every later question into a comparison.
Baselines also keep a query honest. A filter that returns half the month's sign-ins as suspicious is wrong about the month, however reasonable it looks; a baseline is what tells you so before the result reaches a report. The habit of counting the whole before examining the part runs through every module, and it starts here, with the most ordinary table in the lab.
What Module 1 builds
Module 1Module 1 builds on this lesson directly: how a question becomes KQL, how statements and the pipe work, how to learn a table you have never seen, what a value is when it is empty or null, and how to read a query somebody else wrote.
Practice
Read a schema before the first query prepared evidenceThe prepared evidence below is the full schema of SigninLogs in the lab, every column and its type. Pick the column that says whether a sign-in succeeded, the columns that hold nested data, and the right way to write a success in a where. Try each question before opening its answer, and say which column type decided it.
Answer from the types first and the names second. A column's name suggests what it holds, and its type says how it can be used, which is what decides how a filter must be written.
When the two seem to disagree, the type wins, as ResultType's digits held in a string show. If an answer surprises you, run getschema yourself in the console and read the row again; the schema in the practice is the one the lab serves.
A schema is the cheapest evidence there is: one query, no rows read, and it rules out a whole class of mistakes before they happen. The three questions are the three a schema answers first on any table: which column carries the outcome, which columns need unpacking, and which comparisons the types allow.
Then run the queries on this page and change them. Swap SigninLogs for AADNonInteractiveUserSignInLogs in the getschema query and see which columns the non-interactive table adds, then count its failures with the same where.
A query is a table followed by steps, each handed the table the one before produced, and every answer is a table. Read a table's schema before filtering it, compare each column in its own type, know which place a query runs in and how far back it can see, and check every answer against a count taken another way.
The next lesson is about the step that decides most answers, the where, and how to find the rows that matter in a month of them.