Kusto Detective Agency Season 2のメモ | Case 4

# Kusto Detective Agency Season 2 のメモ 

https://qiita.com/georgeOsdDev@github/items/3c9ead422bc3a0be08f9

の続き

## Case 4 - Triple trouble!

### 問題

市長が 3 つの犯罪の疑いをかけられたとのこと。`NetworkMetrics` テーブルと `IpInfo` テーブルから異常な傾向を見出す必要がありそう。

## ポイント

### `ipv4-lookup`

`IpInfo` テーブルは CIDR 表記

```sql
IpInfo
| limit 10
```

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/4914/c86780d6-754d-04e2-9497-309da7b9a78e.png)

Train me にて `ipv4_lookup` を使うと便利と指示がある。IPに特化した `lookup` 演算子

https://learn.microsoft.com/ja-jp/azure/data-explorer/kusto/query/ipv4-lookup-plugin

これを使うと、前述の CIDR をよしなに処理してくれる。

```sql
NetworkMetrics
| take 10
| evaluate ipv4_lookup(IpInfo, ClientIP, IpCidr)
```

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/4914/ef310762-738c-f22c-bfdc-f9b765d7adfb.png)

ただし時間がかかる処理のようで、すべてのデータに対して実行すると全然帰ってこない

```sql
NetworkMetrics
| evaluate ipv4_lookup(IpInfo, ClientIP, IpCidr)
| make-series sumBytesSent=sum(BytesSent), sumBytesReceived=sum(BytesReceived) on Timestamp step 1d by Info
```

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/4914/5a8778a4-fa7e-e860-baae-7044ac0ff168.png)


なので、別途 `distinct` で絞った値をマテビュー的に使う。

```sql
NetworkMetrics
| lookup ( 
    NetworkMetrics
    | distinct ClientIP
    | evaluate ipv4_lookup(IpInfo, ClientIP, IpCidr)
) on ClientIP
| make-series sumBytesSent=sum(BytesSent), sumBytesReceived=sum(BytesReceived) on Timestamp step 1d by Info
```

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/4914/af4d84a6-cbbc-dc6e-9685-72e7bb03781f.png)


### `render`　の `ysplit` プロパティ

複数軸の分割ができて見やすく表示できる

https://learn.microsoft.com/ja-jp/azure/data-explorer/kusto/query/visualization-timechart?pivots=azuredataexplorer#ysplit-property


![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/4914/fc5a769f-85fd-3aa9-6a00-6797496d882f.png)

↑これが、↓こうなる

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/4914/0ef6680b-2aac-df97-cbdd-e39b08d55e60.png)

ただし、これは問題の回答にはつかわなかった。

### 異常値の検出

Train me では `series_decompose_forecast` 関数が紹介されるが、異常値検出には `series_decompose_anomalies`、`anomalychart` が有効。

https://learn.microsoft.com/ja-jp/azure/data-explorer/kusto/query/series-decompose-anomaliesfunction

https://learn.microsoft.com/ja-jp/azure/data-explorer/kusto/query/visualization-anomalychart

シーズン 1 でも使う場面があった。

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/4914/51490798-a82c-bf6d-0479-2fdb865fbe84.png)

なかなか理解するのが難しいが、時系列データに対して `baseline` と比較して異常の有無(`ad_flag`)と異常スコア(`ad_score`) が算出できる。


ただし、これも全量を描画しようとすると死ぬ。

なので、上昇傾向に以上があるレコード `where set_has_element(ad_flag,1)` で絞った後に、スコアが大きい順に10件 Info のレコードを取る。
すると、異常値の傾向が極めて大きい1つのInfoがわかる。

```sql
NetworkMetrics
| lookup ( 
    NetworkMetrics
    | distinct ClientIP
    | evaluate ipv4_lookup(IpInfo, ClientIP, IpCidr)
) on ClientIP
| make-series sumBytesSent=sum(BytesSent) on Timestamp step 1d by Info
| extend (ad_flag, ad_score, baseline) = series_decompose_anomalies(sumBytesSent)
| where set_has_element(ad_flag,1)
| extend maxScore = series_stats_dynamic(ad_score).max
| top 10 by toreal(maxScore)
| project-away sumBytesSent, baseline
| render anomalychart with(anomalycolumns=ad_flag,ad_score)
```

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/4914/b739d673-6af4-b7c6-2976-abaaab3c10f8.png)

<details><summary>セットアップ</summary>

```sql
.execute database script <|
.create-merge table IpInfo (IpCidr:string, Info:string)
//clear any previously ingested data if such exists
.clear table IpInfo data
.ingest into table IpInfo (@'https://kustodetectiveagency.blob.core.windows.net/kda2c4network/ip-lookup.csv.gz')
.create-merge table NetworkMetrics (Timestamp:datetime, ClientIP:string, TargetIP:string, BytesSent:long, BytesReceived:long, NewConnections:int)
.clear table NetworkMetrics data
.ingest async into table NetworkMetrics (@'https://kustodetectiveagency.blob.core.windows.net/kda2c4network/log_00000.csv.gz')
.ingest async into table NetworkMetrics (@'https://kustodetectiveagency.blob.core.windows.net/kda2c4network/log_00001.csv.gz')
// Last command is running sync, so when it finishes the data is already ingested.
// It can take about 1.5min to run.
.ingest into table NetworkMetrics (@'https://kustodetectiveagency.blob.core.windows.net/kda2c4network/log_00002.csv.gz')
```
</details>

<details><summary>回答</summary>

`BytesSent` と `ByteReceived` どっちで見れば良いのか問題の趣旨がよく分からなかったので、両方試してみた。
流出案件なので `ByteReceived` だと思ったのだけど。。。

```sql
NetworkMetrics
| lookup ( 
    NetworkMetrics
    | distinct ClientIP
    | evaluate ipv4_lookup(IpInfo, ClientIP, IpCidr)
) on ClientIP
| make-series sumBytesSent=sum(BytesSent) on Timestamp step 1d by Info
| extend (anomalies, score, baseline) = series_decompose_anomalies(sumBytesSent)
| mv-expand Timestamp, anomalies, score, baseline, sumBytesSent
| where anomalies == 1 
| top 10 by toreal(score)
```

</details>