Posted on by William Chen

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.

CategorySQL QueryKusto QueryLearn more
Select data from tableSELECT * FROM dependenciesdependenciesTabular expression statements
—SELECT name, resultCode FROM dependenciesdependencies | project name, resultCodeproject
—SELECT TOP 100 * FROM dependenciesdependencies | take 100take
Null evaluationSELECT * 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, AggregationSELECT name, AVG(duration) FROM dependencies
GROUP BY name		dependencies
| summarize avg(duration) by name		summarize
avg()
DistinctSELECT DISTINCT name, type FROM dependenciesdependencies
| 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, ExtendingSELECT 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 ConferenceSessionsConferenceSessions
| extend session=strcat(sessionid, " ", session_title)
| project conference, session		strcat()
project
OrderingSELECT name, timestamp FROM dependencies
ORDER BY timestamp ASC		dependencies
| project name, timestamp
| sort by timestamp asc nulls last		sort
Top n by measureSELECT 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
UnionSELECT * FROM dependencies
UNION
SELECT * FROM exceptions		union dependencies, exceptionsunion
—SELECT * FROM dependencies
WHERE timestamp > ...
UNION
SELECT * FROM exceptions
WHERE timestamp > ...		dependencies
| where timestamp > ago(1d)
| union
(exceptions
| where timestamp > ago(1d))
JoinSELECT * 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
HavingSELECT COUNT(\*) FROM dependencies
GROUP BY name
HAVING COUNT(\*) > 3		dependencies
| summarize Count = count() by name
| where Count > 3		summarize
where
Proudly powered by WordPress