Intro
Last time we tracked Krypto to Barcelona. This time, we’ve been asked again by NSO Agent Stas Fistuko - worked out the anagram yet? I think Stas is a double agent, I mean that name is obvs a bit too much fanboi, something #TeamProfSmoke would dream up - to do some code cracking and log hunting.
Cracking the Code
Training for the case pretty much gives the game away. We have to solve a magic number puzzle, but before we jump in with KQL, let’s ask Bing Chat about the Sagrada Familia and if it knows anything about Krypto’s code.
Ah, a cryptogram. Tell me more oh wise AI
Yes yes, but what are the numbers?
Well that was easy.
let city_code=datatable(c1:long,c2:long,c3:long,c4:long)
[1, 14, 14, 4,
11, 7, 6, 9,
8, 10, 10, 5,
13, 2, 3, 15];
print Key=SecretCodeToKey(city_code), Message=
....
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
The Data
Now we have the intell, let’s look at the data
Runs
| count
1075378 rows. Runtastic. And the schema?
Runs
| take 100
Ok, so we need to find the runners that do between 8K and 10K and 3-4 runs per week. From those we need to find the runs that have at least 3 runners (Krypto and his 2+ goons), but we can’t assume he always has the same goons with him.
Let’s start with the suspect runners
Runs
| where Distance between (8 .. 12)
| extend weekstart = startofweek(Timestamp)
| summarize runsperweek=count() by weekstart, RunnerID
| where runsperweek between (3 .. 4)
| distinct RunnerID
Easy.
Now let’s find all the 8K to 12K runs that start in a similar location. We’ll use H3 to do that. (11 is a resolution of 25m)
Runs
| where Distance between (8 .. 12)
| extend hex=geo_point_to_h3cell(StartLon,StartLat,11)
...
Ah, but wait a sec. We also need to work out how we can group runs that are close in time. bin() might work, but imagine a run that starts at 11:59 and another that starts at 12:01. If we bin to say 5mins, even though these runs are only 2 minutes apart they’d be in different bins.
After a bit of digging about in the doco, I came across the scan operator and an example for grouping sessions. This is exactly what we need.
Runs
| where Distance between (8 .. 12)
| extend hex=geo_point_to_h3cell(StartLon,StartLat,11)
| sort by hex, Timestamp asc
| scan with_match_id=session_id declare (sessionStart: datetime, sessionHex: string) with
(
step inSession: true => sessionStart = iff(isnull(inSession.sessionStart), Timestamp, inSession.sessionStart),
sessionHex = iff(isempty(inSession.sessionHex), hex, inSession.sessionHex) ;
step endSession output=none: Timestamp - inSession.sessionStart > 5m or inSession.sessionHex <> hex;
)
We can filter our sessions with less than 3 runners:
Runs
| where Distance between (8 .. 12)
| extend hex=geo_point_to_h3cell(StartLon,StartLat,11)
| sort by hex, Timestamp asc
| scan with_match_id=session_id declare (sessionStart: datetime, sessionHex: string) with
(
step inSession: true => sessionStart = iff(isnull(inSession.sessionStart), Timestamp, inSession.sessionStart),
sessionHex = iff(isempty(inSession.sessionHex), hex, inSession.sessionHex) ;
step endSession output=none: Timestamp - inSession.sessionStart > 5m or inSession.sessionHex <> hex;
)
| summarize runners=make_set(RunnerID) by session_id, hex
| where array_length(runners) > 2
OK, now we have the runs (or sessions) and we can start to put everything together.
let suspectrunners = Runs
| where Distance between (8 .. 12)
| extend weekstart = startofweek(Timestamp)
| summarize runsperweek=count() by weekstart, RunnerID
| where runsperweek between (3 .. 4)
| distinct RunnerID;
let sessions = Runs
| where Distance between (8 .. 12)
| extend hex=geo_point_to_h3cell(StartLon,StartLat,11)
| sort by hex, Timestamp asc
| scan with_match_id=session_id declare (sessionStart: datetime, sessionHex: string) with
(
step inSession: true => sessionStart = iff(isnull(inSession.sessionStart), Timestamp, inSession.sessionStart),
sessionHex = iff(isempty(inSession.sessionHex), hex, inSession.sessionHex) ;
step endSession output=none: Timestamp - inSession.sessionStart > 5m or inSession.sessionHex <> hex;
)
| summarize runners=make_set(RunnerID) by session_id, hex
| where array_length(runners) > 2;
sessions
| mv-expand runners to typeof(string)
| project-rename RunnerID = runners
| lookup kind=inner suspectrunners on RunnerID
| summarize Count=count() by RunnerID, hex
| top 3 by Count
| lookup kind=inner Runs on RunnerID
| project RunnerID, lon = round(StartLon, 5), lat = round(StartLat,5), Count
| distinct *
| extend url = strcat('https://www.google.com/maps/@', lat, ',', lon, ',3a,75y,252.01h,89.45t/data=!3m6!1e1!3m4!1s-1P!2e0!7i16384!8i8192')
| order by Count, RunnerID
Some explanation.
First we take the sessions and expand the runners. Then we filter to the suspectrunners and find their favourite starting cells. Kyrpto can be a little predictable, so I’m going to guess he’s one of the top 3 or so. We’ll lookup the runs for those runners, round the lat/lon to 5 decimal places (as shown on the case answer form) and create a google maps url.
Clicking the links and looking around, the most likely location seems to be:
And Kypto is uid1549166190602.
Nice
