CyberKit

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.

KQL cheat sheet
SyntaxMeaning
Table | operator | operatorA 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 | countCounts the records in the input table. Shorthand for `summarize count()`.
T | take NReturns 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.
range Name from A to B step SGenerates a single-column table holding an arithmetic series. Useful as a scaffold to fill gaps in a timeline.
T | render VisualizationRenders the result set as a chart instead of a table. Purely a presentation step; it does not change the rows.
T | where PredicateFilters to the rows satisfying a predicate. Put your time filter first and your most selective predicate next.
and / orLogical 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, ColProduces 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.
hasMatches 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.
containsMatches 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_csThe case-sensitive forms of `has` and `contains`. Faster than their case-insensitive counterparts when the casing is known.
!has / !containsNegated 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 / endswithCase-insensitive prefix and suffix match on the whole value. `startswith_cs` / `endswith_cs` are the case-sensitive forms.
hasprefix / hassuffixTerm-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 regexCase-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 TimeGeneratedDefender 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 msTimespan 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, ColSelects 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 PatternDrops the named columns (wildcards allowed) and keeps everything else: the inverse of `project` when you only want to lose a few.
T | project-keep PatternKeeps the named columns (wildcards allowed) without having to spell out an explicit order.
T | project-rename New = OldRenames a column in the output while leaving the rest of the schema alone.
T | project-reorder Col, ColReorders columns in the output without dropping any: the readable way to put the columns you care about on the left.
T | extend New = ExprAdds a calculated column to the result set, keeping every existing column.
T | sort by Col descSorts the rows of the input table by one or more columns, ascending or descending.
T | top N by ExprReturns the first N rows after sorting by the expression. One operator instead of `sort` followed by `take`, and cheaper.
T | limit NCaps the number of rows returned. A synonym for `take`, and no ordering is implied by either.
T | summarize Agg by GroupGroups 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 TimestampFirst-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 AxisBuilds 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 ColMerges two tables on matching column values. If the column has the same name on both sides, `on ColumnName` is enough.
on $left.A == $right.BJoin condition when the key columns have different names on each side. Comma-separate several conditions to AND them.
kind=inneruniqueThe 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 / fullouterOuter joins. Cells with no match on the other side come back null.
kind=leftantiReturns 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 / rightsemiReturns 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, T2Returns 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 ColEnriches 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 leftMicrosoft'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.
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 ColExpands 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.
DeviceProcessEventsProcess 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.
DeviceNetworkEventsNetwork connection and related events: RemoteIP, RemotePort, RemoteUrl, LocalIP/LocalPort and the initiating process.
DeviceFileEventsFile creation, modification and other file-system events. Carries FileOriginUrl and FileOriginIP, which is how you attribute a dropped file to its download.
DeviceRegistryEventsCreation and modification of registry entries: RegistryKey, RegistryValueName, RegistryValueData plus the previous values.
DeviceLogonEventsSign-ins and other authentication events on devices: LogonType, AccountName, RemoteIP, FailureReason and IsLocalAdmin.
DeviceImageLoadEventsDLL loading events. The table to reach for when you are chasing side-loading or an unsigned module inside a signed host process.
DeviceEventsA catch-all for many event types, including those raised by security controls such as Defender Antivirus and exploit protection. Filter it by ActionType.
DeviceInfo / DeviceNetworkInfoMachine 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 / AlertEvidenceAlerts 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.
IdentityLogonEventsAuthentication events on Active Directory and Microsoft online services. `IdentityDirectoryEvents` covers on-premises domain-controller events, `IdentityQueryEvents` covers AD object queries.
EmailEvents / EmailAttachmentInfo / EmailUrlInfoMicrosoft 365 email delivery and blocking events, plus the attachments and the URLs found in those messages. `UrlClickEvents` records Safe Links clicks.
CloudAppEventsEvents involving accounts and objects in Office 365 and other connected cloud apps and services.
EntraIdSignInEventsMicrosoft 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.

Related

All references