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: let Statements, User-Defined Functions, Watchlists and IP Address Functions
Introduction
A query that is written once and thrown away can hold its numbers and lists inline. A query that will be run again, shared with a team or turned into a detection cannot, because every number in it is a decision somebody will need to find and change, and every list in it is a copy of something that already lives somewhere else.
KQL gives names to values, results and logic with let, and reads lists kept outside the query from watchlists and other sources.
This lesson names things. It names a threshold and a window, names a filtered result to reuse it, writes a small function that decides whether an address is corporate, writes another that takes a whole table, reads the company's egress addresses from a watchlist, and uses address functions on firewall traffic, the network family's record of what crossed the perimeter.
Four things a query can name: a value, a result, a piece of logic, and a list maintained elsewhere.
This is the ground Module 7 builds on. Every query on the page runs in your browser against the same month.
Naming a Value
let for thresholds and windowsA let statement gives a name to a value, and the rest of the query uses the name. It ends with a semicolon, and any number of lets can come before the query that uses them. Thresholds and time windows are the commonest values to name, because they are the ones most often changed:
let Window = 30d;
let MinFailures = 10;
SigninLogs
| where TimeGenerated > ago(Window) and ResultType != '0'
| summarize Failures = count() by UserPrincipalName
| summarize Accounts = count(), AtOrOver = countif(Failures >= MinFailures)
The two names stand for 30 days and ten failures, and the query reads them where it needs them:
Accounts AtOrOver
-------- --------
79 3
79 accounts had at least one failed sign-in in the window, and 3 reached the threshold. The result is the same as if the values had been typed inline, so the gain is not in the answer. It is in the query. Anyone reading it sees at the top what the window and the threshold are, and anyone changing either changes one line, not every place the number appears.
That matters more than it seems in a query of six lines. Detection rules grow, get copied and get tuned, and a threshold typed in three places is eventually changed in two.
Named values also make a query's assumptions visible: MinFailures = 10 is a decision somebody made, and putting it at the top with a name invites the question of why ten, which is the question a reviewer should ask.
A let can name any scalar value: a number, a string, a timespan, a datetime, a list built with dynamic(). It can also be computed from other lets, so a window can be defined once and an end time derived from it. Names follow the same rules as column names, including case sensitivity, and they should say what the value means rather than what it is: MinFailures rather than Ten.
Named values also make a query easy to rerun against a different question. Change Window to 7d and the same query describes last week; change MinFailures to 1 and it lists every account that failed at all. A query whose decisions sit at the top is a small tool, not a single answer.
The habit is cheap and it compounds. Every query in Modules 8 to 10 starts with a block of lets, and by then a query without them reads as unfinished.
Naming a Result
A filtered table, reusedlet can also name a whole tabular result: everything to the right of the equals sign is a query, and the name then stands for its result wherever it is used. That is useful when a filtered table is the starting point for more than one question:
let Failed = SigninLogs
| where ResultType != '0';
Failed
| summarize Failures = count(), Accounts = dcount(UserPrincipalName),
Apps = dcount(AppDisplayName)
Failed is the month's failed sign-ins, and the query asks three questions of it at once:
Failures Accounts Apps
-------- -------- ----
338 79 13
338 failures by 79 accounts across 13 applications. Here Failed is used once, so the gain is readability: the filter has a name that says what it is, and the summarize reads as a question about failures.
The real gain comes when the same result is used twice, for example compared with itself over two periods, or joined to another table and also counted alone. Writing the filter once means both uses read exactly the same rows.
A named result is not stored anywhere. KQL substitutes the definition where the name is used, so a let used twice may be evaluated twice. On the lab's month that costs nothing; on a large workspace it can matter, and materialize() is the function that tells the engine to compute a result once and reuse it, which Module 10 covers with the rest of query performance.
Naming results also splits a long query into parts a reader can follow. A detection that reads sign-ins, filters them, joins them to a watchlist and summarizes them can be written as four lets and one line that combines them, each let doing one job with a name that says which.
That is the same principle as one where per clause in the second lesson, applied to whole stages of a query.
Module 7's lesson on naming intermediate results goes further, into results that are used as lists, as joins and as scalar values, and the toscalar() function that turns a one-row result into a value another part of the query can compare against.
Writing Your Own Function
Logic named once, called anywherelet can also define a function: a name, a list of parameters in parentheses, and a body in braces. Whether an address belongs to the company is a test that appears in many queries, so it is a good candidate. The company's addresses are private ranges inside and a NAT pool outside:
let IsCorporate = (ip: string) {
ipv4_is_private(ip) or ipv4_is_in_range(ip, '203.0.113.32/28')
};
SigninLogs
| summarize Corporate = countif(IsCorporate(IPAddress)),
Elsewhere = countif(not(IsCorporate(IPAddress)))
IsCorporate takes an address and returns true or false, and the summarize calls it twice:
Corporate Elsewhere
--------- ---------
7456 747
7,456 sign-ins came from a corporate address by this definition and 747 from elsewhere. The definition of corporate is now in one place, with a name that says what it decides. A query that needs it calls IsCorporate(IPAddress) and does not repeat the ranges, and when the NAT pool changes, the function changes and every call follows.
Functions are also how an analyst keeps logic honest. The test above is two conditions; the next lesson's version of it might be five, with an exception for a partner's range and a check for the VPN.
Inline, those conditions would be copied into every query and drift apart. As a function, they are written once, reviewed once and tested once, and a query reads as the question it is asking rather than as the mechanics of answering it.
A function defined with let exists only for the query it is in. Sentinel and Defender both let a function be saved to the workspace, so it can be called from any query, rule or workbook by name.
That is how Microsoft ships its own reusable logic, including the ASIM parsers that present many sources in one schema, and it is the subject of Module 7's lesson on parsers as functions.
Parameters have types, and the type is part of the contract. IsCorporate takes a string, so it can be called with any string column, and it fails clearly if handed a number. Functions can also take several parameters and give some of them default values, which lets a single function serve the common case simply and the unusual case with one extra argument.
The section has a flaw worth noticing before moving on: the NAT pool is still written into the function. Section 05 shows what that costs.
A Function Over a Table
invoke passes the whole tableFunctions can take tables as well as values. A tabular parameter is declared with the columns the function needs, and the function can then do anything a query can do with them. Here is the month's failure rate as a function that works on any table with a ResultType column:
let FailureRate = (T: (ResultType: string)) {
T
| summarize Percent = round(100.0 * countif(ResultType != '0') / count(), 1)
};
SigninLogs
| invoke FailureRate()
invoke passes the table on its left into the function as T, so the same function can be pointed at any sign-in table:
Percent
-------
4.1
4.1 percent of interactive sign-ins failed. Pointed at the non-interactive sign-ins instead, the same function returns 0 percent, with nothing changed but the table name. The calculation, including the 100.0 that keeps the division real, is written once and cannot be got wrong the second time.
The column list in the parameter, (ResultType: string), is a contract: the function says what it needs, and a table without that column is refused with an error that names it. That makes tabular functions safe to share. A colleague can call FailureRate on a table they have built without reading its body, because the signature says what it requires.
Tabular functions are how the larger reusable pieces of a query library are built. A function that takes any process table and returns its parent-child pairs, one that takes any sign-in table and returns one row per user per day, one that takes any network table and classifies its traffic: each is a step that many questions start with, written once and called by name.
Module 7 builds several, and the Project at the end of the course builds a library of them on your own data.
The difference between this and a named result is that a function waits to be given its input. Failed in Section 02 was always the interactive failures; FailureRate is whatever table is handed to it, which is what makes it reusable across tables rather than only within one query.
A List Kept Outside the Query
A watchlist, maintained by its ownerIsCorporate held the NAT pool as a range written into its body. Lists of addresses, accounts and hosts change more often than queries do, and Sentinel keeps them as watchlists: tables maintained outside any query, by whoever owns the list, and read with _GetWatchlist(). The lab has one for the company's egress addresses:
_GetWatchlist('CorporateEgressIPs')
| summarize Addresses = count() by Description
The watchlist is read like a table, and the summarize counts its addresses by what each is for:
Description Addresses
------------------------------------------------- ---------
NAT egress pool (FW-NGE-MCR-01, all sites) 9
Always On VPN egress (Azure North Europe, Dublin) 1
10 addresses: 9 in the NAT pool and one VPN egress. The VPN address is not in the range the function used. Count the month's sign-ins against the watchlist, and split them by whether the range would have caught them:
let Egress = _GetWatchlist('CorporateEgressIPs')
| project IPAddress;
SigninLogs
| where IPAddress in (Egress)
| extend InPool = ipv4_is_in_range(IPAddress, '203.0.113.32/28')
| summarize SignIns = count() by Source = iff(InPool, 'NAT pool', 'Other')
The let holds the watchlist's addresses, the where keeps sign-ins from any of them, and the range test splits them:
Source SignIns
-------- -------
NAT pool 7456
Other 99
7,456 sign-ins came from the NAT pool, exactly the number the function counted, and 99 more came from the VPN egress, which the hard-coded range missed.
Any detection that used the range would have treated every one of those 99 as a sign-in from outside the company. The watchlist did not need to be updated for this query; it already held the address, because the network team that owns the VPN maintains it.
Watchlists are not the only list kept outside a query. Threat intelligence arrives in its own tables, reference data can be read from storage with externaldata, and a small fixed list can be written into the query itself with datatable when it really never changes. Module 7 covers each, and the test for choosing between them is the same: who changes the list, and how will the query find out.
That is the case for keeping lists outside queries. A list in a watchlist has an owner, a place to be reviewed and one copy, and every query that reads it reads the list as it stands when the query runs.
A list typed into a query has none of those, and it is wrong from the day the real list changes until somebody notices. A list also says only what it says: an address on it belongs to the company, which is context about the address, not a verdict about who used it.
Address Functions and Ranges
Private, public and in rangeAddresses are strings in most tables, and string operators handle them badly: 10.0.1.1 starts with 10.0.1.1, and so does 10.0.1.100.
KQL has functions that understand addresses as addresses. ipv4_is_private() says whether an address is in one of the private ranges, and ipv4_is_in_range() whether it falls inside a range written in CIDR notation, such as the /28 the function used. Applied to the firewall's traffic logs, the first tells outbound traffic from inbound:
CommonSecurityLog
| where Activity == 'TRAFFIC log'
| summarize Events = count(), Sources = dcount(SourceIP),
Destinations = dcount(DestinationIP)
by Direction = iff(ipv4_is_private(SourceIP), 'Outbound', 'Inbound')
Traffic is split by whether its source is private, with the events, sources and destinations in each direction:
Direction Events Sources Destinations
--------- ------ ------- ------------
Outbound 1573 84 13
Inbound 441 6 2
1,573 outbound events from 84 internal sources to 13 destinations, and 441 inbound from 6 external sources. The direction is not a column of the table; it was derived from the source address with a function that knows what a private address is, and the derivation is exact in a way no string comparison could be.
CIDR notation names a range by its first address and a prefix length: 203.0.113.32/28 is the 16 addresses from 203.0.113.32 to 203.0.113.47. ipv4_is_in_range() tests membership arithmetically, so the boundary is never a matter of how many characters match. The same family has functions for comparing addresses, for matching against several ranges at once, and for IPv6, which Module 7's lesson on address functions covers.
Address functions belong in the functions of Section 03 rather than scattered through queries. A team that has one IsCorporate, built on these functions and a watchlist, has one definition of its own network. A team that has a range typed into fifty queries has fifty definitions that will disagree within a year.
The firewall table is also a reminder that the same address can mean different things in different columns, which no function can decide for you. A private source is an internal device talking out; a private destination is something internal being reached. The function says whether an address is private; the column says what role it played.
Naming for the Next Reader
Values, results, logic and listsLook back over the lesson as a whole. Every query in it returned the same answers it would have returned with everything typed inline, except one: the range missed 99 sign-ins that the watchlist did not. That one difference is the reason the rest matters.
Named values, results and functions do not change answers; they make the decisions inside a query visible and changeable, so that when a decision is wrong, like the range, it can be found and fixed in one place.
Here is the full picture of where each kind of logic belongs, from a number in one query to a list the whole organization maintains:
The rungs are ordered by how many people rely on what sits there. A let value affects one query; a saved function affects every query that calls it, and a watchlist affects every rule that reads it.
The further down the ladder, the more a change needs an owner, a review and a record, which is why Module 7 ends with indicators and address ranges, the lists that change most often.
Each of the mistakes below is a value that should have been named, or a list that should have lived elsewhere:
The first row is this lesson's finding in one line. It is also the most common way detections go stale in real workspaces: not because their logic was wrong when written, but because a list inside them stopped matching the world.
What Module 7 builds
Module 7Module 7 builds on this lesson: named values and thresholds, named results, your own functions, parsers as functions, watchlists, external data, indicators in every format and address functions. The habits to carry into it: name every threshold and window at the top, name results you reuse, write logic once as a function, keep lists where their owners maintain them, and treat a list as context, never as a verdict.
Practice
Choose the definition a detection should use prepared evidenceThe prepared evidence below is the month's sign-ins counted against two definitions of corporate: the hard-coded NAT range and the watchlist the network team maintains. Find the sign-ins the range misses, choose the definition a detection should use, and say what a match on the list does and does not mean. Try each question before opening its answer, and say which row decided it.
Read the rows as a reconciliation before answering. The second and fourth rows together make the third, and the third and fifth together make the first. If they did not, one of the definitions would be counting something the question did not ask about, and that would be the first thing to resolve.
The questions are about definitions rather than queries. Both definitions are valid KQL and both run; the difference between them is where the knowledge of what is corporate lives, and who keeps it current. That is the decision this module's lessons all lead to.
Then run the queries on this page and change them. Lower MinFailures to 5 and watch the count rise, then point FailureRate at AADSignInEventsBeta, the Defender sign-in table, and see what its different column names do to the call.
Name thresholds and windows at the top, name results that are reused, write repeated logic once as a function, keep changing lists in watchlists their owners maintain, and use address functions rather than string operators on addresses. A named decision can be found and fixed; an inline one waits to be discovered wrong.
The next lesson works with time and order: counts over time that keep their empty periods, this week against last, and the row before and after.