从 SQL 到 Azure Monitor 日志查询备忘单SQL to Azure Monitor log query cheat sheet

下表可帮助熟悉 SQL 的用户学习 Kusto 查询语言,用以在 Azure Monitor 中编写日志查询。The table below helps users who are familiar with SQL to learn the Kusto query language to write log queries in Azure Monitor. 让我们看一下用于解决常见场景的 T-SQL 命令以及 Azure Monitor 日志查询中的等效命令。Have a look at the T-SQL command for solving a common scenarios and the equivalent in an Azure Monitor log query.

从 SQL 到 Azure MonitorSQL to Azure Monitor

说明Description SQL 查询SQL Query Azure Monitor 日志查询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
选择表中的 100 条记录Select 100 records from a table SELECT TOP 100 * FROM dependencies dependencies
| take 100
Null 评估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"
日期比较:上 1 天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"
| where success == "False"
排序Sort SELECT name, timestamp FROM dependencies ORDER BY timestamp asc dependencies
| order by timestamp asc
| order by timestamp asc
区别Distinct SELECT DISTINCT name, type FROM dependencies dependencies
| summarize by name, type
| summarize by name, type
分组,聚合Grouping, Aggregation SELECT name, AVG(duration) FROM dependencies GROUP BY name dependencies
| summarize avg(duration) by name
| 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
按度量值排序的前 n 条记录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)
JoinJoin SELECT * FROM dependencies JOIN exceptions ON dependencies.operation_Id = exceptions.operation_Id dependencies
| join (exceptions) on operation_Id == operation_Id

后续步骤Next steps