Log Analyticsワークスペースから結果を取得する時に、個人的によく使うKQLのTipsとなります。
whereやextendなど、基本的なことは記載しません。
KQLで分からないことがあった場合、まず公式マニュアルを確認することをお勧めいたします。
→Kusto 照会言語 (KQL) の概要 - Azure Data Explorer | Microsoft Learn
MS公式のチュートリアルもあります。
→チュートリアル: 一般的なKusto 照会言語演算子について学習する - Azure Data Explorer | Microsoft Learn
期間指定
期間指定をする場合は「between演算子」を使います。
月の始めと終わりはstartofmonth関数、endofmonth関数で求めることができます。
同様の関数にstartofday関数、endofday関数などがあります。
let month = datetime(2021/09);
let start = startofmonth(month);
let end = endofmonth(month);
Table
| where TimeGenerated between(start..end)
日時を表すdatetime型はUTC（協定世界時）です。
JST（日本標準時）に変換する方法はいくつかありますが、9時間足すと楽です(UTC+09:00)。
表示上は9時間足されている状態ですが、型としてはUTCなのでご注意ください。
let offset = 9h;
Table
| extend Timestamp_JST = Timestamp + offset
ユーザー定義テーブルの作成
datatable演算子でテーブルを作成することができます。
let T = datatable(severity:string, value:long)
['High', 2, 'Medium', 1, 'Low', 0];
ユーザー定義関数
関数の作成が可能です。
「let [関数名] = (引数1: 型, 引数2: 型, ...){ 関数の中身 };」の様に記載します。
詳細は公式ドキュメントに記載があります。
ユーザー定義関数 - Azure Data Explorer | Microsoft Learn
テーブルを引数として渡すこともできますし、
以下のように引数にテーブル名を文字列で渡して、table()関数でテーブルを参照するといった使い方も可能です。
let func = (tableName: string){
table(tableName)
| where TimeGenerated > ago(7d)
| summarize count()
};
let aadSignin = func("SigninLogs");
aadSignin
配列の使用
配列の作成
「dynamic型」や「make_list()関数」を使うと配列を作成できます。
let nums = dynamic([1, 2]);
print(nums)
> [1,2]
let words = datatable(word:string)["New", "Ark"]
| summarize make_list(word);
words
> ["New", "Ark"]
配列の要素との比較
「in演算子」を使って配列のいずれかの要素に等しい（※1）、「has_any演算子」を使って配列のいずれかの要素を含む（※2）といった判定ができます。
「==」「contains」「and」「or」を使っても書けますが、比較対象が多い場合は「配列」を使うことでシンプルに書けます。
※1：大文字、小文字を区別しない場合はin~演算子を使う
※2：すべての要素を含む判定は「has_all演算子」を使う
let nums = dynamic([1, 2]);
datatable(num:int)[0, 1, 2, 3]
| extend bool_ = num in (nums)
> |num|bool_|
===========
|0 |false|
|1 |true |
|2 |true |
|3 |false|
let words = datatable(word:string)["New", "Ark"]
| summarize make_list(word);
datatable(city:string)["New York", "London", "New Ark"]
| extend bool_ = city has_any (words)
> |city |bool_|
================
|New York|true |
|London |false|
|New Ark |true |
テーブルの結合の種類について（結合フレーバー）
テーブルの結合に使うJoin演算子には「結合フレーバー(kind=xxx)」という、結合の種類を決めるパラメータがあります。
LeftTable
|join [kind=結合フレーバー] (RightTable) on column
「結合フレーバー」は全部で9種類あります。
公式マニュアルにベン図を使った説明があるので確認しておきましょう。
→join 演算子 - Azure Data Explorer | Microsoft Learn
未指定の場合は「kind=innerunique（左側の重複を除去する内部結合）」が既定の動作となるので、都度指定した方が無難です。
ピボットテーブル
「ピボットプラグイン」により、ピボットテーブルの実現が可能です。
「ピボットプラグイン」の構文は「| evaluate pivot(引数)」になります。
詳細は公式サイトを確認してください。
→pivot プラグイン - Azure Data Explorer | Microsoft Learn
<サンプルコード>
let InputTable = datatable(TimeGenerated:datetime, Item:string, Value:long)[
datetime(2023/01/01), "項目_A", 1, datetime(2023/01/01), "項目_B", 2,
datetime(2023/01/01), "項目_A", 3, datetime(2023/01/02), "項目_B",4];
InputTable | evaluate pivot(Item, sum(Value), TimeGenerated)
<実行結果>
"TimeGenerated [UTC]","項目_A","項目_B"
"2023/1/1 0:00:00.000",4,2
"2023/1/2 0:00:00.000",0,4
外部データをテーブルとして利用
通常はログとして取り込んでいないデータ（インターネット上に公開されているデータや手元にあるデータなど）をクエリ内で参照して使いたい場合があると思います。
そういった場合に有効な方法です。
インターネットに公開されたCSV、JSONファイルの利用
「externaldata演算子」を使うと、インターネットに公開されたCSVやJSONファイルをテーブルとして取り込んで利用することができます。
公開されたデータのURLを「externaldata演算子」の引数に指定することで取り込みを実施します。
externaldata(Columnname:columnType [, ...] )
[storageConnectionString [, ...] ] [with(propertyName=propertyValue [, ...])]
以下はインターネット上に公開されているCSVを読み込む例です。
let iocs = externaldata(DateAdded:string,FirstSeen:string,IoC:string,Type:string,TLP:string)
[@https://raw.githubusercontent.com/microsoft/mstic/master/Indicators/May21-NOBELIUM/May21NOBELIUMIoCs.csv] with (format="csv", ignoreFirstRecord=True);
iocs | where Type =~ "SHA256"| project IoC
以下はインターネット上に公開されているJSONファイルを読み込む例です。
let azure_ranges = externaldata(changeNumber: string, cloud: string, values: dynamic)
[https://download.microsoft.com/download/7/1/D/71D86715-5596-4529-9B13-DA13A5DE5B63/ServiceTags_Public_20230828.json]
with(format='multijson')
| mv-expand values
| mv-expand values.properties.addressPrefixes
| mv-expand values_properties_addressPrefixes
| summarize by tostring(values_properties_addressPrefixes);
azure_ranges
このクエリは、MSが公開するAzureのIPアドレスレンジが記載されたJSONファイルから、IPアドレスレンジを取得します。
この例で使用するJSONファイルは頻繁に更新されるため、動作確認を行う際は最新のファイル名「ServiceTags_Public_YYYYMMDD.json」を以下URLから確認してください。
→https://www.microsoft.com/en-US/download/details.aspx?id=56519
「mv-expand演算子」は、テーブルの列要素が配列やプロパティバッグの場合に展開を行う演算子で、「make-list関数」「make-series関数」とは逆の動きとなります。
JSONはツリー構造なので、externaldata演算子で読み込んだ結果、必要な値が動的な要素に含まれる場合は「extend演算子」「mv-expand演算子」「summarize演算子」「distinct演算子」などを使って必要な情報を抽出する必要があります。
ウォッチリストの利用
インターネットに公開したくないけど外部データをテーブルとして使用したいという場合は「ウォッチリスト」が有効です。
「ウォッチリスト」に格納した表形式のデータは「_GetWatchlist 関数」で参照することができます。
