Kusto Detective Agency Season 2のメモ | Case 2 まで

# Kusto Detective Agency Season 2 のメモ

https://detective.kusto.io

せっかくなので学んだことをまとめておく。

## Onboarding - Welcome to the Kusto Detective Agency - Season 2!

### 問題

DetectiveCases テーブルから最も賞金を稼いだ Detective を見つけ出す。
賞金は、最初にケースを解決したものにのみ与えられる。

> But here's the catch: only the first detective to crack the case gets the bounty and major street cred!

> So, your mission, should you choose to accept it, is to dig into the vast archives of our system operation logs from the legendary year 2022.

### ポイント

`EventType == 'CaseSolved'` のレコードは同じ `CaseId` で複数レコード存在する。
賞金は、最初にケースを解決したものにのみ与えられるため、`Timestamp` が最も小さいレコードをもつ、`DetectiveId` に賞金を与えるようにする必要がある。

### 便利な関数

`arg_min` 最小のレコードを抽出するために利用。同様に最大のレコードを取得するには `arg_max`も使える。

https://learn.microsoft.com/ja-jp/azure/data-explorer/kusto/query/arg-min-aggfunction

https://learn.microsoft.com/ja-jp/azure/data-explorer/kusto/query/arg-max-aggfunction

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

```kql
.execute database script <|
// Create a table for the telemetry data:
.create table DetectiveCases(Timestamp:datetime, EventType:string, DetectiveId:string, CaseId: string, Properties:dynamic)
// Load the data:
.ingest async into table DetectiveCases (@'https://kustodetectiveagency.blob.core.windows.net/kda2start/log_00000.csv.gz')
.ingest async into table DetectiveCases (@'https://kustodetectiveagency.blob.core.windows.net/kda2start/log_00001.csv.gz')
.ingest into table DetectiveCases (@'https://kustodetectiveagency.blob.core.windows.net/kda2start/log_00002.csv.gz')
```
</details>

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


```kql
DetectiveCases
| where EventType == 'CaseOpened'
| project Timestamp,CaseId,money=toreal(Properties.Bounty)
| join kind=inner 
    (
    DetectiveCases
    | where EventType == 'CaseSolved'
    | summarize arg_min(Timestamp, DetectiveId) by CaseId
    ) on CaseId
| summarize sum(money) by DetectiveId

```
</details>

## Case 1 -To bill or not to bill?

### 問題

水道代と電気代が2倍になったので、請求レコードから精査し、本来の請求額を算出する

> Your mission is to work your magic with this data and query, bringing us closer to the truth behind this puzzling situation.


請求額の算出には以下の SQL が用いられる。

```sql
SELECT SUM(Consumed * Cost) AS TotalCost
FROM Costs
JOIN Consumption ON Costs.MeterType = Consumption.MeterType
```

### ポイント

請求レコード数が増えているのがおかしい。重複レコードを除外する必要がある。
同じ日付のレコードが複数あれば、最大値を採用する。

```kql
Consumption
| where Timestamp between (datetime(2023-04-01) .. datetime(2023-04-30))
| summarize count() by Timestamp,MeterType
| render timechart 
```

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/4914/ee111f16-62c0-52cb-f0cd-33d6b37086c4.png)

### 便利な関数

`lookup`　はより単純な `join` のような感じで使えるみたい。

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


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

```kql
.execute database script <|
// The script takes ~20seconds to complete ingesting all the data.
.set-or-replace Costs <| 
    datatable(MeterType:string, Unit:string, Cost:double) [
     'Water', 'Liter', 0.001562, 
     'Electricity', 'kwH', 0.3016]
.create-merge table Consumption (Timestamp:datetime , HouseholdId:string, MeterType:string, Consumed:double)
.ingest async into table Consumption (@'https://kustodetectiveagency.blob.core.windows.net/kda2c1taxbills/log_00000.csv.gz')
.ingest async into table Consumption (@'https://kustodetectiveagency.blob.core.windows.net/kda2c1taxbills/log_00001.csv.gz')
.ingest into table Consumption (@'https://kustodetectiveagency.blob.core.windows.net/kda2c1taxbills/log_00002.csv.gz')
```
</details>

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


```kql
Consumption
| where Timestamp between (datetime(2023-04-01) .. datetime(2023-04-30))
| summarize Consumed=max(Consumed) by Timestamp,HouseholdId,MeterType
| join kind=inner 
    (Costs) on MeterType
| summarize sum(Consumed * Cost)
```
</details>

## Case 2 - Catch the Phishermen!

### 問題

通話履歴から傾向を見出して詐欺電話の電話番号の特定する

> The police have asked for our assistance, and we've got a massive data set to work with. We've got listings of all the calls that have been made during the week, and we need to find the source of the phishing calls.

> It's not going to be easy, but we know you're up for the challenge! We need you to analyze the data and use your detective skills to find any patterns or clues that could lead us to the source of these calls.

### ポイント

`PhoneCalls` テーブルには、`EventType` のパターンとして、`Connect` と `Disconnect` が含まれるので、通話先から切断される回数が極めて多い電話番号を探す。

### 便利な関数

`Propeties` はオブジェクトなので、その要素を `extend` 関数で取り出す。

dynamic な値は型を付けてあげないと、集計関数が利用できない。

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

https://learn.microsoft.com/ja-jp/azure/data-explorer/kusto/query/scalar-data-types/dynamic#dynamic-object-accessors

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

```kql
.execute database script <|
.create-merge table PhoneCalls (Timestamp:datetime, EventType:string, CallConnectionId:string, Properties:dynamic)
.ingest async into table PhoneCalls (@'https://kustodetectiveagency.blob.core.windows.net/kda2c2phonecalls/log_00000.csv.gz')
.ingest async into table PhoneCalls (@'https://kustodetectiveagency.blob.core.windows.net/kda2c2phonecalls/log_00001.csv.gz')
// Last command is running sync, so when it finishes the data is already ingested.
// It can take about 1min to run.
.ingest into table PhoneCalls (@'https://kustodetectiveagency.blob.core.windows.net/kda2c2phonecalls/log_00002.csv.gz')
```
</details>

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

```kql
Consumption
PhoneCalls
| where EventType == "Connect"
| extend Origin=Properties.Origin, Destination=Properties.Destination, IsHidden=Properties.IsHidden
| where IsHidden == true
| join kind=inner (
PhoneCalls
| where EventType == "Disconnect"
| extend DisconnectedBy=Properties.DisconnectedBy
| where DisconnectedBy == "Destination"
) on CallConnectionId
| summarize count() by tostring(Origin)
| order by count_ desc
```
</details>