Sentinel – mainri

KQL query map SQL query

To those whom are familiar SQL query syntax, but new to KQL. The following table shows sample queries in SQL and their KQL equivalents.

Category	SQL Query	Kusto Query	Learn more
Select data from table	`SELECT * FROM dependencies`	`dependencies`	Tabular expression statements
—	`SELECT name, resultCode FROM dependencies`	`dependencies \| project name, resultCode`	project
—	`SELECT TOP 100 * FROM dependencies`	`dependencies \| take 100`	take
Null evaluation	`SELECT * FROM dependencies` `WHERE resultCode IS NOT NULL`	`dependencies` `\| where isnotnull(resultCode)`	isnotnull()
Comparison operators (date)	`SELECT * FROM dependencies` `WHERE timestamp > getdate()-1`	`dependencies` `\| where timestamp > ago(1d)`	ago()
—	`SELECT * FROM dependencies` `WHERE timestamp BETWEEN ... AND ...`	`dependencies` `\| where timestamp between (datetime(2016-10-01) .. datetime(2016-11-01))`	between
Comparison operators (string)	`SELECT * FROM dependencies` `WHERE type = "Azure blob"`	`dependencies` `\| where type == "Azure blob"`	Logical operators
—	`-- substring` `SELECT * FROM dependencies` `WHERE type like "%blob%"`	`// substring` `dependencies` `\| where type has "blob"`	has
—	`-- wildcard` `SELECT * FROM dependencies` `WHERE type like "Azure%"`	`// wildcard` `dependencies` `\| where type startswith "Azure"` `// or` `dependencies` `\| where type matches regex "^Azure.*"`	`startswith` matches regex
Comparison (boolean)	`SELECT * FROM dependencies` `WHERE !(success)`	`dependencies` `\| where success == False`	Logical operators
Grouping, Aggregation	`SELECT name, AVG(duration) FROM dependencies` `GROUP BY name`	`dependencies` `\| summarize avg(duration) by name`	summarize avg()
Distinct	`SELECT DISTINCT name, type FROM dependencies`	`dependencies` `\| summarize by name, type`	summarize distinct
—	`SELECT name, COUNT(DISTINCT type)` `FROM dependencies` `GROUP BY name`	`dependencies` `\| summarize by name, type \| summarize count() by name` `// or approximate for large sets` `dependencies` `\| summarize dcount(type) by name`	count() dcount()
Column aliases, Extending	`SELECT operationName as Name, AVG(duration) as AvgD FROM dependencies` `GROUP BY name`	`dependencies` `\| summarize AvgD = avg(duration) by Name=operationName`	Alias statement
—	`SELECT conference, CONCAT(sessionid, ' ' , session_title) AS session FROM ConferenceSessions`	`ConferenceSessions` `\| extend session=strcat(sessionid, " ", session_title)` `\| project conference, session`	strcat() project
Ordering	`SELECT name, timestamp FROM dependencies` `ORDER BY timestamp ASC`	`dependencies` `\| project name, timestamp` `\| sort by timestamp asc nulls last`	sort
Top n by measure	`SELECT TOP 100 name, COUNT(*) as Count FROM dependencies` `GROUP BY name` `ORDER BY Count DESC`	`dependencies` `\| summarize Count = count() by name` `\| top 100 by Count desc`	top
Union	`SELECT * FROM dependencies` `UNION` `SELECT * FROM exceptions`	`union dependencies, exceptions`	union
—	`SELECT * FROM dependencies` `WHERE timestamp > ...` `UNION` `SELECT * FROM exceptions` `WHERE timestamp > ...`	`dependencies` `\| where timestamp > ago(1d)` `\| union` `(exceptions` `\| where timestamp > ago(1d))`
Join	`SELECT * FROM dependencies` `LEFT OUTER JOIN exceptions` `ON dependencies.operation_Id = exceptions.operation_Id`	`dependencies` `\| join kind = leftouter` `(exceptions)` `on $left.operation_Id == $right.operation_Id`	join
Nested queries Sub-query	`SELECT * FROM dependencies` `WHERE resultCode ==` `(SELECT TOP 1 resultCode FROM dependencies` `WHERE resultId = 7` `ORDER BY timestamp DESC)`	`dependencies` `\| where resultCode == toscalar(` `dependencies` `\| where resultId == 7` `\| top 1 by timestamp desc` `\| project resultCode)`	toscalar
Having	`SELECT COUNT(\) FROM dependencies` `GROUP BY name` `HAVING COUNT(\) > 3`	`dependencies` `\| summarize Count = count() by name` `\| where Count > 3`	summarize where

Please do not hesitate to contact me if you have any questions at William . chen @ mainri.ca

(remove all space from the email account 😊)

Kusto Query Language (KQL) – quick reference

It is for the new KQL engineer to quick reference only.

KQL Work Flow

Quick reference

Operator/Function	Description	Syntax
Filter/Search/Condition	*Find relevant data by filtering or searching*
where	Filters on a specific predicate	`T \| where Predicate`
where contains/has	`Contains`: Looks for any substring match `Has`: Looks for a specific word (better performance)	`T \| where col1 contains/has "[search term]"`
search	Searches all columns in the table for the value	`[TabularSource \|] search [kind=CaseSensitivity] [in (TableSources)] SearchPredicate`
take	Returns the specified number of records. Use to test a query *Note*: `take` and `limit` are synonyms.	`T \| take NumberOfRows`
case	Adds a condition statement, similar to if/then/elseif in other systems.	`case(predicate_1, then_1, predicate_2, then_2, predicate_3, then_3, else)`
distinct	Produces a table with the distinct combination of the provided columns of the input table	`distinct [ColumnName], [ColumnName]`
Date/Time	*Operations that use date and time functions*
ago	Returns the time offset relative to the time the query executes. For example, `ago(1h)` is one hour before the current clock’s reading.	`ago(a_timespan)`
format_datetime	Returns data in various date formats.	`format_datetime(datetime , format)`
bin	Rounds all values in a timeframe and groups them	`bin(value,roundTo)`
Create/Remove Columns	*Add or remove columns in a table*
print	Outputs a single row with one or more scalar expressions	`print [ColumnName =] ScalarExpression [',' ...]`
project	Selects the columns to include in the order specified	`T \| project ColumnName [= Expression] [, ...]` Or `T \| project [ColumnName \| (ColumnName[,]) =] Expression [, ...]`
project-away	Selects the columns to exclude from the output	`T \| project-away ColumnNameOrPattern [, ...]`
project-keep	Selects the columns to keep in the output	`T \| project-keep ColumnNameOrPattern [, ...]`
project-rename	Renames columns in the result output	`T \| project-rename new_column_name = column_name`
project-reorder	Reorders columns in the result output	`T \| project-reorder Col2, Col1, Col* asc`
extend	Creates a calculated column and adds it to the result set	`T \| extend [ColumnName \| (ColumnName[, ...]) =] Expression [, ...]`
Sort and Aggregate Dataset	*Restructure the data by sorting or grouping them in meaningful ways*
sort operator	Sort the rows of the input table by one or more columns in ascending or descending order	`T \| sort by expression1 [asc\|desc], expression2 [asc\|desc], …`
top	Returns the first N rows of the dataset when the dataset is sorted using `by`	`T \| top numberOfRows by expression [asc\|desc] [nulls first\|last]`
summarize	Groups the rows according to the `by` group columns, and calculates aggregations over each group	`T \| summarize [[Column =] Aggregation [, ...]] [by [Column =] GroupExpression [, ...]]`
count	Counts records in the input table (for example, T) This operator is shorthand for `summarize count()`	`T \| count`
join	Merges the rows of two tables to form a new table by matching values of the specified column(s) from each table. Supports a full range of join types: `fullouter`, `inner`, `innerunique`, `leftanti`, `leftantisemi`, `leftouter`, `leftsemi`, `rightanti`, `rightantisemi`, `rightouter`, `rightsemi`	`LeftTable \| join [JoinParameters] ( RightTable ) on Attributes`
union	Takes two or more tables and returns all their rows	`[T1] \| union [T2], [T3], …`
range	Generates a table with an arithmetic series of values	`range columnName from start to stop step step`
Format Data	*Restructure the data to output in a useful way*
lookup	Extends the columns of a fact table with values looked-up in a dimension table	`T1 \| lookup [kind = (leftouter\|inner)] ( T2 ) on Attributes`
mv-expand	Turns dynamic arrays into rows (multi-value expansion)	`T \| mv-expand Column`
parse	Evaluates a string expression and parses its value into one or more calculated columns. Use for structuring unstructured data.	`T \| parse [kind=regex [flags=regex_flags] \|simple\|relaxed] Expression with * (StringConstant ColumnName [: ColumnType]) *...`
make-series	Creates series of specified aggregated values along a specified axis	`T \| make-series [MakeSeriesParamters] [Column =] Aggregation [default = DefaultValue] [, ...] on AxisColumn from start to end step step [by [Column =] GroupExpression [, ...]]`
let	Binds a name to expressions that can refer to its bound value. Values can be lambda expressions to create query-defined functions as part of the query. Use `let` to create expressions over tables whose results look like a new table.	`let Name = ScalarExpression \| TabularExpression \| FunctionDefinitionExpression`
General	*Miscellaneous operations and function*
invoke	Runs the function on the table that it receives as input.	`T \| invoke function([param1, param2])`
evaluate pluginName	Evaluates query language extensions (plugins)	`[T \|] evaluate [ evaluateParameters ] PluginName ( [PluginArg1 [, PluginArg2]... )`
Visualization	*Operations that display the data in a graphical format*
render	Renders results as a graphical output	`T \| render Visualization [with (PropertyName = PropertyValue [, ...] )]`

Please do not hesitate to contact me if you have any questions at William . chen @ mainri.ca

(remove all space from the email account 😊)