Kusto Detective Agency Season 2のメモ | Case 10 その２

# Kusto Detective Agency Season 2のメモ

https://qiita.com/georgeOsdDev@github/items/9ae8a467385b99e32205

の続き

# Train me より

## `partition` 演算子

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

```sql
KuandaLogs
| where DetectiveId endswith '11' // Just a sample of the data.
| partition hint.strategy=native by DetectiveId
(
    order by Timestamp asc 
    | extend Diff = Timestamp - prev(Timestamp)
)
```

## `scan` 演算子

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

```sql
KuandaLogs
| where DetectiveId endswith '11' // Just a sample of the data.
| partition hint.strategy=native by DetectiveId
(
    order by Timestamp asc 
     | scan declare(FirstActivity:string, LoginTime:datetime) with (
        step start output=none: Message has 'User entered the system' => LoginTime=Timestamp;
        step s2:
            Timestamp - start.LoginTime < 10min and Message !has 'User entered the system' 
            =>  FirstActivity = Message, LoginTime = start.LoginTime;
    )
    | project DetectiveId, LoginTime, FirstActivityTimestamp=Timestamp,  FirstActivity
)
```

なかなか難しいが、
復号化のキーとなる、ユーザーのアクティブトークン `strcat_array(<active-user-encryption-tokens>, '')` を取得するために使う必要がありそう。


## やってみる

`SessionStart` ~ `SessinReset` で step を作ってみる

```sql
| partition hint.strategy=native by DetectiveId
(
    order by Timestamp asc 
     | scan with_match_id=session_id declare(SessionStartTime:datetime, activeOperations:string)  with  (
        step start: 
           EventName == "SessionStart" => SessionStartTime=Timestamp;
        step step:
           EventName != "SessionStart" and EventName != "SessinReset" 
           => activeOperations = iff(EventName == "OperationStarted", strcat(step.activeOperations, ",",operationId),
                                 iff(EventName == "OperationCompleted", replace_string(step.activeOperations, strcat(",",operationId), ""),
                                step.activeOperations));
        step end:
           EventName == "SessinReset" and Timestamp > SessionStartTime;
    )
)
```

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/4914/8fd66acb-543b-3884-ca0e-f34d1a07caa8.png)

`OperationCompleted` の時に除く token が入っていないのでルックアップを作っておく。

```sql
let CompleteOperationLogWithToken =
CompleteOperationLog 
| lookup StartOperationLog on operationId;
```

これで、メッセージ時点のActiveTokenがとれた。(tokenに,が入っていない前提だが)

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/4914/af06a54b-2459-8c98-4c4e-673260124871.png)


@で始まる文字列を強引にいじれば、一部エラーがあるがとりあえずある程度はこれでいけそう。

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/4914/c8eeefc9-1c9d-e94e-c388-5c7cd4f2d18d.png)


エラーは 10件しかないので`partition` 使わずに手作業でトークンを集めて解く

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/4914/89e44b04-da0c-1418-d361-df29c1b5a16b.png)


10件ともあまり重要そうなメッセージじゃないので無視

> [2023-09-09T23:08:30.0000000Z] Packing user 'kvc7433e4d153c3b81b2f0' answers + active user encryption tokens and sending them to Kuanda. Happy fishing!

なお、エラーの原因は以下のようにきれいに、`SessionStart` ~ `SessinReset`となっていないからっぽい

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/4914/1d0919d9-3025-1345-f249-23a05237e83e.png)

### メッセージ

メッセージは以下の 4 パターン

> Kuanda server endpoint is not reachable, will retry later.

>  Kuanda server returned an error... Cmon, we must migrate our servers to AMD processors to avoid these system halts!

>  Packing user <DetectiveId> answers + active user encryption tokens and sending them to Kuanda. Happy fishing!

>  TODO [BUGBUG]: Validate: bitset_count_ones(hash_many(<DetectiveId>, tostring($user_answer))) < 54! Leaving as-is for now, the chance it will actually happen is very low. (O boy, these non-AMD processors are literally melting down on invalid instruction sets!)

どう考えても 4 番目 が怪しい。

### 回答

とりあえず、AMD や melt-down でサブミットしてみるが違うので、そういうことではないらしい。

> If you have anything that can help, type it here

とのことなので、

bitset_count_ones　が 54 を超えるようなメッセージ送信する状況を作ってみる。

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

https://learn.microsoft.com/ja-JP/azure/data-explorer/kusto/query/bitset-count-onesfunction

自分の DetectiveID はクラスター名から取得可能

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/4914/1d9f2c6e-f620-b69b-862a-0da86117ec58.png)

うまく 54 を超えるような回答を送れば

> Whoohhoooo! 🎉 You've just knocked Kuanda's servers clean out of the game!


![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/4914/0bfc22cd-f655-119b-555c-fce7157a390f.png)




## 回答

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

```sql
.execute database script <|
.create table KuandaLogs (Timestamp:datetime, DetectiveId:string, Message:string)
.ingest async into table KuandaLogs (@'https://kustodetectiveagency.blob.core.windows.net/kda2c10adminlogs/log_00000.csv.gz')
.ingest async into table KuandaLogs (@'https://kustodetectiveagency.blob.core.windows.net/kda2c10adminlogs/log_00001.csv.gz')
.ingest into table KuandaLogs (@'https://kustodetectiveagency.blob.core.windows.net/kda2c10adminlogs/log_00002.csv.gz')
```

</details>

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

> If you have anything that can help, type it here

```sql
range s from 0 to 100000000 step 1
| extend bitsetCount = bitset_count_ones(hash_many("kvcxkfkqdxph56ydvmjur3", tostring(s)))
| where bitsetCount >= 54
```

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/4914/b73eef3e-c9b5-7569-3388-b9e08621bbca.png)


</details>