Kusto Detective Agency Season 2 のメモ
の続き
Case 6 - Hack this rack!
問題
https://kuanda.org の新人が美術館で 1 週間 過ごす際の暗号を解読せよ的な内容。
Kusto のクエリ自体は難しくないが、その後のなぞなぞ？が非英語ネイティブには難しい。。。
Who is the leader of Kuanda.org?
12204/497 62295/24 50883/678 47108/107 193867/3,
45534/141 hidden 100922/183 143461/1 1181/505 46187/380.
41526/155 66447/199 30241/114, 33745/154 12145/387 46437/398 177191/131:
293/64 41629/1506 210038/432, 41612/803 216839/1.
404/258 rules 40/186 1472/222 122894/2 46081/105:
41594/650 32579/439 44625/141 184121/19 33254/348 357/273 32589/821,
46171/687 punctuations 62420/10 50509/48 1447/128,
176565/82'56721/591 561/225 insensitive, 30744/129 76197/32.
1319/42 41599/216 68/457 136016/146, 42420/126'46198/389 42429/158 40091/108 41667/252,
1515/555 177593/223 176924/73 45889/65 159836/96 35080/384 32578/199.
1607/167 124996/9 71/56, 1303/187 45640/1114 72328/247 75802/11,
1168/146 163380/12 57541/116 206122/738 365/267 46026/211 46127/19.
119295/425 45062/128 12198/133 163917/238 45092/8 54183/4 42453/82:
561/433 9/387 37004/287 1493/118 41676/38 163917/238 3159/118 63264/687
1/905 1493/109 43723/252, 136355/1 1159/134 40062/172 32588/604,
158574/1 45411/8 10/892 127587/175 - 633/9 72328/247 1514/615 42940/138.
164958/84 221014/479 151526/7 111124/138, 41668/206 34109/46 1514/555,
147789/2 3228/152 993/323 166477/167 178042/167, 50753/91'207786/8 12/372.
1108/158'42423/150 12/309 66154/9 213566/11 44981/158 1197/300
40184/149 92994/63-71071/179 75093/7 211718/18 74211/5 46144/399.
ポイント
文字列操作
Train me より
toupper や tolower など 文字列操作系の関数を使えと
extract、extract_all
正規表現で文字列を抜き出す
暗号解読
テーブル構造をまずは理解
12204/497 62295/24 などは ObjectIdとなにかの組み合わせっぽい。
Hint の内容からして、文字列操作で各レコードから単語を引き出せば良さそう。
候補としては、AssistiveText と ProvenanceText があるが、AssistiveText は存在しないレコードもあるので、ProvenanceTextで実施するとそれっぽい文章になる。
print Text =
```12204/497 62295/24 50883/678 47108/107 193867/3,
45534/141 hidden 100922/183 143461/1 1181/505 46187/380.
41526/155 66447/199 30241/114, 33745/154 12145/387 46437/398 177191/131:
293/64 41629/1506 210038/432, 41612/803 216839/1.
404/258 rules 40/186 1472/222 122894/2 46081/105:
41594/650 32579/439 44625/141 184121/19 33254/348 357/273 32589/821,
46171/687 punctuations 62420/10 50509/48 1447/128,
176565/82'56721/591 561/225 insensitive, 30744/129 76197/32.
1319/42 41599/216 68/457 136016/146, 42420/126'46198/389 42429/158 40091/108 41667/252,
1515/555 177593/223 176924/73 45889/65 159836/96 35080/384 32578/199.
1607/167 124996/9 71/56, 1303/187 45640/1114 72328/247 75802/11,
1168/146 163380/12 57541/116 206122/738 365/267 46026/211 46127/19.
119295/425 45062/128 12198/133 163917/238 45092/8 54183/4 42453/82:
561/433 9/387 37004/287 1493/118 41676/38 163917/238 3159/118 63264/687
1/905 1493/109 43723/252, 136355/1 1159/134 40062/172 32588/604,
158574/1 45411/8 10/892 127587/175 - 633/9 72328/247 1514/615 42940/138.
164958/84 221014/479 151526/7 111124/138, 41668/206 34109/46 1514/555,
147789/2 3228/152 993/323 166477/167 178042/167, 50753/91'207786/8 12/372.
1108/158'42423/150 12/309 66154/9 213566/11 44981/158 1197/300
40184/149 92994/63-71071/179 75093/7 211718/18 74211/5 46144/399.
`` `
| extend keyList = extract_all(@"(\d+/\d+)", Text)
| mv-expand with_itemindex=Index keyList
| project Index, kv = keyList,Text
| parse kv with k1 "/" k2
| extend k1 = tolong(k1),k2=tolong(k2)
| join kind = leftouter NationalGalleryArt on $left.k1 == $right.ObjectId
| project kv, k1,k2,ProvenanceText,Title,Index,Text
| extend words = extract_all(@"(\w+)", ProvenanceText)
| extend keyword = words[k2]
| order by Index asc
| summarize wordList = make_list(keyword), kvList = make_list(kv) by Text
| extend NewText = replace_strings(tolower(Text), kvList,wordList)
| project NewText
これで得られる NewText
in catalogue of titles Grand,
three hidden words Demand your Hand.
when found all, they form A line:
A clear timeline, simply Fine.
words rules are simple to Review:
at least three Letters have in view,
all punctuations Mark the End,
they're case insensitive, my friend.
to find all words, you'll need some skill,
seeking the popular will guide you still.
below The King, the first word mounts,
the Second shares with Third their counts.
reveal the last word with Wise thought:
take first two letters from word most sought
into marked dozen, and change just one,
and with those two - the word is done.
so search the titles, high and low,
and when you find it, you'll know.
you've picked the Image that revealed
the pass-code to the World concealed.
タイトルから頻出単語を探し出せば良さそう。
1つめのキーワード
seeking the popular will guide you still. below The King, the first word mounts
とのことなので、
まずは、king の次にシェアが多い単語
NationalGalleryArt
| extend titleWords = extract_all(@"(\w+)", Title)
| mv-expand with_itemindex=Index titleWords
| where strlen(titleWords) >= 3
| extend word = tostring(tolower(titleWords))
| summarize count() by word
| order by count_
| extend nextWord = next(word)
| where word == "king"
2つめのキーワード
the Second shares with Third their counts.
これは引っ掛け？third と同じシェアを持つ単語
NationalGalleryArt
| extend titleWords = extract_all(@"(\w+)", Title)
| mv-expand with_itemindex=Index titleWords
| where strlen(titleWords) >= 3
| extend word = tostring(tolower(titleWords))
| where word == "third"
| summarize c = count() by word
| join kind = leftouter (
NationalGalleryArt
| extend titleWords = extract_all(@"(\w+)", Title)
| mv-expand with_itemindex=Index titleWords
| where strlen(titleWords) >= 3
| extend word = tostring(tolower(titleWords))
| summarize c= count() by word
) on $left.c == $right.c
3 つめのキーワード
reveal the last word with Wise thought: take first two letters from word most sought into marked dozen, and change just one, and with those two - the word is done.
NationalGalleryArt
| extend titleWords = extract_all(@"(\w+)", Title)
| mv-expand with_itemindex=Index titleWords
| where strlen(titleWords) >= 3
| extend word = tostring(tolower(titleWords))
| summarize count() by word
| order by count_
| take 12
when found all, they form A line: A clear timeline, simply Fine.
とのことなので他のキーワードとの関連を見て、12 位 の manのいち文字を入れ替えて、the から2文字を足してやる、month で問題なさそう。
該当の作品を探す。
NationalGalleryArt
| extend lowerTitle = tolower(Title)
| where lowerTitle has "year" and lowerTitle has "month" and lowerTitle has "day"
| project Title,ImageUrl
これで 1 つの画像にたどり着く
セットアップ
.execute database script <|
// The data was obtained from the repository of the National Gallery of Art:
// https://github.com/NationalGalleryOfArt/opendata
.create-merge table NationalGalleryArt (ObjectId:long, Title:string, BeginYear:long, EndYear:long, Medium:string, Inscription:string, Attribution:string, AssistiveText:string, ProvenanceText:string, Creditline:string, Classification:string, ImageUrl:string)
//clear any previously ingested data if such exists
.clear table NationalGalleryArt data
.ingest into table NationalGalleryArt ('https://kustodetectiveagency.blob.core.windows.net/kda2c6art/artview.csv')
