The Case
El Puente has been digging into Kuanda and turned up some spicy intel! He’s found some super secret on-boarding message to decode (I bet it’s just a link to a Teams meeting on how to setup the office printer or something).
Anyway, he’s sent it over to us to decode and hopefully spring the banged up Major out of the slammer.
The Data
We have a dump of the National Gallery Art collection data to use to decode the message. As usual, we’ll start with a quick look at the data
NationalGalleryArt
| count
138910 rows.
And the first few rows?
NationalGalleryArt
| take 50
Neat. So, that message. It looks suspiciously like a Book Cypher.
Let’s write it to a table for later:
.set-or-append Instructions <|
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.```
I’m guessing by the shape of the NGA data, we’ll be using the ProvenanceText field as our ‘book’. The first part of the message encoding seems to map to the ObjectId and the second, the word in the ProvenanceText? Let’s crack that open and make a key of ObjectId and word Index:
NationalGalleryArt
| project ObjectId, word = extract_all(@'(?m)(\w+)', ProvenanceText)
| mv-expand with_itemindex=Index word
| project key=strcat(ObjectId,'/',Index), word
Ugh, regex. (This is just a simple multiline word match in case you were wondering).
Some people, when confronted with a problem, think “I know, I’ll use regular expressions.” Now they have two problems. - Jamie Zawinski
https://blog.codinghorror.com/regular-expressions-now-you-have-two-problems/
Cool. Now we have two problems, but also table we can use to decode the Instructions.
Let’s split up the Instructions keys with another regex (now I have 4 or 5 moments problems)
Instructions
| extend key=extract_all(@"(\d+/\d+)", Text)
| mv-expand key to typeof(string)
...
This will extract up those ObjectId/Index pairs into a single column.We can now join the two tables together on the key:
Instructions
| extend key=extract_all(@"(\d+/\d+)", Text)
| mv-expand key to typeof(string)
| join kind = leftouter ( NationalGalleryArt
| project ObjectId, word = extract_all(@'(?m)(\w+)', ProvenanceText)
| mv-expand with_itemindex=Index word
| project key=strcat(ObjectId,'/',Index), word
) on key
...
…and finally, we can replace the code in the Instructions with the words from the NationalGalleryArt table using replace_strings
Instructions
| extend key=extract_all(@"(\d+/\d+)", Text)
| mv-expand key to typeof(string)
| join kind = leftouter ( NationalGalleryArt
| project ObjectId, word = extract_all(@'(?m)(\w+)', ProvenanceText)
| mv-expand with_itemindex=Index word
| project key=strcat(ObjectId,'/',Index), word
) on key
| project key, word, Text
| summarize key=make_list(key), word=make_list(word) by Text
| project message = replace_strings(Text, key, word)
Ah, but why not just chuck the keys and words we extracted from the NationalGalleryArt table into two lists and do the replace_strings using that? Well,make_list has a size limitation. In order to fit into ‘‘make_list’ you’d have to filter to those keys in the Instructions table, and by the time you’ve done that, you’ve effectively done a join anyway.
So now we have the message decoded.
to find all words, you’ll need some skill,
“No Sh*t Sherlock”
Finding the Art
in catalogue of titles Grand,
three hidden words Demand your Hand.
when found all, they form A line:
A clear timeline, simply Fine.
We’re looking for three words in the titles of the catalogue, that form “a clear timeline”. Hmm. I’m super good at overthinking these puzzles, let’s try to not do that this time.
words rules are simple to Review:
at least three Letters have in view,
all punctuations Mark the End,
they’re case insensitive, my friend.
Ok, all at least 3 characters long, end with punctuation and case insensitive. Got it.
Let’s start with the titles. We can extract all the words with more than 3 letters with yet more regex (\w{3,}), and count and rank them:
let T = NationalGalleryArt
| extend word=extract_all(@"(\w{3,})", Title)
| mv-expand word to typeof(string)
| project word=tolower(word), ObjectId
| summarize freq=count() by word
| sort by freq desc
| extend rank=row_rank_min(freq);
Sweet
to find all words, you’ll need some skill,
seeking the popular will guide you still.
below The King, the first word mounts,
Is that “The King” as the title, then take the next title by title length? Or is it the next in the artist’s portfolio? Perhaps “below” means to look in the inscription? Or maybe it’s in a portrait of Elvis? Or Professor Smoke (or glorious leader) has been crowned?
Overthinking it again, I actually wrote a dozen queries testing out each one of those. Duh.
“Below the King” Oh, it’s just the the word after King? next() will return the next row in a serialized table, so we can use that to get the next ranked word:
let T = NationalGalleryArt
| extend word=extract_all(@"(\w{3,})", Title)
| mv-expand word to typeof(string)
| project word=tolower(word), ObjectId
| summarize freq=count() by word
| sort by freq desc
| extend rank=row_rank_min(freq);
T
| serialize next=next(word)
| where word=="king"
| project wordOne = next
the Second shares with Third their counts.
Yeah this one threw me for a bit. For a while I thought it was the third tie in ranking, and spent far too long chasing that rabbit (did I mention overthinking???). But no, it’s as easy as it sounds. | where word=="third"
let T = NationalGalleryArt
| extend word=extract_all(@"(\w{3,})", Title)
| mv-expand word to typeof(string)
| project word=tolower(word), ObjectId
| summarize freq=count() by word
| sort by freq desc
| extend rank=row_rank_min(freq);
T
| where freq in (
(T
| where word=="third"
| project freq)
)
| project wordTwo = word
I’m going to guess that the second word is year (in keeping with the time theme).
Now the last word:
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, take the first and twelfth ranked world and mush them together into something Wibby Wobbly Timey Wimey.
Let’s put all the words together in a single query:
let T = NationalGalleryArt
| extend word=extract_all(@"(\w{3,})", Title)
| mv-expand word to typeof(string)
| project word=tolower(word), ObjectId
| summarize freq=count() by word
| sort by freq desc
| extend rank=row_rank_min(freq);
T
| serialize next=next(word)
| where word=="king"
| project wordOne = next
| extend wordTwo = toscalar( T | where freq in ( (T | where word=="third" | project freq) ) | project word)
| extend wordThreeA = toscalar( T | where rank == 1 | project word)
| extend wordThreeB = toscalar( T | where rank == 12 | project word)
Seems like the crims are Tay Tay fans. See? I told you I’m #TeamProfSmoke. Anyway, the and man can be munged into month. Noice.
Finding the right picture is simple:
NationalGalleryArt
| where Title has "year" and Title has "month" and Title has "day"
Club Tropicana
Next up, breaking into Kuanda Club Tbh, this hacking seems very sketchy, and probs in breach of s.478.1(1) of the criminal code. But don’t worry, you can suntan.
Lol, Phishing Fishing. I see what you did there. Let’s try to log in:
I’ve no idea what the passcode is (“Smoke4Lyfe” nope… “I<3ProfSmoke” nope…>), but I’m guessing there’s a clue in the site code.
F12….clicky click…aha! It just happens that the URL for the painting matches the filter in the debouncesHintUrl function (and look, yay, more regex)
Pasting in the URL for the painting gives us octopus holding the passcode:
And we’re in!
Case Solved!
