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: where, has and contains, Time Windows and Negative Filters
Introduction
Most of the work in most queries is done by one operator. where keeps the rows that satisfy a condition and drops the rest, and almost every question an analyst asks starts by narrowing a table to the rows that matter: one file, one account, one day, one switch on a command line. Getting that narrowing right decides the answer more often than anything that comes after it.
This lesson narrows a month of process starts on the lab's managed devices. It asks exact questions and loose ones, inside time windows and outside them, and it shows the three ways a filter fails quietly: by case, by matching too much, and by keeping rows that have no value at all.
Each where keeps the rows that match and hands the rest of the query fewer to read.
This is the ground Module 2 builds on. The table is DeviceProcessEvents, the endpoint family's record of every program started on a managed device. Every count on the page comes from the query above it, and every query runs in your browser, so change the values and watch what the counts do.
Narrowing a Month
where keeps the rows that matchBefore filtering a table, it is worth knowing how big it is and how many things it covers. One summarize gives both, the rows and the distinct devices:
DeviceProcessEvents
| summarize Processes = count(), Devices = dcount(DeviceName)
It returns one row, the size of the whole month on the endpoint side:
Processes Devices
--------- -------
9578 18
9,578 process starts on 18 devices. That is the denominator for everything below, and it is small next to a real estate, where a single busy server can start more processes than this in a day. The habit transfers unchanged: know the size of what you are about to filter.
Now the simplest kind of question, one program by name:
DeviceProcessEvents
| where FileName =~ 'powershell.exe'
| summarize Processes = count(), Devices = dcount(DeviceName)
The where keeps the rows whose FileName is powershell.exe in any case, and the summarize counts them:
Processes Devices
--------- -------
786 17
786 PowerShell starts on 17 of the 18 devices. Nothing about that number is suspicious by itself; administrators and management tools run PowerShell constantly. What the where did is turn a question about the whole month into a question about one program, and every later step can now work on 786 rows instead of 9,578.
A where takes a condition that is either true or false for each row. Conditions can be combined with and and or, negated with not, and written as several where lines one after another, which reads more clearly and means the same as one where with and between the conditions. Module 2 is about choosing conditions, because the condition is where the question either survives or quietly changes.
It is worth seeing what this filter did not decide. It did not decide which PowerShell starts matter, or on which devices, or when. It narrowed the table to one program, and the later sections narrow further, by text inside the command line and by time.
A good filter does one job that can be named in words, and a query is easiest to check when each where line does exactly one.
Exact Values and Case
== is case-sensitive, =~ is notThe file name filter above used =~, not ==. The difference is case. == compares two values exactly, including whether letters are upper or lower case, and =~ compares them ignoring case. The lab stores Microsoft Teams as Teams.exe, so watch what each operator makes of a lower-case name:
DeviceProcessEvents
| summarize Exact = countif(FileName == 'teams.exe'),
AnyCase = countif(FileName =~ 'teams.exe'),
AsStored = countif(FileName == 'Teams.exe')
Three counts, one per way of writing the same name:
Exact AnyCase AsStored
----- ------- --------
0 673 673
The exact lower-case comparison finds nothing. The case-insensitive one and the exact comparison with the stored spelling both find 673. A filter that returns no rows looks exactly like a question with no answer, and nothing in the result says the filter was wrong. That is the most dangerous failure in filtering: an empty result that is believed.
The rule that follows is simple. Use == when the stored spelling is known for certain, because it is the faster of the two, and =~ when it is not, which is most of the time for file names, account names and anything typed by a person.
Microsoft's guidance is to prefer the case-sensitive form when both would do the same job, for performance, and that advice assumes you have checked the spelling.
The same pair exists for lists. in keeps a row whose value is one of a list, exactly, and in~ does the same ignoring case, so FileName in~ ('powershell.exe', 'pwsh.exe') finds both PowerShell hosts however each was recorded. Lists keep a query readable when a question names more than two or three values, and Module 2 uses them heavily.
Case is not only a file name problem. Account names arrive in whatever case an administrator typed when the account was made, domain names in whatever case a link used, and host names in whatever case a device reported. A query that will be run again, or shared, should not depend on the spelling one person happened to see in one result.
Whole Terms and Parts
has against containsText inside a longer value, such as a switch in a command line, needs a different kind of operator. KQL breaks every string into terms, the runs of letters and digits between other characters, and has asks whether a value contains a term. contains asks whether it contains the characters anywhere, even inside a longer word. Count both for the same three letters:
DeviceProcessEvents
| summarize Contains = countif(ProcessCommandLine contains 'net'),
Has = countif(ProcessCommandLine has 'net'),
NetExe = countif(FileName =~ 'net.exe')
The counts sit side by side, with the number of net.exe processes beside them for comparison:
Contains Has NetExe
-------- --- ------
1205 226 226
contains finds 1,205 command lines; has finds 226. The difference is rows where net is part of a longer word, and a second query shows where they came from:
DeviceProcessEvents
| where ProcessCommandLine contains 'net'
| where not(ProcessCommandLine has 'net')
| summarize Rows = count() by FileName
| top 3 by Rows
It keeps the rows contains matched and has did not, and names the programs behind them:
FileName Rows
----------- ----
svchost.exe 760
cmd.exe 163
msedge.exe 54
Every svchost.exe start in the month has the command line -k netsvcs, which contains the letters and not the term. The has count, meanwhile, matches the net.exe count exactly, because in this month the only command lines with net as a term of its own are net.exe's. An analyst looking for net commands who used contains would have read 979 irrelevant rows.
has is also faster. Kusto builds an index of terms three characters or longer, and has can use it, while contains has to scan every value.
On a month of lab data the difference is invisible; on a year of a large workspace it is the difference between a query that returns and one that times out. Module 2 teaches the rest of the family, from startswith to the case-sensitive variants, and the choice comes down to a short climb:
Start at the top and stop at the first rung that fits the question.
Most of the filters an analyst writes belong on the first two: a file name is a whole value, a switch or a cmdlet in a command line is a whole term. contains is the right tool for a fragment that really can sit inside a longer word, such as part of an encoded string, and when it is used the next step is to read a sample of what it kept, because it will keep things nobody asked for.
Inside a Time Window
ago, explicit dates and where a day endsEvery security question has a time window, and a query that does not state one asks about everything the table holds. ago() counts back from now, which in the lab is fixed at noon on 15 March, so Timestamp > ago(1d) keeps the last day and Timestamp > ago(7d) the last week. In this month those are 13 and 2,401 process starts.
A question about a particular day needs explicit dates. Here is 3 March, written as a range that includes its start and excludes its end:
DeviceProcessEvents
| where Timestamp >= datetime(2026-03-03)
and Timestamp < datetime(2026-03-04)
| summarize Processes = count(), Devices = dcount(DeviceName)
It counts the process starts that day and the devices they ran on:
Processes Devices
--------- -------
457 15
457 process starts on 15 devices. The same day written with between, from 3 March to 4 March, returns the same number here. It would not always: between includes both of its ends, so a row stamped exactly at midnight on 4 March would be counted in 3 March and again in 4 March if the next day's query used the same form.
The half-open range, greater than or equal to the start and less than the end, never counts a row twice, and that is the form to use when days are counted one after another.
Time filters belong at the top of a query, straight after the table. A workspace stores its data by time, and a filter on the time column lets the engine skip everything outside the window before it reads a row. Putting the time filter last asks the engine to do all the other work on data it will throw away, and Module 10 measures what that costs.
The time column itself has to be the right one. In Defender tables it is Timestamp, in Sentinel tables TimeGenerated, and some tables carry other times as well, such as when a process was created or when a message was delivered. They are not interchangeable, and Module 2's lessons on working inside a window and pivoting on a timestamp are about choosing and using them.
When You Do Not Know the Column
search, and what it costsSometimes an analyst has a value, a file name, an address, a hash, and no idea which tables or columns hold it. search looks for a value across every column of the tables it is given. Limited to two tables here:
search in (DeviceProcessEvents, DeviceNetworkEvents) 'teams.exe'
| summarize Rows = count() by $table
$table is a column search adds, naming the table each row came from, so the summarize can count by it:
$table Rows
------------------- ----
DeviceProcessEvents 673
DeviceNetworkEvents 968
The value appears in 673 process rows, where it is the file name, and in 968 network rows, where it is the process that opened a connection. search found both without being told which column to read in either table, and its match is case-insensitive, which is why the lower-case value found Teams.exe.
That convenience has a cost. search reads every column of every table it is pointed at, so on a large workspace an unscoped search is the slowest query an analyst can write. It is the right first step for a value you know nothing about, and the wrong second step.
Once it has said where the value lives, the follow-up query names the table and the column and uses where, which is exact, fast and readable by the next person.
The result also shows why a value's meaning depends on its column. In the process table Teams.exe is the program that started; in the network table it is the program that made a connection. The same string answers two different questions, and a search result is only the list of places to ask them.
search has a narrower cousin worth knowing. A where with the column, as in where has 'teams.exe', looks across every column of one table, which is useful when the table is known and the column is not. Both forms are for finding, not for keeping: a query that will run again names its column.
Module 2 opens with this problem, searching for a value when you do not know the column, because it is how many investigations start: an indicator from a report, a name from a ticket, and a workspace with dozens of tables to find it in.
What Not to Keep
Negative filters and the empty values they keepA negative filter keeps everything that does not match: !=, !in, !has, or not() around any condition. They are useful for removing known noise, and they carry a trap that positive filters do not. Ask how many sign-ins came from something other than Windows, using the operating system inside DeviceDetail:
SigninLogs
| extend OS = tostring(DeviceDetail.operatingSystem)
| summarize All = count(), NotWindows = countif(OS != 'Windows10'),
NoOS = countif(isempty(OS))
The query counts all sign-ins, the ones whose OS is not Windows10, and the ones with no OS at all:
All NotWindows NoOS
---- ---------- ----
8203 5787 5787
Of 8,203 sign-ins, 5,787 are not Windows10, and every one of them is a sign-in with no operating system recorded at all. The negative filter did exactly what it was told: an empty value is not Windows10. But the question was which sign-ins came from another system, and the honest answer is that none recorded one. A report that said 5,787 non-Windows sign-ins would be wrong by all of them.
The fix is to say what is known before saying what is not: isnotempty(OS) first, then the negative condition. That turns an unknown into an excluded row instead of a counted one, and the count becomes the answer to the question asked.
The same trap waits in any column that is often empty, which in real sign-in data includes device details for unmanaged devices and location details for some service traffic.
Negative filters for noise have a second risk. Every value added to an exclusion list is a value nothing will ever match again, and an exclusion added for a benign program will also hide a malicious program that takes its name. Module 2's lesson on negative filters is about both problems: the empty values a negative keeps, and the real activity an exclusion hides.
All three filters above run without errors, and each one returns a plausible number. That is the theme of the whole module: KQL accepts the filter you wrote, not the one you meant, and the only defense is to read what each filter kept. A count before and after, or a look at a few of the rows it removed, takes seconds and catches each of these.
Filters in Order
Time first, then exact, then textA real question usually needs several filters. Which devices ran PowerShell's Get-WinEvent in the last week? It needs a time window, a program and a term in the command line, and the order they go in matters for speed even when it does not change the answer:
DeviceProcessEvents
| where Timestamp > ago(7d)
| where FileName =~ 'powershell.exe'
| where ProcessCommandLine has 'Get-WinEvent'
| summarize Runs = count(), Devices = dcount(DeviceName)
The query keeps a week, then PowerShell, then the term, and counts what is left:
Runs Devices
---- -------
9 5
9 runs on 5 devices. The time filter went first because it lets the engine skip the rest of the month. The exact file name went second because an exact comparison is cheap and cuts the week's rows down to 211 PowerShell starts.
The term filter on the command line went last, because it reads the longest values and is best asked of as few rows as possible. The answer is the same in any order; the work is not.
Each where line also does one job that can be named, which makes the query easy to check. Comment out the last line and the count is the week's PowerShell; comment out the second as well and it is the week's processes. Being able to take a query apart one filter at a time, and see what each kept, is how a wrong answer is found.
The question itself also shaped the filters. Which devices ran Get-WinEvent is a question about a cmdlet, so the term filter names the cmdlet rather than a fragment of it, and it is a question about PowerShell, so the file name filter names both the program and its case-insensitive comparison.
Writing the question in words first, then one where per clause of it, is the method this module and Module 2 both use.
What Module 2 builds
Module 2Module 2 builds on these ideas one lesson at a time: searching for a value across tables, narrowing a table, matching text the way the engine indexes it, time windows and pivoting on a timestamp, addresses and ranges, nested fields and negative filters.
The habits from this lesson carry through it: know the size of what you filter, match case deliberately, prefer whole terms, state the window, and read what a negative filter kept.
Practice
Read the counts for what each filter matched prepared evidenceThe prepared evidence below is seven where clauses, each run alone against the month of process starts, with the number of rows it kept. Find the filter that failed on case, the filter that matched far more than its author meant, and the reason two different filters kept the same rows. Try each question before opening its answer, and say which operator decided it.
Counts are a useful way to judge a filter before reading any rows. A filter that keeps nothing, or keeps far more than the question could plausibly need, is telling you something about itself before it tells you anything about the data.
Two filters written different ways that keep the same rows are a check passed. The questions below use all three readings: a count of zero, a count far too large, and two counts that agree when nothing forced them to.
Then run the queries on this page and change them. Swap the file name in the case query for OUTLOOK.EXE in lower case, then change the term in the has query to enc and compare its count with contains. Each change is one operator, and each count says what that operator really does.
A where keeps rows, and how it compares values decides which. Use == for known spellings and =~ otherwise, has for whole terms and contains only when nothing else fits, half-open ranges for days and the time filter first, search only to find where a value lives, and isnotempty before a negative filter.
The next lesson takes the rows a filter kept and shapes them: new columns, chosen columns, classified values and an order the reader can use.