Kusto Detective Agency Season 2のメモ | Case 9

# Kusto Detective Agency Season 2 のメモ

https://qiita.com/georgeOsdDev@github/items/e59ef92581c4fbf44c22

の続き

# Case 9 Network Hunch

> The KDA network is made up of three types of machines: Gateway, Backend, and Admin. All requests flow through a Gateway machine before taking a journey through various Backends until they finally reach the Admin. Each machine can split a request into sub-tasks, and these sub-tasks go to another Backend or Admin machine. Oh, and here's a crucial tidbit: each machine undergoes periodic vulnerability scans, triggering an alert if any issues are found.
> 
> Now, here's your mission, Detective. Professor Smoke has shared some secret tech with us, which involves graph-matching. We need you to put it to use and determine if an attack went down. Specifically, we want to know if a request or any of its sub-tasks reached a vulnerable Admin machine, with all the hops along the way (Gateway and Backends) also being vulnerable. If an attack did occur, we can use this info to investigate the compromised Admin machine and see what we can uncover.

Smoke 教授の [graph-matching](https://learn.microsoft.com/en-us/azure/data-explorer/kusto/query/graph-operators) テクニックを使って、MachineLog から攻撃された Admin Machine を見るければいいのかな。

## テーブル情報
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/4914/6bb59372-9c3b-9536-0e22-c2a75e2cd765.png)


![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/4914/d6006dde-55a5-f3fe-adca-5cd4df56a649.png)

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/4914/a5ed96c0-46fe-3862-332a-51e6b3acc1f9.png)

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/4914/99f60213-92d9-7198-614e-ec7ca3bbea70.png)

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/4914/06d1ddb2-5868-6055-84ff-5fd9617ffb89.png)

### EventType == "IncomingRequest"

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/4914/d31dc3d8-5c62-2a2e-6082-77785a3a3b11.png)

とりあえずパースしてビューにしておく

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/4914/528ed0be-a51e-791b-5651-e613d3e4086b.png)

### EventType == "SpawnTask"

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/4914/32379cdd-509a-9eb7-91d4-a0038030f3e3.png)

こちらもパースしておく

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/4914/6c7891dd-610f-f3b6-2467-8650743a0680.png)

### EventType == "PeriodicScan"

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/4914/fcceafb7-e362-9723-97df-f0bafe5b328c.png)

こちらもパースしておく

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/4914/d5268ce2-45ec-7f7a-6ebf-144092a6a529.png)

## 方針

`TaskId`/`SubTaskId`から辿って、途中に存在した `Machine` が すべて vulnerable であるものを探せばよいのかな。

### とりあえず

vulnerable かどうか判定用のビュー

```sql
let PeriodicScanMachineLogs = MachineLogs
| where EventType == "PeriodicScan"
| parse Message with MachineType " periodic scan completed, " criticalCount " critical vulnerabilities were found."
| project-away Message, EventType
| order by Timestamp asc;
let vulnerableMachines = PeriodicScanMachineLogs
| where toreal(criticalCount) > 0
| distinct Machine;
vulnerableMachines
```

### Train me よりグラフ構造化を使えとのこと

`make-graph` と `graph-match`

https://learn.microsoft.com/ja-jp/azure/data-explorer/kusto/query/make-graph-operator

https://learn.microsoft.com/ja-jp/azure/data-explorer/kusto/query/graph-match-operator

Gateway - Backend * n - Admin の経路を `make-graph` をつかってグラフ構造にする

#### Machine 同士 でグラフにしてみる

```sql
let PeriodicScanMachineLogs = MachineLogs
| where EventType == "PeriodicScan"
| parse Message with MachineType " periodic scan completed, " criticalCount " critical vulnerabilities were found."
| project-away Message, EventType
| order by Timestamp asc;
// 脆弱 Machine 一覧
let VulnerableMachines = PeriodicScanMachineLogs
| where toreal(criticalCount) > 0
| distinct Machine;
// MachineTypeのルックアップ用
let MachineTypes = PeriodicScanMachineLogs
| distinct Machine, MachineType;
let SpawnTasks = MachineLogs
| where EventType == "SpawnTask"
| parse Message with "TaskID="TaskId ": spawning a sub-task with TaskID=" SubTaskId " on " NextMachine
| project-away Message, EventType
| order by Timestamp asc;
SpawnTasks
| where Machine in (VulnerableMachines) and NextMachine in (VulnerableMachines)
// Machine 間のグラフ
| make-graph Machine --> NextMachine with MachineTypes on Machine
| graph-match (gateway)-[firstTask]->(backend)-[tasks*1..10]->(admin)
where gateway.MachineType == "Gateway"
and backend.MachineType == "Backend"
and admin.MachineType == "Admin"
project Gateway=gateway.Machine, firstTask.NextMachine, backend.Machine, Steps = tasks.NextMachine, Admin = admin.Machine
| limit 1000
```

これだと件数が多すぎてどうにもならない。
原因としては、同じMachine -> NextMachine の遷移が何度も発生しているからと考えられる。

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/4914/53d1ae93-2fb7-8413-eb86-e2007057660d.png)

#### TaskId でグラフにしてみる

Machine -> NextMachineは同じパスが無数に存在するので、他に使えそうなものは TaskId -> SubTask　がある。
TaskId は結局実行したMachineに紐づくので、MachineTypeもTaskIdにつなげておく

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/4914/2daec4f9-345d-70d7-11b4-a8eb0e93b3fd.png)



## 回答

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

```sql
.execute database script <|
.create-merge table MachineLogs (Timestamp:datetime, Machine:string, EventType:string, Message:string)
.ingest async into table MachineLogs (@'https://kustodetectiveagency.blob.core.windows.net/kda2c9kda/log_00000.csv.gz')
.ingest async into table MachineLogs (@'https://kustodetectiveagency.blob.core.windows.net/kda2c9kda/log_00001.csv.gz')
.ingest into table MachineLogs (@'https://kustodetectiveagency.blob.core.windows.net/kda2c9kda/log_00002.csv.gz')
```

</details>

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

Where can we catch the suspect?　lan, lat


```sql
let PeriodicScanMachineLogs = MachineLogs
| where EventType == "PeriodicScan"
| parse Message with MachineType " periodic scan completed, " criticalCount " critical vulnerabilities were found."
| project-away Message, EventType
| order by Timestamp asc;
// 脆弱 Machine 一覧
let VulnerableMachines = PeriodicScanMachineLogs
| where toreal(criticalCount) > 0
| distinct Machine;
// MachineTypeのルックアップ用
let MachineTypes = PeriodicScanMachineLogs
| distinct Machine, MachineType;
// TaskId毎のMachineTypeルックアップ用
let TaskIdWithMachineType = MachineLogs
| where EventType == "IncomingRequest"
| parse Message with "Got request with TaskID=" TaskId " from " FromMachine
| project-away Message, EventType
| where Machine in (VulnerableMachines)
| join kind = inner MachineTypes on Machine
| project TaskId,Machine,MachineType;
let SpawnTasks = MachineLogs
| where EventType == "SpawnTask"
| parse Message with "TaskID="TaskId ": spawning a sub-task with TaskID=" SubTaskId " on " NextMachine
| project-away Message, EventType
| order by Timestamp asc;
SpawnTasks
| where Machine in (VulnerableMachines) and NextMachine in (VulnerableMachines)
| project Timestamp,Machine,TaskId,SubTaskId,NextMachine
// TaskIdとSubTaskIdのグラフをつくって、ルックアップテーブルと紐づける
| make-graph TaskId --> SubTaskId with TaskIdWithMachineType on TaskId
| graph-match (gateway)-[firstTask]->(backend)-[tasks*1..10]->(admin)
where gateway.MachineType == "Gateway"
    and backend.MachineType == "Backend"
    and admin.MachineType == "Admin"
project Gateway=gateway.Machine, firstTask.NextMachine, backend.Machine, Steps = tasks.NextMachine, Admin = admin.Machine
| limit 100
```

これで1レコードとれるので、admin.Machine を入力してクリア

</details>