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: Query Performance, Advanced Hunting Limits and Checking KQL Answers
Introduction
Every earlier lesson ended with a check: a count that reconciled, two routes to one number, a filter read for what it kept. This lesson makes the checks the subject.
A query has two jobs. It has to run within the limits of the service it runs on, which is a matter of speed, and it has to answer the question it was written for, which is a matter of being right. KQL helps with the first and stays silent about the second.
This lesson looks at both. It shows how the engine uses a time filter and why filters go first, finds a reordering that quietly changes the answer, compares two string operators that agree, sets out Advanced Hunting's documented limits, takes apart a borrowed password-spray query that returns nothing, reconciles an answer two ways, and reads an AI-style query that runs cleanly and answers a different question.
A query that runs is the start of checking, not the end of it.
This is the ground Module 10 builds on, and the end of this module. Every query on the page runs in your browser against the same month.
How a Query Runs
The time filter decides what is read at allA real workspace stores data in pieces called shards, and each holds rows from a span of time. Microsoft's best-practice guidance says to apply predicates on datetime columns first, because Kusto has an efficient index on them that often removes whole shards without reading them.
A time filter right after the table therefore decides what the engine reads at all, before any other step does any work. One summarize shows the size of each stage of a typical question:
SigninLogs
| summarize Month = count(),
LastWeek = countif(TimeGenerated > ago(7d)),
LastWeekFailed = countif(TimeGenerated > ago(7d) and ResultType != '0')
Three counts, the month, the last week, and the last week's failures:
Month LastWeek LastWeekFailed
----- -------- --------------
8203 1958 66
8,203 sign-ins in the month, 1,958 in the last week and 66 failed sign-ins in that week. A question about last week's failures needs 66 rows, and a query that filters time first lets the engine skip the 76 percent of the month outside that week before reading a row.
On the lab's month that saves nothing anyone can feel. On a year of a large workspace, where the month is a small fraction of what is stored, it is the difference between reading a few shards and reading all of them.
The same guidance asks for where immediately after the table reference, for has rather than contains when searching for whole terms, and for the smaller table on the left of a join. Each rule has the same reason: do the work that removes rows before the work that handles them.
A query written that way is fast in every engine, because no engine can make reading data it did not need free.
The lab cannot show timing, because its tables are small and the engine runs in your browser. It can show the rows each step touches, which is what timing is made of, and every query in this lesson is written so the rows can be counted.
Outside the lab, the platform measures this for you. Advanced Hunting rates each query's resource use as low, medium or high in its query resources report, and a query that filters late is the usual reason for a high rating.
Filter First, Same Meaning
Reordering can change the answerFiltering first is a performance rule, and applied carelessly it can change the answer. Here is last week's failures written the straightforward way, time first and outcome second:
SigninLogs
| where TimeGenerated > ago(7d)
| where ResultType != '0'
| summarize Failures = count(), Accounts = dcount(UserPrincipalName)
Two where lines, then a summarize of the failures and the accounts behind them:
Failures Accounts
-------- --------
66 42
66 failures by 42 accounts. Now the same question written another way, computing the day and the outcome as columns first and filtering on those:
SigninLogs
| extend Day = startofday(TimeGenerated), Failed = ResultType != '0'
| where Day > ago(7d) and Failed
| summarize Failures = count(), Accounts = dcount(UserPrincipalName)
The extend works on every row of the month, and then the where keeps a week of failures:
Failures Accounts
-------- --------
65 42
65 failures by 42 accounts. The second query is slower, because it computes two columns for all 8,203 rows before discarding most of them, and it is also different. It filters on the start of each row's day being within the last seven days, which drops the part of the oldest day that falls inside the window but whose day started outside it.
1 failure fell in that sliver. The rewrite changed what the window meant, and both versions ran without a word.
That is the trap in performance tuning: moving or rewriting a step to make it faster can change its meaning, and only a comparison shows it.
The safe sequence is to get the query right first, record its answer, then make it faster and check that the answer has not moved. Module 10's lesson on filtering first is built around that sequence, and around the cases where a rewrite that looks equivalent is not.
The difference here is one row. In a detection with a threshold, one row is the difference between an alert and silence.
The fix for the slow version is not to abandon computed columns but to compute them after the filter. A where on TimeGenerated first, then the extend for whatever the rest of the query needs, keeps the original meaning and does the extra work only on the rows that survive.
Most performance problems in real queries are this pattern, an expensive step placed before the filter that would have made it cheap.
Strings and Their Cost
has and contains agree on a whole termThe second lesson showed has and contains returning very different counts for a fragment. For a whole term, they return the same rows, and the choice is only about cost:
DeviceProcessEvents
| summarize Has = countif(ProcessCommandLine has 'Get-WinEvent'),
Contains = countif(ProcessCommandLine contains 'Get-WinEvent')
Both counts are taken in one pass over the month's command lines:
Has Contains
--- --------
33 33
33 command lines either way. Get-WinEvent is a complete term in each command line that contains it, so has, which looks up terms in the index, and contains, which scans every character of every value, find the same rows. The difference is the work done to find them. Microsoft's guidance is explicit that has is preferred to contains when looking for full tokens, because it does not look for substrings.
The same guidance covers case. Comparing with =~ is preferred to lowering a column with tolower() and comparing with ==, because tolower() has to transform every value before comparing it, while =~ compares directly. On lab data both are instant; at scale the transform is the expensive part, and it is also a pattern that appears constantly in published queries.
None of this changes answers, which is why it is safe to apply mechanically: replacing contains with has for a whole term, or tolower() == with =~, gives the same rows faster.
The care needed is in deciding that a value really is a whole term. Section 03 of the second lesson is the counterexample: net inside netsvcs is not a term, and has and contains disagree by more than a thousand rows.
Terms have one more property worth knowing. Kusto's term index holds terms of three characters or more, as the second lesson noted, so has on a shorter value cannot use the index and falls back to scanning. A has on a two-letter switch is correct and no faster than contains, which is a reason to search for the longer, more specific term where one exists.
The test is the one used here: run both, compare the counts, and switch only when they agree. A query that will run thousands of times, as a detection does, is worth that one comparison.
Limits of the Service
What Advanced Hunting documentsA query that is too slow or returns too much does not just take longer; the service stops it. Microsoft documents Advanced Hunting's limits per query: up to 100,000 rows in a result, up to 10 minutes of running time, and CPU resources allocated by tenant size and measured every 15 minutes, with queries blocked if the tenant reaches its allocation until the next cycle.
Sentinel's Logs page queries a Log Analytics workspace with its own limits, and the retention a workspace is configured with decides how far back a query can see.
The habit that keeps queries inside those limits is the one the best-practice guidance ends on: on a new query over unknown data, count first or limit to a few rows, rather than returning everything. Here is a count across four of the lab's busiest tables:
union withsource = Source SigninLogs, AADNonInteractiveUserSignInLogs,
DeviceProcessEvents, EmailEvents
| summarize Rows = count() by Source
| order by Rows desc
The union counts each source separately, without returning any of its rows:
Source Rows
------------------------------- -----
EmailEvents 15468
AADNonInteractiveUserSignInLogs 13586
DeviceProcessEvents 9578
SigninLogs 8203
46,835 rows across four tables. Returned as rows, that would be a result nobody could read and, on a real workspace with a year of data, one that would hit the row limit long before it finished. Returned as counts, it is four numbers that say where the volume is and what a filter needs to remove before the rows are worth looking at.
Limits are also a check on correctness. A result that stops at exactly 100,000 rows has been cut short, and any count taken from it is a count of what the service returned, not of what exists. Module 10's lesson on limits covers how to recognize a truncated result and how to restructure a query that hits one, from summarizing before returning to splitting a window into smaller ones.
The quotas apply separately to manual queries and to custom detection rules, Microsoft notes, which means a query moved from a hunt into a rule is measured again under the rule's own limits.
A Borrowed Query
Count what each filter keepsPublished queries are one of the best ways to learn KQL and one of the easiest ways to be wrong. A query from a repository was written for its author's data, and its tables, columns, values and thresholds encode assumptions about that data. Here is a password-spray query of a common published shape: wrong-password sign-ins to the Azure Portal, counted per account, reported at five or more:
SigninLogs
| where ResultType == '50126'
| where AppDisplayName == 'Azure Portal'
| summarize Attempts = count() by UserPrincipalName
| where Attempts >= 5
On the lab it returns no rows. An empty result from a detection invites the conclusion that nothing happened, and that conclusion is unsupported until the query has been taken apart. The method is to count what each filter keeps, one at a time, with countif:
SigninLogs
| summarize SignIns = count(),
WrongPassword = countif(ResultType == '50126'),
ThroughPortal = countif(ResultType == '50126'
and AppDisplayName == 'Azure Portal')
Each countif adds one filter to the one before it:
SignIns WrongPassword ThroughPortal
------- ------------- -------------
8203 172 6
8,203 sign-ins, 172 with the wrong-password code, and 6 of those through the Azure Portal. The application filter removed almost everything. Those 6 sign-ins belong to 6 accounts, none with five attempts, so the threshold removed the rest.
Without the application filter there are 172 wrong-password sign-ins across 57 accounts, and whether any of that is a spray is now a question that can be asked properly, with a query written for this data.
The filter that did the damage was not wrong in its author's environment, where the portal may have been the target that mattered. It was wrong here.
That is the general lesson about borrowed queries: each filter is a claim about the data, and the claims have to be tested on the data the query will run on. A query that returns nothing has not checked anything until each of its filters has been counted.
Module 10's lesson on reading and adapting public queries makes this a routine: schema first, then filter by filter, then the threshold last, because a threshold tuned for someone else's volume is the claim least likely to transfer.
Two Routes to One Answer
Reconcile before reportingEvery lesson in this module checked an answer by reaching it a second way. The simplest form is to compute a total and then compute it again from its parts. Here are the month's failures summed from their codes:
SigninLogs
| where ResultType != '0'
| summarize Failures = count() by ResultType
| summarize Codes = count(), Failures = sum(Failures)
The first summarize counts failures per code, the second adds the codes back up:
Codes Failures
----- --------
8 338
8 codes and 338 failures, the same 338 a plain count of failed sign-ins returns. Two routes, one number: the grouping lost nothing, and the total can be reported with confidence. If the two had differed, the difference would be the first thing to explain, because it would mean rows were dropped, duplicated or miscounted somewhere between them.
The check also surfaces things worth knowing. Grouping failures by code and description together gives 10 pairs for 8 codes, because some codes appear with more than one description in the lab: a wrong password is described two different ways, and so is a multifactor prompt.
A query that grouped or filtered by description would split one kind of failure into two, exactly as the folder spellings did in the third lesson. The code is the contract; the description is wording.
Reconciliation scales from one number to a whole report.
A report of failures by application should add up to the total; a report by day should add up to the week; a join that enriches rows should return as many rows as it was given. Each is a one-line check, and each catches a different class of mistake from the earlier lessons: the dropped zero days, the default join, the expansion that lost rows.
Reconciling is also cheap to automate. A query can compute the total and the sum of the parts in the same result and flag a difference, so a scheduled report checks itself every time it runs instead of relying on someone to notice. Module 10 shows the pattern.
Module 10's lesson on checking the answer turns these into a short list to run before any number leaves the query window. This module has used every item on it already.
Checking AI-Written KQL
Runs cleanly, answers something elseAI assistants write KQL fluently, and their queries share the failure of borrowed ones: plausible, valid and built on assumptions nobody stated. Here is a query of the kind an assistant produces when asked for wrong-password failures:
SigninLogs
| where ResultDescription contains 'password'
| summarize Failures = count() by ResultType
It filters on the description, a readable column, and counts by code:
ResultType Failures
---------- --------
50126 172
50055 3
175 failures, against the 172 that the wrong-password code alone gives. The extra 3 are expired passwords, whose description also contains the word, and which are a different event: the user knew the password and it had lapsed.
The query ran, returned a reasonable number and answered a slightly different question from the one asked. The second lesson's has against contains and the third lesson's classify-on-the-code both apply, and either would have caught it.
What makes AI-written KQL hard to check is how good it looks. It uses the right operators, plausible column names and sensible structure, so the reader's attention slides past the one assumption that is wrong.
Microsoft's own tables add to the risk, because similar columns exist under different names in Sentinel and Defender tables, and an assistant can mix them. The checks that catch these problems are the ones a borrowed query needs, applied in the same order:
The ladder is the whole module in five rungs: schemas from the first lesson, filters from the second, codes from the third, reconciliation from every lesson, and reading the query for the question from all of them. Three conclusions it protects against:
None of the three is a failure of KQL. Each query did exactly what it said; each conclusion went further than the query did. The checks on this page are about closing that gap, between what a query says and what its reader concludes.
What Module 10 builds
Module 10Module 10 builds on this lesson: how a query runs, filtering first, string operators and cost, joins and summaries at scale, the limits of Sentinel and Advanced Hunting, adapting public queries, checking the answer and checking AI-written KQL.
The habits to carry into it are the ones this module has repeated: know the size, filter time first, keep meaning fixed while making a query faster, count what each filter keeps, compare on codes, reconcile two routes, and read every query for the question it actually asks.
Practice
Take the borrowed query apart prepared evidenceThe prepared evidence below is the borrowed password-spray query taken apart one filter at a time on the lab's month. Find the filter that removed most of the wrong-password sign-ins, decide what to do before concluding anything, and say what the empty result tells you about the month. Try each question before opening its answer, and say which row decided it.
The table is the method itself: one row per filter, each row the count after that filter, read from the top down until the count collapses. The row where it collapses is the filter to examine, and the question to ask of it is whether its assumption holds on this data.
This is the last practice of the module, and it uses every habit the module built: counting before trusting, reading filters for what they keep, comparing on codes and treating an empty result as a question rather than an answer.
Then run the queries on this page and change them. Remove the application filter from the borrowed query and lower nothing else, then compare the two routes in Section 06 after adding ResultDescription to the first summarize.
A query has to be fast enough to run and right enough to trust, and only the first is enforced by the engine.
Filter on time first and keep meaning fixed while making a query faster, prefer whole-term operators when they agree, stay inside the service's limits by counting first, take borrowed and AI-written queries apart filter by filter, compare on codes, and reconcile every answer by a second route before it leaves the query window.
That is the end of Module 0. Each of its ten lessons was a short version of one module; the course now takes them one at a time, starting with Module 1 and how a query is built.