KQL Cheat Sheet
Kusto syntax for hunting in Defender XDR and Sentinel: operators, time windows, joins, and aggregation.
KQL runs Defender XDR advanced hunting, Microsoft Sentinel, Azure Data Explorer and Azure Monitor. One grammar, four surfaces.
Before you read the table
- Everything is case-sensitive: tables, columns, operators and functions.
- Defender tables use Timestamp. Sentinel and Azure Monitor tables use TimeGenerated. A query that returns nothing across products is usually one of these two things.
117 rows.
| Syntax | Meaning |
|---|---|
| Table | operator | operator | A query is a tabular data source followed by operators chained with the pipe character. Each operator takes a table in and emits a table out, so order matters for both results and performance. |
| // | Line comment. Worth putting at the top of anything you intend to save or hand to someone else. Microsoft's own sample queries lead with one. |
| let Name = ...; | Binds a name to a scalar, a tabular expression, or a function definition, for reuse later in the same query. Statements are separated by a semicolon. |
| ['keyword'] | KQL is case-sensitive for everything: table names, column names, operators and functions. A keyword can still be used as an identifier by wrapping it in brackets and quotes. |
| T | count | Counts the records in the input table. Shorthand for `summarize count()`. |
| T | take N | Returns the specified number of records, with no ordering guarantee. Use it to sanity-check a query cheaply before you widen it. `take` and `limit` are synonyms. |
| print Expression | Outputs a single row of one or more scalar expressions, handy for testing a function before you wire it into a query. |
| range Name from A to B step S | Generates a single-column table holding an arithmetic series. Useful as a scaffold to fill gaps in a timeline. |
| T | render Visualization | Renders the result set as a chart instead of a table. Purely a presentation step; it does not change the rows. |
| T | where Predicate | Filters to the rows satisfying a predicate. Put your time filter first and your most selective predicate next. |
| and / or | Logical conjunction and disjunction inside a single `where`. Chaining separate `where` clauses is equivalent to `and` and often reads better. |
| == != < <= > >= | Comparison operators. On strings `==` and `!=` are case-sensitive; use `=~` / `!~` for the case-insensitive forms. |
| in (...) / !in (...) | Equals (or does not equal) any element of a list. Case-sensitive: the `in~` / `!in~` variants are the case-insensitive forms. |
| between (A .. B) | Matches a value inside an inclusive range: the readable way to express a fixed investigation window rather than a rolling one. |
| search "term" | Searches every column of the table (or of several tables) for a value. Convenient for a first pass on an unfamiliar schema, and far slower than a targeted `where`. |
| T | distinct Col, Col | Produces the distinct combinations of the named columns: the fastest way to answer "which hosts/accounts/binaries are even involved here?". |
| isempty() / isnotempty() | True when a string argument is empty or null / neither empty nor null. Guard clauses for columns that are only populated on some event types. |
| isnull() / isnotnull() | True when the argument is null / is not null. Use these for non-string columns, where `isempty()` does not apply. |
| ipv4_is_private(IP) | True when an IPv4 string belongs to a private network range. The clean way to strip internal traffic out of an egress hunt. |
| ipv4_is_in_range(IP, "CIDR") | True when an IPv4 string falls inside a prefix in CIDR notation. Use `ipv4_is_in_any_range()` to test against several prefixes at once. |
| has_ipv4(Text, "IP") | True when a free-text column contains the given IPv4 address. Index-accelerated, so it beats a regex over a command line or URL. |
| == | Case-sensitive equality. Prefer it over `=~` when you know the exact casing: the case-sensitive form is the faster one. |
| =~ | Case-insensitive equality. Use it when the telemetry casing is inconsistent, which on Windows paths and account names it usually is. |
| != / !~ | Case-sensitive and case-insensitive inequality. |
| has | Matches a whole TERM (a maximal alphanumeric sequence of three characters or more) not an arbitrary substring. Term-indexed, so it is the fastest string operator here. |
| contains | Matches an arbitrary substring, case-insensitively. It cannot use the term index, so it scans. Reach for `has` first and fall back to `contains` only when the fragment you want is not a whole term. |
| has_cs / contains_cs | The case-sensitive forms of `has` and `contains`. Faster than their case-insensitive counterparts when the casing is known. |
| !has / !contains | Negated term match and negated substring match: the usual shape of an allow-list exclusion. |
| has_any (...) | True when ANY element of the list is a term in the value. One clause instead of a long chain of `or`s. |
| has_all (...) | True when EVERY element of the list is a term in the value, for command lines that must contain several markers together. |
| startswith / endswith | Case-insensitive prefix and suffix match on the whole value. `startswith_cs` / `endswith_cs` are the case-sensitive forms. |
| hasprefix / hassuffix | Term-level prefix and suffix match, which matches the start or end of any TERM in the value, not just the start or end of the whole string. `_cs` variants are case-sensitive. |
| matches regex | Case-sensitive regular-expression match against the right-hand side. The heaviest option on this list. Use it only when nothing term- or substring-based will do. |
| in~ (...) / !in~ (...) | Case-insensitive membership test against a list of literals. |
| ago(timespan) | Subtracts a timespan from the current UTC clock. The first filter in almost every hunting query, because scoping the time range is what keeps a query fast and finite. |
| Timestamp vs TimeGenerated | Defender XDR advanced hunting tables carry the event time in `Timestamp`; Azure Monitor / Sentinel workspace tables carry it in `TimeGenerated`. Getting this wrong is the most common reason a cross-product query silently returns nothing. |
| d h m s ms | Timespan literals: a bare number followed by a unit. Combine them with `ago()`, `bin()` and arithmetic on datetime columns. |
| now() | The current UTC clock time, optionally offset by a timespan. Kusto time filters are always UTC regardless of your portal timezone setting. |
| datetime(YYYY-MM-DD) | A datetime literal. Use it for a fixed incident window that must not move as the query is re-run. |
| bin(Timestamp, 1h) | Rounds a value down to a multiple of the bin size. This is how you bucket a timeline. Automatic hourly binning of datetime columns is no longer implicit, so bin explicitly. |
| startofday() / endofday() | Snaps a datetime to the start or end of the containing day, optionally shifted by an offset. `startofweek`, `startofmonth` and `startofyear` follow the same shape. |
| datetime_diff(part, A, B) | Returns the difference between two datetimes as an integer count of the named date part. |
| format_datetime(dt, fmt) | Formats a datetime against a format pattern, useful when a report has to line up with an external timeline. |
| todatetime(value) | Converts a string or number to a datetime scalar. Needed whenever a timestamp arrived as text in a custom or parsed column. |
| unixtime_seconds_todatetime(n) | Converts Unix epoch seconds to a UTC datetime. Millisecond, microsecond and nanosecond variants exist under the same naming pattern. |
| hourofday() / dayofweek() | Extract the hour of the day, or the days elapsed since the preceding Sunday as a timespan. The building blocks of an "activity outside business hours" hunt. |
| T | project Col, Col | Selects the columns to include, in the order given, and can rename or compute them inline. Project early, carrying 60 columns through a pipeline you only need six of costs you. |
| T | project-away Pattern | Drops the named columns (wildcards allowed) and keeps everything else: the inverse of `project` when you only want to lose a few. |
| T | project-keep Pattern | Keeps the named columns (wildcards allowed) without having to spell out an explicit order. |
| T | project-rename New = Old | Renames a column in the output while leaving the rest of the schema alone. |
| T | project-reorder Col, Col | Reorders columns in the output without dropping any: the readable way to put the columns you care about on the left. |
| T | extend New = Expr | Adds a calculated column to the result set, keeping every existing column. |
| T | sort by Col desc | Sorts the rows of the input table by one or more columns, ascending or descending. |
| T | top N by Expr | Returns the first N rows after sorting by the expression. One operator instead of `sort` followed by `take`, and cheaper. |
| T | limit N | Caps the number of rows returned. A synonym for `take`, and no ordering is implied by either. |
| T | summarize Agg by Group | Groups rows by the `by` expressions and computes aggregations over each group. Without a `by` clause you get a single summary row. |
| count() | Counts rows in the group. Note that it counts nulls too, unlike most other aggregates. |
| countif(Predicate) | Counts only the rows in the group satisfying a predicate: the way to put two competing counts side by side in one pass. |
| dcount(Col) | An approximate distinct count of the group. `dcountif()` takes a predicate; `count_distinct()` is the exact (and more expensive) version. |
| sum() / avg() / min() / max() | The standard statistical aggregates. Each has an `if` variant (`sumif`, `avgif`, `minif`, `maxif`) that takes a predicate. Nulls are ignored. |
| make_set(Col) | Returns a dynamic array of the DISTINCT values in the group. The fastest way to collapse "everything this host talked to" into one readable cell. |
| make_list(Col) | Returns a dynamic array of ALL values in the group, duplicates included. Use it when frequency or order matters. |
| arg_max(Expr, *) | Returns the whole row (with `*`) where the expression is maximized. This is how you get the LATEST event per host rather than just its timestamp. `arg_min()` is the mirror image. |
| min() / max() on Timestamp | First-seen and last-seen bounds for a group: the two numbers that turn a pile of events into an activity window. |
| percentile(Col, N) | A percentile estimate over the group. `percentiles()` returns several at once. |
| take_any(Col) | Returns an arbitrary non-empty value from the group. Cheap when you just need one representative sample, not a full set. |
| summarize ... by bin(Timestamp, 1h) | Groups by a rounded time bucket, producing the row-per-interval shape a timeline or a `render timechart` expects. |
| T | make-series Agg on Axis | Builds evenly-spaced series along an axis, filling gaps with a default value: the input shape the time-series and anomaly functions expect. |
| T1 | join kind=inner (T2) on Col | Merges two tables on matching column values. If the column has the same name on both sides, `on ColumnName` is enough. |
| on $left.A == $right.B | Join condition when the key columns have different names on each side. Comma-separate several conditions to AND them. |
| kind=innerunique | The DEFAULT join flavor, and the one that surprises people: it de-duplicates the left side on the join key before matching. Say `kind=inner` explicitly when you want a standard inner join. |
| kind=leftouter / rightouter / fullouter | Outer joins. Cells with no match on the other side come back null. |
| kind=leftanti | Returns the left rows that DON'T match anything on the right, with the left schema only. The workhorse for "which hosts are missing X?" and for baseline subtraction. `anti` and `leftantisemi` are accepted spellings; `rightanti` is the mirror. |
| kind=leftsemi / rightsemi | Returns rows from one side that DO have a match on the other, keeping only that side's columns: a filter rather than a widening. |
| union T1, T2 | Returns the rows of two or more tables. `kind=inner` keeps only columns common to all inputs; `kind=outer` (the default) keeps every column. |
| union withsource=Col ... | Adds a column naming the source table each row came from. Essential once a union spans more than two tables. |
| T1 | lookup (T2) on Col | Enriches a fact table with columns from a dimension table. Semantically a `leftouter`/`inner` join, but optimized for the small-lookup-table case. |
| Put the SMALL table on the left | Microsoft's own performance guidance for `join`: when one side is consistently smaller, make it the left (outer) table. Filter each leg before joining rather than after. |
| iff(pred, a, b) | Two-branch conditional: returns the second argument when the predicate is true, otherwise the third. |
| case(p1, v1, p2, v2, else) | Multi-branch conditional: returns the value for the first satisfied predicate, else the final fallback. |
| coalesce(a, b, ...) | Returns the first non-null (or, for strings, non-empty) argument, useful for collapsing two columns that carry the same idea in different tables. |
| strcat(a, b, ...) | Concatenates between 1 and 64 arguments. `strcat_delim()` takes a delimiter as its first argument. |
| split(text, delimiter) | Splits a string on a delimiter into a dynamic array of substrings. |
| substring(text, start, len) | Extracts a substring from a zero-based start index. Omit the length to run to the end of the string. |
| tolower() / toupper() | Case normalization. Worth applying before a join or a `summarize by` so that two spellings of the same account do not split into two groups. |
| strlen(text) | Length of a string in characters. A long command line is a cheap, high-signal heuristic for encoded payloads. |
| indexof(text, lookup) | Zero-based index of the first occurrence of a substring, or -1 when it is absent. |
| countof(text, search) | Counts occurrences of a substring (plain matches may overlap) or of a regex (regex matches do not). |
| extract(regex, n, text) | Returns capture group n of the first regex match against the text: the compact alternative to a full `parse` when you want one field. |
| extract_all(regex, text) | Returns every regex match as a dynamic array, rather than only the first. |
| replace_string(text, old, new) | Replaces every plain-string match. `replace_regex()` does the same for a pattern; `replace_strings()` takes several pairs at once. |
| trim(regex, text) | Removes leading and trailing matches of a regex. `trim_start()` and `trim_end()` do one side only. |
| parse_command_line(text, parser) | Parses a command-line string into an array of arguments the way the OS would. Far more reliable than splitting on spaces once quoting is involved. |
| parse_url(text) | Parses an absolute URL into a dynamic object with its scheme, host, path, query parameters and port broken out. |
| url_decode(text) | Converts a percent-encoded URL back to its plain representation. `url_encode()` is the inverse. |
| base64_decode_tostring(text) | Decodes a base64 string to UTF-8. Note that PowerShell's own `-EncodedCommand` is UTF-16LE, so expect interleaved null bytes you still have to strip. |
| parse_json(text) / todynamic() | Interprets a string as JSON and returns it as a `dynamic` value you can index into with dot notation. |
| tostring() / toint() / tolong() | Type conversions. Filtering a `dynamic` property usually needs an explicit `tostring()` around it before a string operator will behave. |
| array_length(arr) | Number of elements in a dynamic array. `set_has_element()` tests membership; `array_index_of()` returns a position. |
| T | mv-expand Col | Expands a dynamic array into one row per element: the standard way to flatten a `make_set()` result, or an array you just parsed, back into rows. |
| T | parse Expr with ... | Parses a string column into new columns against a pattern, in simple, regex or relaxed mode. Use it to structure a free-text field you will be filtering on repeatedly. |
| hash_sha256() / hash_md5() | Hash functions over a scalar value. Useful for building a stable key from several columns, not for re-deriving a file hash from telemetry. |
| DeviceProcessEvents | Process creation and related events (Defender XDR). The starting point for parent/child chains and command-line hunting. Note that SHA256 is often unpopulated here. Prefer SHA1. |
| DeviceNetworkEvents | Network connection and related events: RemoteIP, RemotePort, RemoteUrl, LocalIP/LocalPort and the initiating process. |
| DeviceFileEvents | File creation, modification and other file-system events. Carries FileOriginUrl and FileOriginIP, which is how you attribute a dropped file to its download. |
| DeviceRegistryEvents | Creation and modification of registry entries: RegistryKey, RegistryValueName, RegistryValueData plus the previous values. |
| DeviceLogonEvents | Sign-ins and other authentication events on devices: LogonType, AccountName, RemoteIP, FailureReason and IsLocalAdmin. |
| DeviceImageLoadEvents | DLL loading events. The table to reach for when you are chasing side-loading or an unsigned module inside a signed host process. |
| DeviceEvents | A catch-all for many event types, including those raised by security controls such as Defender Antivirus and exploit protection. Filter it by ActionType. |
| DeviceInfo / DeviceNetworkInfo | Machine information including OS details, and the network properties of devices: adapters, IP and MAC addresses, connected networks and domains. These are the dimension tables you join a hunt against. |
| AlertInfo / AlertEvidence | Alerts across Defender for Endpoint, Office 365, Cloud Apps and Identity, with severity and threat categorization, and the files, IPs, URLs, users and devices attached to each alert. |
| IdentityLogonEvents | Authentication events on Active Directory and Microsoft online services. `IdentityDirectoryEvents` covers on-premises domain-controller events, `IdentityQueryEvents` covers AD object queries. |
| EmailEvents / EmailAttachmentInfo / EmailUrlInfo | Microsoft 365 email delivery and blocking events, plus the attachments and the URLs found in those messages. `UrlClickEvents` records Safe Links clicks. |
| CloudAppEvents | Events involving accounts and objects in Office 365 and other connected cloud apps and services. |
| EntraIdSignInEvents | Microsoft Entra interactive and non-interactive sign-ins. `EntraIdSpnSignInEvents` covers service-principal and managed-identity sign-ins. |
| SecurityEvent (Sentinel) | The Windows Security log forwarded into a Log Analytics workspace: EventID, Account, LogonType, NewProcessName, CommandLine. A workspace table, so it uses TimeGenerated, not Timestamp. |
| SigninLogs (Sentinel) | Microsoft Entra ID sign-in logs in a Log Analytics workspace: UserPrincipalName, IPAddress, ResultType, ConditionalAccessStatus, RiskLevelDuringSignIn. |