SQL to Azure Monitor log query cheat sheet

The table below helps users who are familiar with SQL to learn the Kusto query language to write log queries in Azure Monitor. Have a look at the T-SQL command for solving common scenarios and the equivalent in an Azure Monitor log query.

SQL to Azure Monitor

Description SQL Query Azure Monitor log query
Select all data from a table SELECT * FROM dependencies dependencies
Select specific columns from a table SELECT name, resultCode FROM dependencies dependencies
| project name, resultCode
Select 100 records from a table SELECT TOP 100 * FROM dependencies dependencies
| take 100
Null evaluation SELECT * FROM dependencies WHERE resultCode IS NOT NULL dependencies
| where isnotnull(resultCode)
String comparison: equality SELECT * FROM dependencies WHERE name = "abcde" dependencies
| where name == "abcde"
String comparison: substring SELECT * FROM dependencies WHERE name like "%bcd%" dependencies
| where name contains "bcd"
String comparison: wildcard SELECT * FROM dependencies WHERE name like "abc%" dependencies
| where name startswith "abc"
Date comparison: last 1 day SELECT * FROM dependencies WHERE timestamp > getdate()-1 dependencies
| where timestamp > ago(1d)
Date comparison: date range SELECT * FROM dependencies WHERE timestamp BETWEEN '2016-10-01' AND '2016-11-01' dependencies
| where timestamp between (datetime(2016-10-01) .. datetime(2016-10-01))
Boolean comparison SELECT * FROM dependencies WHERE !(success) dependencies
| where success == "False"
Sort SELECT name, timestamp FROM dependencies ORDER BY timestamp asc dependencies
| order by timestamp asc
Distinct SELECT DISTINCT name, type FROM dependencies dependencies
| summarize by name, type
Grouping, Aggregation SELECT name, AVG(duration) FROM dependencies GROUP BY name dependencies
| summarize avg(duration) by name
Column aliases, Extend SELECT operation_Name as Name, AVG(duration) as AvgD FROM dependencies GROUP BY name dependencies
| summarize AvgD=avg(duration) by operation_Name
| project Name=operation_Name, AvgD
Top n records by measure SELECT TOP 100 name, COUNT(*) as Count FROM dependencies GROUP BY name ORDER BY Count asc dependencies
| summarize Count=count() by name
| top 100 by Count asc
Union SELECT * FROM dependencies UNION SELECT * FROM exceptions union dependencies, exceptions
Union: with conditions SELECT * FROM dependencies WHERE value > 4 UNION SELECT * FROM exceptions WHERE value < 5 dependencies
| where value > 4
| union (exceptions
| where value < 5)
Join SELECT * FROM dependencies JOIN exceptions ON dependencies.operation_Id = exceptions.operation_Id dependencies
| join (exceptions) on operation_Id == operation_Id

Next steps