SQL から Azure Monitor ログ クエリへのチート シートSQL to Azure Monitor log query cheat sheet

下の表は、SQL を使い慣れているユーザーが、Azure Monitor でログ クエリを記述するための Kusto クエリ言語を学習する助けになります。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 common scenarios and the equivalent in an Azure Monitor log query.

SQL から Azure Monitor へSQL 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
DistinctDistinct 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
UnionUnion 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