Kusto Detective Agency Season 2 のメモ
の続き
Case 8 Catchy Run
Now, let's dive into the current situation. We were thiiis close to catching Krypto, but he slipped through our fingers in the bustling city of Barcelona. Our intelligence sources provided a lead indicating that Krypto is a die-hard runner, hitting the pavement 3-4 times a week and running 8-12 kms each time. We managed to obtain fitness data from the past two weeks of all runners in Barcelona, thanks to our National Security Office's (NSO) extensive reach. However, despite our best efforts, we couldn't pinpoint where he starts his runs. It's up to you, detective, to crack this case wide open!
ランナーのデータから Krypto の所在を突き止めよとのこと
But wait, there's more. We intercepted a message sent by Krypto to his Kuanda associates. As expected, he encrypted the message using a sophisticated Krypto-code, rendering it unreadable without the key. It appears that the Kuanda possess a tribal knowledge of how to obtain these keys based on specific cities. While our agents have managed to decipher half of the required 16 numbers, we're still missing the full Barcelona city code.
Good luck, detective, and may your wits guide you through this challenge!
Krypto が送るメッセージの暗号化キーの半分を特定せよ的な感じ。
ランニングデータ
hitting the pavement 3-4 times a week and running 8-12 kms each time している RunnerID をとりあえず探す
Runs
| summarize _7dRunCount=count(), _7dAvgDistance=avg(Distance) by bin(Timestamp,7d), RunnerID
| summarize _avg7dRunCoount = avg(_7dRunCount), _avg7dAvgDistance=avg(_7dAvgDistance) by RunnerID
| where _avg7dRunCoount between (3 .. 4)
| where _avg7dAvgDistance between (8 .. 12)
| distinct RunnerID
これだと 23317 件もヒットするのでもっと絞る工夫が必要
Runs
| summarize _7dRunCount=count(), _7dAvgDistance=avg(Distance), _7dMinDistance=min(Distance), _7dMaxDistance=max(Distance) by bin(Timestamp,7d), RunnerID
| where _7dMinDistance >= 8
| where _7dMaxDistance <= 12
| where _7dRunCount between (3 .. 4)
| summarize _avg7dRunCoount = avg(_7dRunCount), _avg7dAvgDistance=avg(_7dAvgDistance) by RunnerID
| where _avg7dRunCoount between (3 .. 4)
| where _avg7dAvgDistance between (8 .. 12)
| distinct RunnerID
もう少し絞ってみると 7628 件
こっちはちょっとお手上げなので、ヒントを見る。
Train me
Welcome to the vibrant and mysterious city of Barcelona, where thrilling adventures await you at every turn!
You've already mastered the art of KQL geo-spatial capabilities, but let me ask you something, Detective:
Can our skills truly shine without a touch of soul? So, it's time to dive into the captivating arts and history of one of the coolest cities in the world!If you haven't heard or experienced Barcelona yet, get ready to be blown away. Everywhere you look, you'll find astonishing buildings by famous architects like Antoni Gaudi and Joseph Cadafalch. But let me tell you, the real gem in this city's crown is the legendary Sagrada Familia.
Crafted by the genius Antoni Gaudi, this mind-boggling basilica has been a work in progress for over a century, and it's still evolving! Gaudi's intricate plans continue to shape this iconic monument right before our eyes. It's unlike anything you've ever seen before—a magnificent fusion of brilliant ideas fused into a single architectural marvel! And let's not forget about the incredible facades of the Sagrada Familia, like the Nativity and Passion ones. Each facade has its own special theme and style. The Nativity facade fills you with joy and wonder, while the Passion facade takes you to a more serious and solemn atmosphere, reminding you of the intense moments of Christ's suffering.
No need to hold back, detective! Go out and explore the city, and feel free to use extra resources to navigate it. And when you're on the final stages of cracking the case, be sure to double-check the location with your very own eyes. Here's a handy little helper function to visit the final answer places:
let VirtualTourLink = (lat:real, lon:real) {
print Link = strcat('https://www.google.com/maps/@', lat, ',', lon,
',3a,75y,252.01h,89.45t/data=!3m6!1e1!3m4!1s-1P!2e0!7i16384!8i8192')
};
VirtualTourLink(lat, lon)
Hint 1/3
Wondering about the city code? Perhaps, wandering the city wonders can help.
Hint 2/3
The encrypted message may shed a light on unique behavior or pattern. Can you sport what is it?
Hint 3/3
Got multiple locations and not sure which one is correct? Sometimes, even the best detective needs to visit the location in person to get a street view and solve the case. 🕵️🔎
今までの Case と違って具体的な kusto の手ほどきじゃない感じがする。
バルセロナの情報をよく見ろとの感じヒント
でとりあえずググってみると、サグラダファミリアのファサードにパネルがあるらしい。
これは暗号解読のキーっぽい。
暗号解読
以下の関数が定義されていて
.create-or-alter function Dekrypt(T:(Message:string, Key:string)) {
let lx=array_concat(range(48, 57, 1), range(65, 92, 1), range(97, 122, 1));
let uc = (_s:string) { unicode_codepoints_from_string(hash_sha256(_s))};
T
| extend c1 = uc(tolower(Key)), c2 = uc(toupper(Key))
| mv-apply with_itemindex=i l=lx to typeof(int), c=c1 to typeof(int) on (order by c asc, i asc | summarize c1=unicode_codepoints_to_string(make_list(l)))
| mv-apply with_itemindex=i l=lx to typeof(int), c=c2 to typeof(int) on (order by c asc, i asc | summarize c2=unicode_codepoints_to_string(make_list(l)))
| extend Result = coalesce(base64_decode_tostring(translate(c1, c2, Message)), "Failure: wrong key")
| project-away c1, c2
}
.create-or-alter function SecretCodeToKey(T:(c1:long,c2:long,c3:long,c4:long))
{
let magic_const = toscalar(T | summarize sum(c1)*sum(c2)/sum(c3)+sum(c4));
let key = toscalar(T
| summarize list = make_list(pack_array(c1, c2, c3, c4))
| project key=unicode_codepoints_to_string(series_add(list, magic_const)));
key
}
以下のような処理がおぜん立てされている。
ランニングデータをもとに、? に当てはまるコードを見つけて実行する
// Secret Message intercepted
let city_code=datatable(c1:long,c2:long,c3:long,c4:long)
[1, ?, ?, 4,
?, 7, 6, ?,
8, ?, 10, 5,
?, 2, ?, 15];
print Key=SecretCodeToKey(city_code), Message=
@'0SOHpSdTgidfqXFOYeIOjktOjXFcjktPjwzHgSABgsctZknJZKfEjBAygipOgS\\pBNEjknCVedTpSdyjk7EZKFHVSOa8i7E8SOCZedfgSOTgSA'
@'tYPFaYB4TjXFHZ[\OVkNT17mzgSv\VPFHjknHjKFnVedygSvuVBvOYBxDgS4HgiztVkAyYyFujPFupwgEpiztjKFmVaIOVaImV[nHgS\\pBNEYB'
@'d\Z[\OjXFb8SNEjk4yYyFujPFb8SNEKbIFqEbo7adbgSjOZwgEVBAbqXFc6KFDVeO\VXFCV[tyZkIOYyfEjBAygipOgiv5Zk2DgSnupXFeZwjOY'
@'PFBYBAcgSAtYPFfZwI5gKFzjPF\VaOb8SOTjyfEp[NEY[\\VSfE8knbjknH8kjngSAtYPFOjBjuYaIHgidTpSODgiI5jKFqIssEZeztVkzDjwME'
@'ZBdTjk4b8XFupwgEjBdOpXxvXJJEZ[4TVBAbgiv5ZwzOgiIuVyFcpkv5gS4bgiI58wMEpSOcjKfEZadbgizOYe7EZwvHpwzOjXfEp[NEZwzOgiz'
@'tVBnmVBYEVedygXzHVkAQjKFbjwvbYygDgSzupSEEjBOapwz\pSO[jk2ngS4TjXF2pkObjKFD8wIOYB4DViJT17mN8Sdngiv5Zk2DgSdxYSAHjK'
@'Fb8SNEKbIFzeMEp[d\8[nOYevOYyF\VB7E8SdyZk2JgSObYyFOYSOCgSIup[nBZk2DqEbo17mUVeYDgS2OpXFtYyF\jSIyjwvHgiI5jKFcZwIbj'
@'wgEV[ZEVwJEp[dDVXtPjkOTjyxEKKFtVBIOYavbZknJgiI5Zw7EpS\OYBNE8wMEZKFaYBd\pXFJjk4DgSABgSvtYBOuY[Ob6KFyjkp\YBImVBYE'
@'VwJEY[4BjwInqPrvXJ2OpXFcjKF\YevtYBNE6kAtqXFmpXFeZwMEZk2DgSsEVk4bpSdygSABgSOcYSdCZ[4PVSNEpSOc8knaqPFUVyFJVedPpXF'
@'c6KFCV[nTjkvb8knagSjD8kp5pXFeZwMEZkxEjw\fjwzmjknCjKFujPF\gS2mjBdb8ktOg7bodSAugSz\jXFc6KFDpkpaZkpOgSj\8k2OjXFbVy'
@'FlV[OTgStOgSATgiI58wMEpS\y8k2D8knagSmupwzTjwJ\g15m175vXJztpXFBjk4ygSnupXfEVwJEjazmjknJYyfEVSd\pBOTjyFb8SOTjeMEp'
@'SREZ[\\VBvOgSOHgSnupXFc6KFHpiODjKxEKKF5ZwjOgS4HY[dcZB2OjXF\giIOZkbEV[ZEVSAnZkfEZBAJ6kptZwzJYyfvXap5VyFcVejOgipm'
@'pSEEVkNEVSOQjKFOVidH8wjOgiF5ZknbV[tHqXFOVavtYBOTjyFc6KFmVajmVBvmZBOD8wInqPFFpXF\VaJEj[O[jkxEpSOcjKfEZw7EVSd\Ye7'
@'EpipugSABgiI5jkbEjSOHZezOjwID67boY[\\jSAegStngSd[jwzngStupBNDgSd[jkxEjidy8knagStngSdx8SODZwz\pSOTjyFypknHgiI5YB'
@'Atj[EEpS\OgSvmpiJTg4Iypk2nqXFzgSjOjkfEpknbVedC8S4PVSNTgs4TjXFDjw7EVkNEpSdDVXFnVeND17mb8SOHgSvmpiJE8wMEZKF58kIJj'
@'kxEj[dcgKFzpXFujBjOYaMEZkxEZkztVBI\VBvOgSABgSt\YajOVSAtYyFHYSAbYyFe8SdyjKFuVBNEZ[4TgSOTjidDj[NE8kxEZKFyjkjyjwv5'
@'8knagiv5ZkcOgS4BpSdy17mCV[n2pkdy8knagSsEZazOZwI5pS4Q8knag1sfKyFypkxTgsObgSOHgSsEYSdyjBdCpXFPVSdTjXFujPFc8wvC8SO'
@'OjPF\VB7EYBdlpwjOVB4b8kATqXF\gSIOVSOa8iIBpkfEZ[ATZ[ACpSOuVPFb8S4bgSjtjk2HgStngivbYBdTjeI5qEbo17m0VyfEVwJEjBdDVS'
@'AegizujedOYyfEVSdbgidHgScOjwrEVedygSdnjwMEjBOxjk7EV[xEpS\OgiI\YBpOpXxEKKFe8k2DgizOpBd\VXFcVezOgSIOpS4mViMEZkzup'
@'w7EVedygiFDZknHgSOTgSItjKFb8ktOqPrvXOFyjwF\YBNE6kAtYavOVijOYyFbVyFe8wITjwvHgiI5jKFHYSdCpS4Cpk2\YPFJVepTjB4DVXFu'
@'jPFb8SNEKbIFqXF\YyFejKFyjk2OVaIDjwvHViJEjizmVSfE8knbVyFmpiMEZ[AyjKF\pXFBpk2DgivfjkdJg7bogrboKeznYiIu'
| invoke Dekrypt()
| project Result
ここにさっきのパネルの値を入れてメッセージを取得
メッセージ
Listen up, esteemed members of Kuanda, for we have encountered a slight hiccup in our grand scheme.
I can sense your concern, as rumors of our true intentions have reached the ears of the KDA.
But fear not, my loyal comrades, for we shall not waver from our path! If anything, we shall intensify our efforts until the KDA crumbles beneath our feet.
I cannot share too much at this time, but rest assured, we are running our "smoke tests", both figuratively and quite literally.
They shall expose the KDA's weaknesses and herald its epic downfall.Now, let us address the matter of my well-being. I understand that there is a great deal of curiosity regarding my safety.
Let me assure you, it was all a matter of impeccable timing. No doubt my connecting flight was an experience of a lifetime!
Too bad my luggage failed to join me on this thrilling journey! :)But fear not, my friends, leaving things to chance is not my style. I have assembled a team of loyal bodyguards,
who move with me like elusive phantoms, ensuring my invincibility. At any given time, at least two of them discreetly
shadow my every move, even during my exhilarating runs through the city. Truly, I feel untouchable. And let me tell you,
this city is a hidden gem! It offers an abundance of marvelous spots where one can indulge in a refreshing shake after
conquering a breathtaking 10K run. It is a perfect blend of mischief and rejuvenation, a delightful concoction that fuels my strength.So, my fellow rogues, let us keep our eyes fixed on the target. I will reveal more details about our plans in due time.
Prepare yourselves to witness the spectacular downfall of the KDA, as we relentlessly drill into its core at full speed!Krypto
At any given time, at least two of them discreetly
shadow my every move, even during my exhilarating runs through the city
ということで、同時に同じコースを走る人が3人以上いるそうで、
これまで使ってきた、geo_point_to_s2cell が有効に使えそう。
回答
セットアップ
.execute database script <|
.create-or-alter function Dekrypt(T:(Message:string, Key:string)) {
let lx=array_concat(range(48, 57, 1), range(65, 92, 1), range(97, 122, 1));
let uc = (_s:string) { unicode_codepoints_from_string(hash_sha256(_s))};
T
| extend c1 = uc(tolower(Key)), c2 = uc(toupper(Key))
| mv-apply with_itemindex=i l=lx to typeof(int), c=c1 to typeof(int) on (order by c asc, i asc | summarize c1=unicode_codepoints_to_string(make_list(l)))
| mv-apply with_itemindex=i l=lx to typeof(int), c=c2 to typeof(int) on (order by c asc, i asc | summarize c2=unicode_codepoints_to_string(make_list(l)))
| extend Result = coalesce(base64_decode_tostring(translate(c1, c2, Message)), "Failure: wrong key")
| project-away c1, c2
}
.create-or-alter function SecretCodeToKey(T:(c1:long,c2:long,c3:long,c4:long))
{
let magic_const = toscalar(T | summarize sum(c1)*sum(c2)/sum(c3)+sum(c4));
let key = toscalar(T
| summarize list = make_list(pack_array(c1, c2, c3, c4))
| project key=unicode_codepoints_to_string(series_add(list, magic_const)));
key
}
.create-merge table Runs (Timestamp:datetime, RunnerID:string, Distance:double, Duration:timespan, StartLat:double, StartLon:double)
.ingest async into table Runs (@'https://kustodetectiveagency.blob.core.windows.net/kda2c8run/log_00000.csv.gz')
.ingest into table Runs (@'https://kustodetectiveagency.blob.core.windows.net/kda2c8run/log_00001.csv.gz')
回答
Where can we catch the suspect? lan, lat
let runnerIds =
Runs
// 5分以内に3人以上が同じポイントをスタートした地点
| extend point = geo_point_to_s2cell(StartLon,StartLat,21) //21 = 3~5m
| summarize count() by bin(Timestamp,5m), point
| where count_ >= 3
| join (
Runs
// そのポイントをスタートしたランナー
| extend point = geo_point_to_s2cell(StartLon,StartLat,21)
| extend Timestamp5m = bin(Timestamp,5m)
) on point, $left.Timestamp == $right.Timestamp5m
// 絞る
| summarize _7dRunCount=count(), _7dAvgDistance=avg(Distance), _7dMinDistance=min(Distance), _7dMaxDistance=max(Distance) by bin(Timestamp,7d), RunnerID
| where _7dMinDistance >= 8
| where _7dMaxDistance <= 12
| where _7dRunCount between (3 .. 4)
| distinct RunnerID;
Runs
// 該当のランナーの出発地を確認
| where RunnerID in (runnerIds)
| extend link = strcat('https://www.google.com/maps/@', StartLat, ',', StartLon,
',3a,75y,252.01h,89.45t/data=!3m6!1e1!3m4!1s-1P!2e0!7i16384!8i8192')
あとはストリートビューで確認するとそれっぽい店がでるロケーションがある。
this city is a hidden gem! It offers an abundance of marvelous spots where one can indulge in a refreshing shake after
conquering a breathtaking 10K run. It is a perfect blend of mischief and rejuvenation, a delightful concoction that fuels my strength.
メッセージ的にもジュース屋を隠喩してる。
