Kustoのよく使うクエリ集

Log Analyticsに蓄積されたデータで統計情報をとる場合に使えるKQLを記載します。

定期的に使えると思ったクエリをまとめていきます

サンプルデータ

以下でサンプルデータを作成する

datatable(TimeGenerated:datetime, SourceIP:string, DestinationIP:string)[
    datetime(2022/12/26 00:00:01), "1.1.1.1", "192.168.1.100",
    datetime(2022/12/26 00:00:02), "2.2.2.2", "192.168.1.100",
    datetime(2022/12/26 00:00:03), "3.3.3.3", "192.168.1.100",
    datetime(2022/12/26 00:00:04), "3.3.3.3", "192.168.1.100",
    datetime(2022/12/26 00:00:05), "1.1.1.1", "192.168.2.100",
]

実行結果は以下の通りになる

TimeGenerated [UTC]	SourceIP	DestinationIP
2022/12/26 0:00:01.000	1.1.1.1	192.168.1.100
2022/12/26 0:00:02.000	2.2.2.2	192.168.1.100
2022/12/26 0:00:03.000	3.3.3.3	192.168.1.100
2022/12/26 0:00:04.000	3.3.3.3	192.168.1.100
2022/12/26 0:00:05.000	1.1.1.1	192.168.2.100

ケース紹介

宛先IPに対する送信元IPアドレスのユニーク化と宛先IPアドレスに対するイベント件数の表示

以下のような結果を出したい

DetinationIP	SourceIP_details	event_count
92.168.1.100	["1.1.1.1","2.2.2.2","3.3.3.3"]	4
92.168.2.100	["1.1.1.1"]	1

以下のクエリを使う。make_setは重複値を排除する。make_listは重複値も表示する。

| summarize SourceIP_details=make_set(SourceIP), event_count=count() by DestinationIP

宛先IPに対する送信元IPアドレスのユニーク化とその件数ならびに宛先IPアドレスに対するイベント件数の表示

以下のような結果を出したい

DetinationIP	SourceIP_details	event_count
92.168.1.100	[{"SourceIP":"1.1.1.1","Count":1},{"SourceIP":"2.2.2.2","Count":1},{"SourceIP":"3.3.3.3","Count":2}]	4
92.168.2.100	[{"SourceIP":"1.1.1.1","Count":1}]	1

以下のクエリを使う。

| summarize count() by DestinationIP, SourceIP
| extend summary = bag_pack("SourceIP", SourceIP, "Count", count_)
| summarize SourceIP_details=make_set(summary), event_count=sum(count_) by DestinationIP

詳細解説

最初にDestinationIPとSourceIPの組み合わせに対するイベント数をカウントする

| summarize count() by DestinationIP, SourceIP

DestinationIP	SourceIP	count_
192.168.1.100	1.1.1.1	1
192.168.1.100	2.2.2.2	1
192.168.1.100	3.3.3.3	2
192.168.2.100	1.1.1.1	1

その後SourceIPとcount_をまとめた辞書型フィールドを作成する

| extend summary = bag_pack("SourceIP", SourceIP, "Count", count_)

summary	DestinationIP	SourceIP	count_
{"SourceIP":"1.1.1.1","Count":1}	192.168.1.100	1.1.1.1	1
{"SourceIP":"2.2.2.2","Count":1}	192.168.1.100	2.2.2.2	1
{"SourceIP":"3.3.3.3","Count":2}	192.168.1.100	3.3.3.3	2
{"SourceIP":"1.1.1.1","Count":1}	192.168.2.100	1.1.1.1	1

最後にDestinationIP単位に集約し、summaryフィールドのデータを集約し配列に格納し、count_フィールドの合計数を求める

| summarize SourceIP_details=make_set(summary), event_count=sum(count_) by DestinationIP