本記事について
本記事は、私が Qiita でまとめている、「Azure Log Analytics と Kusto (KQL) 入門」の第三弾となります。本稿では、Kusto (KQL) で複雑なクエリを書くときに重要になる Join 句の使い方を見ていきます。
第一弾
第二弾
また、今回ご紹介するクエリもすべて、マイクロソフト社から提供されているデモ環境 (aka.ms/LADemo) でどなたでもすぐにお試しいただけます。
Log Analytics や Sentinel, Azure Monitor などでの Join 句の使いどころ
一番よく利用されるケースは、ログのテーブルとデバイスやユーザーなどの一覧となっているテーブルを結合し、ログテーブルに対して情報を付加するシナリオではないでしょうか？
例えば、Azure Monitor VM Insights では、VMComputer テーブルが仮想マシンの一覧を提供し、VMProcess テーブルや VMConnection テーブルがログになっています。VMComputer テーブルは仮想マシンの IP アドレスや MAC アドレス、スペックや OS 情報などこと細かな情報が提供されます。一方各ログのテーブルはプロセスやコネクションの情報を提供しますが、仮想マシンそのものについての列は限定的です。そういった際に、VMProcess テーブルに対して、VMComputer テーブルの情報を結合することで調査に必要な情報をまとめて出力できます。
ほかにも、Sentinel UEBA では IdentityInfo がユーザーアカウントの一覧のテーブルになっていたり、また DeviceInfo テーブルが Defender for Endpoint のデバイス一覧のテーブルになっていたりします。それぞれのツールでログを検索する際に、これらのテーブルを使って Join で結合することで、よりリッチな情報を引き出せます。
また、Sentinel では、脅威インテリジェンスのテーブルやウォッチリストでユーザーがアップロードしたテーブルもこういった用途で利用できるかと思います。
Log Analytics や Sentinel, Azure Monitor で利用するときの注意点
Azure のログ系のサービスでは、一覧を使うテーブルもログと同じテーブルに入ることが多くあります。（先述のテーブルなど。）その場合、テーブルのレコードが定期的に追記される形になり、同じユーザーアカウントやコンピュータに対して、複数のレコードがある状態になっています。
そういった場合は、最新のレコードのみを表示するテーブルを作成して Join をかけるのがおすすめです。これにより、一覧の中で重複を排除した形で結合に使えるようになります。
VMComputer テーブルの例
たとえば、デモ環境の VMComputer テーブルには、直近31日間のレコードが15825個入っていました。これは、このデモ環境の中に15825台のサーバーがあり、そのレコードが入っているわけではありません。定期的に最新の状態のレコードがこのテーブルに追記される仕組みのため、直近31日間のレコード数が15825行にも上っているという状態です。
VMComputer | where TimeGenerated > ago(31d) | count
なので、実際のサーバー数を知る必要があります。ここで便利なのが、第一弾の記事でも紹介した distinct 句です。distinct 句を使い、 AgentId で重複排除すると、27個になりました。
VMComputer | where TimeGenerated > ago(31d) | distinct AgentId | count
つまり、今回コンピューター一覧ではこの27台の情報さえあれば良いということになります。27台の情報一覧を、それぞれ最新のレコードから作成するには、以下のクエリを実行します。
VMComputer | where TimeGenerated > ago(31d) | summarize arg_max(TimeGenerated, *) by AgentId
arg_max(引数1, 引数2) by 列名1 (集計関数)
なお、arg_max 関数は、列名1の値ごとに、指定の引数1が最大値になるレコードの中の引数2の結果を返す関数です。特に、今回のように、引数2 = * とすることですべての列を返すことができます。
今回は、AgentId ごとに1レコードずつ、それぞれ最新のものを表示する必要がありました。なので、arg_max 関数を使って、by AgentId として AgentId ごとにひとつ返すことにし、それが、引数1=TimeGenerated で TimeGenerated が最大 (=最新)のレコードが選ばれるようにしました。また、引数2=* なので VMComputer テーブルのすべての列がそのまま表示されます。
Join 句の使い方
ここから、本題の Join の使い方に入っていきます。Join の基本文法は下記です。
LeftTable | join [ kind = <Join の種類>] (RightTable) on <条件>
Join の種類については、公式ドキュメントの下記の図が直感的にわかりやすくまとめてくれています。
早速、左側を VMProcess テーブル、右側を VMComputer テーブル (arg_max を使って一台一レコードにしたもの）として、Join を使ってクエリを書いてみます。なお、VMProcess は名前の通り、VM Insights が収集しているサーバーのプロセスのログになります。どちらのテーブルにも列名が AgentId という列が存在しており、両方同じ一意の ID を使っているもののため、この列を条件に結合します。（もし、左右のテーブルでこの条件に使う列名が異なっている場合、 $left.<左のテーブルの列名> == $right.<右のテーブルの列名> としてください。）
VMProcess
| where TimeGenerated between (datetime(2023-7-1 00:00:00) .. datetime(2023-7-1 01:00:00))
| join (
VMComputer
| where TimeGenerated > ago(31d)
| summarize arg_max(TimeGenerated, *) by AgentId
)
on AgentId
| sort by TimeGenerated
すると、VMProcess テーブルの中に、VMComputer が持っているテーブルの情報が付加されたテーブルが作成されました。しかし、VMProcess テーブルのレコードが17行しかありません。。これは、Kusto の join の種類の既定値が innerunique (内部結合でかつ左側で重複している行は削除) になっているからです。
なので、次に kind=inner (内部結合) を試してみます。下の図のように、両方のテーブルに合致するキーの値がある行のみ取り出されます。
VMProcess
| where TimeGenerated between (datetime(2023-7-1 00:00:00) .. datetime(2023-7-1 01:00:00))
| join kind=inner (
VMComputer
| where TimeGenerated > ago(31d)
| summarize arg_max(TimeGenerated, *) by AgentId
)
on AgentId
| sort by TimeGenerated
すると、今度は1044行のレコードが取り出されました。しかし、内部結合の場合、もし右側の仮想マシン一覧のテーブルから漏れているサーバーがあった場合、 VMProcess のテーブルから除外されてしまいます。このようなケースを防ぐため、次は kind=leftouter (左外部結合) を試してみます。
VMProcess
| where TimeGenerated between (datetime(2023-7-1 00:00:00) .. datetime(2023-7-1 01:00:00))
| join kind=leftouter (
VMComputer
| where TimeGenerated > ago(31d)
| summarize arg_max(TimeGenerated, *) by AgentId
)
on AgentId
| sort by TimeGenerated
今回は仮想マシン一覧の中でリスト漏れした仮想マシンがなかったようで（過去31日分のリストを引っ張ってきているので当たり前といえば当たり前ですが）、内部結合と左外部結合の出力値が一致していました。
ちなみに、本来今回の用途で使う必要はない種類ですが、kind=fullouter (完全外部結合) を試すと以下になります。
VMProcess
| where TimeGenerated between (datetime(2023-7-1 00:00:00) .. datetime(2023-7-1 01:00:00))
| join kind=fullouter (
VMComputer
| where TimeGenerated > ago(31d)
| summarize arg_max(TimeGenerated, *) by AgentId
)
on AgentId
| sort by TimeGenerated
絵からもわかる通り、VMProcess テーブルに存在しない仮想マシンのレコードが VMComputer 側にあった場合、それらのレコードが最後に追記されてしまっています。このユースケースでは出番がない種類でしたが、挙動を確認することができました。
上記では、既定値の innerunique の含めると4種類の Join を試してみました。それ以外にも複数種類がありますので、Kusto クエリを書かれる際は、下記ドキュメントをご参照いただき、必要なクエリ分をご作成ください。
最後に
普段クエリ分を書かない方にとっては、Join は少し難しい演算子かもしれません。本稿がその Join を使って Kusto クエリを書き始めていただく、第一歩となりましたら幸いです。
＊本稿は、個人の見解に基づいた内容であり、所属する会社の公式見解ではありません。また、いかなる保証を与えるものでもありません。正式な情報は、各製品の販売元にご確認ください。