Kusto Detective Agency Season 2のメモ | Case 5

Kusto Detective Agency Season 2 のメモ

の続き

Case 5 - Blast into the past

問題

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

ポイント

parse

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

試しに数件見てみると

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

StorageArchiveLogs
| parse EventText with op ": '" url "' " rest
| summarize count() by op

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

parse_url

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

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

series_periods_validate

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

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

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

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

これは週次でリリースしているようではなさそうなので除外。
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 

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

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 

あとは、

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 からバックアップの消し漏れを探す。

セットアップ

.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')

回答

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

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"