KQL Cheat Sheet v0.2.2 github.com/marcusbakker/KQL/blob/master/kql_cheat_sheet.

pdf Bakk3rM
The purpose of this cheat sheet is to cover essential basics for the Kusto Query Language (KQL). The majority of the queries from this
cheat sheet will run on the SecurityEvent table: accessible via https://portal.loganalytics.io/demo. In the queries below, the table
SecurityEvent is abbreviated by T. Many of the KQL functions and operators below link back the official KQL documentation.
The example queries only have the purpose of explaining KQL and may stop providing results due to changes in the data on the Log Analytics demo portal.

Generic String search

A string literal with a backslash requires escaping by a backslash: Search across all tables and columns: search "*KEYWORD*"
"a string literal with a \\ needs to be escaped" • Keep in mind that this is a performance intensive operation.

The same can be achieved using a verbatim string literal by

Search for a specific value: T | where ProcessName ==
putting the @ sign in front: @"a verbatim string literal with
@"C:\Windows\System32\regsvr32.exe"
a \ that does not need to be escaped"

More info on escaping string data types can be found here. A not equal to match is done by adding an exclamation mark
as prefix:
Add comments to your query with a double forward slash: • Equal to: ==
// This is a comment • Not equal to: !=

The where operator and the pipe ( | ) delimiter are essential in This is also supported in a similar way for other string operators.
writing KQL queries.
A case insensitive match can be achieved using a tilde:
where is used to filter rows from a table. In this example we filter on events • Case sensitive: ==
from a source, the table SecurityEvent, where the column Computer is • Case insensitive: =~
equal to "ContosoAppSrv1", and count the number of results: • Case insensitive and not equal to: !~
SecurityEvent | where Computer == "ContosoAppSrv1" | count
This is also supported in a similar way for other string operators.
The pipe is used to separate data transformation operators. Such as:
where Computer == "ContosoAppSrv1" . The result can be piped to a new Match on values that contain a specific string:
operator. For example, to count the number for rows: | count T | where CommandLine contains "guest"

Only include events from the last 24 hours using the ago() Because has is more performant, it’s advised to use has over contains
function: T | where TimeGenerated > ago(24h) when searching for full keywords. The following expression yields to true:
• "North America" has "america"
For performance reasons always use time filters first in your query.
contains and has are case insensitive by default. A case sensitive match
can be achieved by adding the suffix _cs: contains_cs / has_cs
The ago() function supports multiple types of timespans. More info can be
found here. For example:
• 1d 1 day Match on values starting with or ending with a specific string:
• 10m 10 minutes T | where Computer startswith "contoso"
• 30s 30 seconds • Ending with a specific string: endswith

startswith and endswith are case insensitive by default. A case sensitive

Include events that occurred between a specific timeframe:
match can be achieved by adding the suffix _cs: startswith_cs /
T | where TimeGenerated between(datetime(2019-11-01
endswith_cs
00:00:00) .. datetime(2019-11-01 06:00:00))

Select and customize the columns from the resulting table of your Match on multiple string values: T | where Computer in
("ContosoAppSrv1", "ContosoSQLSrv1")
query with the project operator.
• Not equal to: !in
• Specify the columns to include:
• Case insensitive: in~
T | project TimeGenerated, EventID, Account,
• Case insensitive and not equal to: !in~
Computer, LogonType

• Rename columns. In this example we renamed the column Match based on a regular expression: T | where Computer
Account to UserName: matches regex "^Contoso.*"
T | project TimeGenerated, EventID, UserName =
Account, Computer, LogonType • KQL uses the re2 library and also complies with that syntax.
Troubleshooting your regex can be done on regex101.com. Select
• Remove columns with project-away: the regex Flavor “Golang” which also makes use of re2.
T | project-away EventSourceName, Task, Level
A not equal to match can be done using the not() function:
Add calculated columns to the result using the extend operator:
T | where not(Computer matches regex "^Contoso.*")
T | extend EventAge=now()-TimeGenerated

Count the number of records using the count operator: A case insensitive match can be achieved by providing the i flag:
T | count T | where Computer matches regex "(?i)^contoso.*"
 Generic Named expressions and user-defined functions
Match based on conditions using logical operators. For example: Use the let statement to bind names to expressions. See below
• T | where EventID == 4624 and LogonType == 3 two examples of a named expression. Of course, much more complex
• T | where EventID == 4624 or EventID == 4625 expression can be created. Such as complete queries that can be nested
• T | where (EventID == 4624 and LogonType == 3) or inside another query (i.e. sub-query). For sub-queries consider the use of
EventID == 4625 the materialize() function when the sub-query is called multiple times.

Aggerate results from your query with the summarize operator: Take into account the semicolon at the end of the let statement:
• let _SearchWindow = ago(24h);
• Aggregate on multiple columns:
T | where TimeGenerated > _SearchWindow
T | summarize by Computer, Account
• let _computers = dynamic(["ContosoAppSrv1",
• Aggregate on multiple columns and return the count of the "ContosoSQLSrv1"]);
group: T | summarize count() by Computer, Account T | where Computer in (_computers)

The let statement can be used in many other useful ways. Such
Besides count() many more very useful aggregation functions exist. An
as to create user-defined functions. More info on the let
overview can be found here.
statement can be found here.
Sort the rows of the result using the sort operator:
Visualizations
T | where EventID == 4624 | summarize count() by
AuthenticationPackageName | sort by count_
The render operator can be used to create visualizations. Besides
the below example, more types of visualizations are possible.
By default, rows are sorted in descending order. Sorting in ascending order More info can be found here.
is also possible:
• sort by count_ asc pageViews |
• Descending order: desc summarize total_page_views=count() by name |
sort by total_page_views | render columnchart
Concatenate values. The result will be a string data type:
T | project example=strcat(EventID, " - ", Channel)

A variable number of values can be passed through the strcat function. If

values are not a string, they will be forcibly converted to a string.

Numerical search
Search for a specific value: T | where EventID == 4688
• Not equal to: !=

All of the numerical operators can be found here.

Search for a value less or greater than: T | where EventID ==

4688 | summarize count() by Process | where count_ < 5
Join tables
• Greater: >
• Less or Equal: <= KQL has the ability to join tables. In this example, we join some of
• Greater or Equal: >= the events in the SecurityAlert table with process creation
events (event ID 4688) from the SecurityEvent table. More
information on joining tables can be found here.
Match on multiple numeric values:
T | where EventID in (4624, 4625)
This query serves purely as an example to explain the join operator because all
process data is contained within the column Entities of the SecurityAlert table.
Extract values
Extract values from a string or JSON data. For example, extract the
SecurityAlert |
“process name” using a regular expression (if you are less familiar with
extend _ProcessId = extractjson("$.process id",
regular expressions have a look at the split and parse function):
ExtendedProperties),
SecurityAlert | extend _ProcessName=extract('"process _ProcessCommandLine = tolower(extractjson("$.command
name": "(.*)"', 1, ExtendedProperties) line", ExtendedProperties)),
_HostName = tolower(extractjson("$[0].HostName",
Because the column ExtendedProperties contains JSON data you Entities))
can also use the function extractjson(): | join kind=inner (
SecurityAlert | extend _ProcessName = SecurityEvent
extractjson("$.process name", ExtendedProperties) | where EventID == 4688
| extend _HostName=tolower(Computer)
If you need to extract multiple elements from JSON data, stored as a
| extend _ProcessCommandLine=tolower(CommandLine)
string, you can use the function parse_json(). Use the dot notation if
) on $left._ProcessId == $right.NewProcessId,
the data is of the type dictionary or a list of dictionaries in an array. _HostName, _ProcessCommandLine
One way to find out is through the gettype() function. To play with data
stored as a dictionary have a look at the help cluster in the Azure Data
Explorer (table: StormEvents, column: StormSummary).