SQL to Kusto Query Language cheat sheet
Applies to: ✅ Microsoft Fabric ✅ Azure Data Explorer ✅ Azure Monitor ✅ Microsoft Sentinel
If you're familiar with SQL and want to learn KQL, translate SQL queries into KQL by prefacing the SQL query with a comment line, --, and the keyword explain. The output shows the KQL version of the query, which can help you understand the KQL syntax and concepts.
--
explain
SELECT COUNT_BIG(*) as C FROM StormEvents
Output
| Query |
|---|
StormEvents<br>| summarize C=count()<br>| project C |
SQL to Kusto cheat sheet
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 dependenciesWHERE resultCode IS NOT NULL |
dependencies| where isnotnull(resultCode) |
isnotnull() |
| Comparison operators (date) | SELECT * FROM dependenciesWHERE timestamp > getdate()-1 |
dependencies| where timestamp > ago(1d) |
ago() |
| -- | SELECT * FROM dependenciesWHERE timestamp BETWEEN ... AND ... |
dependencies| where timestamp between (datetime(2016-10-01) .. datetime(2016-11-01)) |
between |
| Comparison operators (string) | SELECT * FROM dependenciesWHERE type = "Azure blob" |
dependencies| where type == "Azure blob" |
Logical operators |
| -- | -- substringSELECT * FROM dependenciesWHERE type like "%blob%" |
// substringdependencies| where type has "blob" |
has |
| -- | -- wildcardSELECT * FROM dependenciesWHERE type like "Azure%" |
// wildcarddependencies| where type startswith "Azure"// ordependencies| where type matches regex "^Azure.*" |
startswithmatches regex |
| Comparison (boolean) | SELECT * FROM dependenciesWHERE !(success) |
dependencies| where success == False |
Logical operators |
| Grouping, Aggregation | SELECT name, AVG(duration) FROM dependenciesGROUP 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 dependenciesGROUP 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 dependenciesORDER 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 dependenciesGROUP BY nameORDER BY Count DESC |
dependencies| summarize Count = count() by name| top 100 by Count desc |
top |
| Union | SELECT * FROM dependenciesUNIONSELECT * FROM exceptions |
union dependencies, exceptions |
union |
| -- | SELECT * FROM dependenciesWHERE timestamp > ...UNIONSELECT * FROM exceptionsWHERE timestamp > ... |
dependencies| where timestamp > ago(1d)| union (exceptions | where timestamp > ago(1d)) |
|
| Join | SELECT * FROM dependencies LEFT OUTER JOIN exceptionsON dependencies.operation_Id = exceptions.operation_Id |
dependencies| join kind = leftouter (exceptions)on $left.operation_Id == $right.operation_Id |
join |
| Nested queries | SELECT * FROM dependenciesWHERE resultCode == (SELECT TOP 1 resultCode FROM dependenciesWHERE resultId = 7ORDER BY timestamp DESC) |
dependencies| where resultCode == toscalar( dependencies | where resultId == 7 | top 1 by timestamp desc | project resultCode) |
toscalar |
| Having | SELECT COUNT(\*) FROM dependenciesGROUP BY nameHAVING COUNT(\*) > 3 |
dependencies| summarize Count = count() by name| where Count > 3 |
summarize where |
Related content
- Use T-SQL to query data