Kusto Detective Agency Season 2のメモ | Case 5

# Kusto Detective Agency Season 2 のメモ 

https://qiita.com/georgeOsdDev@github/items/8f726405444c7f9de756

の続き

## Case 5 - Blast into the past

### 問題

削除された動画を探してそのURLを見ろという内容

## ポイント

### `parse`

`.create-merge table StorageArchiveLogs(Timestamp:datetime, EventText:string)`
となっていて `StorageArchiveLogs` にはタイムスタンプと `EventText` しかないので、`EventText`からなんとかするしかない。

試しに数件見てみると

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/4914/a21f67f6-8ca1-a7a1-ba03-9a15c6b085d5.png)

となっているので、`EventText` をそれっぽくパースしてみる

```sql
StorageArchiveLogs
| parse EventText with op ": '" url "' " rest
| summarize count() by op
```
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/4914/abf423a3-70b2-233a-30a4-3b38cf04bd61.png)

`Read blob transaction` を除くと以下のような感じ

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/4914/b19ba4a6-94c2-6f0a-d648-d0598d7614ab.png)

### `parse_url`

url をパーツごとに分けて PropertyBag に 変換してくれるユーティリティ

https://learn.microsoft.com/en-us/azure/data-explorer/kusto/query/parseurlfunction

```sql
StorageArchiveLogs
| parse EventText with TransactionType " blob transaction: '" BlobURI "'" *
| limit 10 
| extend urlParts = parse_url(BlobURI)
```

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/4914/e202a4d3-02ce-58ed-1524-b4e8fa01f877.png)

### `series_periods_validate`

https://learn.microsoft.com/en-us/azure/data-explorer/kusto/query/series-periods-validatefunction

Hint 1,2 より

> Imagine the vast horizons of a renowned influencer's digital realm. Thousands of views paint a vibrant picture of popularity. As a diligent detective, explore the data logs with precision. Which storage accounts shine brightly, effortlessly handling the surge of views?

> Every detective knows that even the most cunning culprits have a telltale signature. Now, shift your focus to Scott's weekly releases. What hidden patterns emerge when you study the access logs?

Scott Hanselman が配信に使っている Blob ストレージを見つけられれば良さそう。週次でビデオがリリースされるので、7d ごとのパターンを見る

```
StorageArchiveLogs
| parse EventText with TransactionType " blob transaction: '" BlobURI "'" *
| parse EventText with * "(" Reads:long "reads)" *
| extend Host = tostring(parse_url(BlobURI).Host)
| summarize 
    Reads=sumif(Reads, TransactionType == 'Read') by bin(Timestamp,1d), Host
| make-series ReadsAvg=avg(Reads) default=0 on Timestamp from todatetime("2023-06-14") to todatetime("2023-07-14") step 1d by Host
| project (periods,scores) = series_periods_validate(ReadsAvg, 7.0),Host
| where toreal(scores[0]) > 0
```

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/4914/504eff8b-c59e-2e95-634a-17d8c60f3642.png)

すると、score の高い Host が 107 件みつかる。

とりあえず一番異常値である "jrhlhtcgkp.blob.core.windows.net" を見てみる

```sql
StorageArchiveLogs
| parse EventText with TransactionType " blob transaction: '" BlobURI "'" *
| parse EventText with * "(" Reads:long "reads)" *
| extend Host = tostring(parse_url(BlobURI).Host)
| where Host == "jrhlhtcgkp.blob.core.windows.net"
| summarize 
    PartialDeletes=countif(TransactionType  == 'Delete' and EventText has "some parts may still be available in the archive location."), 
    CompleteDeletes=countif(TransactionType  == 'Delete' and EventText has "backup is completely removed"),
    Reads=sumif(Reads, TransactionType == 'Read') by bin(Timestamp, 1d), BlobURI
```

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/4914/e6e9f75e-2d11-d5ef-c101-2950f7f8b40b.png)

これは週次でリリースしているようではなさそうなので除外。
score を 0.5 以上で絞ると14 Host が得られる。

```
StorageArchiveLogs
| parse EventText with TransactionType " blob transaction: '" BlobURI "'" *
| parse EventText with * "(" Reads:long "reads)" *
| extend Host = tostring(parse_url(BlobURI).Host)
| summarize 
    Reads=sumif(Reads, TransactionType == 'Read') by bin(Timestamp,1d), Host
| make-series ReadsAvg=avg(Reads) default=0 on Timestamp from todatetime("2023-06-14") to todatetime("2023-07-14") step 1d by Host
| extend  (periods,scores) = series_periods_validate(ReadsAvg, 7.0)
| where toreal(scores[0]) > 0.5
| project Timestamp,Host,ReadsAvg
| render timechart 
```

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/4914/d488b920-babd-294f-9f55-0c5762adeb19.png)

`okeexeghsqwmda.blob.core.windows.net` は週次で新作をリリースしてそう。

```sql
StorageArchiveLogs
| parse EventText with TransactionType " blob transaction: '" BlobURI "'" *
| parse EventText with * "(" Reads:long "reads)" *
| extend Host = tostring(parse_url(BlobURI).Host)
| where Host == "okeexeghsqwmda.blob.core.windows.net"
| summarize 
    PartialDeletes=countif(TransactionType  == 'Delete' and EventText has "some parts may still be available in the archive location."), 
    CompleteDeletes=countif(TransactionType  == 'Delete' and EventText has "backup is completely removed"),
    Reads=sumif(Reads, TransactionType == 'Read') by bin(Timestamp, 1d), BlobURI
| project Timestamp, BlobURI, Reads
| render timechart 
```

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/4914/7cf9e9c6-b51e-1844-6935-448cacd308f1.png)

あとは、
> My hope is that the deletions weren't fully synced into the archive, leaving behind some remnants of that epic interview.

とのことなので、`okeexeghsqwmda.blob.core.windows.net` からバックアップの消し漏れを探す。



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

```sql
.execute database script <|
.create-merge table StorageArchiveLogs(Timestamp:datetime, EventText:string)
//clear any previously ingested data if such exists
.clear table StorageArchiveLogs data
.ingest async into table StorageArchiveLogs (@'https://kustodetectiveagency.blob.core.windows.net/kda2c5backuplogs/log_00000.csv.gz')
.ingest async into table StorageArchiveLogs (@'https://kustodetectiveagency.blob.core.windows.net/kda2c5backuplogs/log_00001.csv.gz')
.ingest into table StorageArchiveLogs (@'https://kustodetectiveagency.blob.core.windows.net/kda2c5backuplogs/log_00002.csv.gz')
```
</details>

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

バックアップの Delete が完了していないものを探して、バックアップ保存先を得る

```sql
StorageArchiveLogs
| parse EventText with TransactionType " blob transaction: '" BlobURI "'" *
| parse EventText with * "(" Reads:long "reads)" *
| extend Host = tostring(parse_url(BlobURI).Host)
| where Host == "okeexeghsqwmda.blob.core.windows.net"
| summarize 
    Create=countif(TransactionType  == 'Create'), 
    PartialDeletes=countif(TransactionType  == 'Delete' and EventText has "some parts may still be available in the archive location."), 
    CompleteDeletes=countif(TransactionType  == 'Delete' and EventText has "backup is completely removed"),
    Reads=sumif(Reads, TransactionType == 'Read') by BlobURI

StorageArchiveLogs
| parse EventText with TransactionType " blob transaction: '" BlobURI "'" *
| where BlobURI == "https://okeexeghsqwmda.blob.core.windows.net/vyskl/jqfovf.mp4"
| where TransactionType != "Read"
```

</details>