Challenges
I really enjoyed this week’s challenge, it has a fun story element and works well with some of the more interesting KQL features. I definitely get to keep my Kusto card after this one!
General advice
This time arounds the clues are not only crucial but also pretty cool, this case also has some similarities to case 4 from season 1 but if you aren’t super familiar with this KQL capability the “train me” does a good job of laying the groundwork.
Challenge: Case 7
Hi Detective,
It’s been awesome witnessing your progress. Seriously, you’ve climbed to new heights in uncovering the misdeeds of sly cyber-criminal, Krypto. We, the National Security Office (NSO), had our eyes on him for ages, and thanks to your information, we finally managed to track him down. I’ll spare you the thrilling details, but guess what? Turns out our guy held a high-ranking position as City Manager in the Mayor’s office, and he was tight with Ms. Gaia Budskott, the Mayor of Digitown. And yes, he’s also the mastermind behind the infamous KUsto ANti-Detective Agency (Kuanda.org) that you brilliantly exposed. However, here’s the unfortunate part: he slipped through the fingers of Digitown’s law enforcement. Given the new international nature of the case, we (the NSO) are taking over.
So, let’s cut to the chase. Time is of the essence, and we need your expertise and experience to help us find the final destination of Krypto.
While we have gathered significant information about him, it is not enough to capture him. Our sources indicate that he was spotted at the Doha airport on August 11, 2023, between 03:30 AM and 05:30 AM (UTC). However, by the time our agents arrived, he had already made his escape, presumably utilizing a private jet. We have deployed dozens of officers to all potential landing destinations, but he has evaded us so far. We have a single lead that suggests Krypto may have attempted a plane-to-plane jump, given his skills as a wingsuit expert. Here is where we got stuck.
Fortunately, we have you (and full access to the public and private jet plane schedules on this day). Your mission, should you choose to accept it, is to determine the destination to which Krypto has fled.
Hoping to hear back from you soon,
NSO Agent Stas Fistuko
Alright detectives lets find that fugitive!
This is a geo challenge and you’re going to need to check out the geo_point_to_h3cell and geo_point_to_s2cell commands for their awesome capabilities.
Solution – Spoilers below
Well, we know the point and time of departure and we also know some pretty fancy flying would have to take place to allow Krypto to jump between planes Mission Impossible style.
//First we need to find the first airport, you can use the code but the municipality works just as well
Airports
| where municipality == “Doha”
| project lat, lon
//Then we need to find flights leaving from Doha, between 03:30 AM and 05:30 AM, that have a close encounter with another plane where our suspect flight is the one flying above the other plane.
let doha=
Flights
| where Timestamp between (datetime(2023-08-11T03:30:00Z) .. datetime(2023-08-11T05:30:00Z))
| where onground==true
| summarize callsign=make_set(callsign) by geo_point_to_s2cell(51.608056,25.273056,12)
| mv-expand callsign
| extend callsign=tostring(callsign)
| distinct callsign;
let potentialplanes=
Flights
| where Timestamp between (datetime(2023-08-11T05:30:00Z) .. now() )
| where callsign in (doha)
| summarize Planes=make_set(callsign), Heights=make_set(geoaltitude) by geo_point_to_s2cell(lat,lon,15), bin(Timestamp, 1m)
| extend CountofPlanes=array_length(Planes),HeightCount=array_length(Heights)
| where CountofPlanes == 2
| where HeightCount == 2
| extend H1=toint([‘Heights’][0]), H2=toint([‘Heights’][1])
| extend HeightDelta=H1-H2
| where HeightDelta >=0 and HeightDelta<=10
| mv-expand Planes
| extend callsign=tostring(Planes)
| distinct callsign;
Flights
| summarize arg_max(Timestamp, *) by callsign
| where onground== true
| where callsign in~ (potentialplanes)
| extend key=geo_point_to_s2cell(lon, lat, 13)
| join kind=inner(
Airports
| extend key=geo_point_to_s2cell(lon, lat, 13)
) on key
| distinct municipality
//Bingo one suspect and it’s flying to Barcelona!
Bonus observation: I noticed the name of our new friend NSO Agent Stas Fistuko looked a little strange, turned out it’s an anagram for Kusto is fast and I couldn’t agree more!
Great job detectives, with 3 cases to go we’re hot on the trail of Krypto and Kuanda, hopefully we can catch them before they do any real damage!
Note: There are some fun things you can do with mapping the flights onto a map in order to see the two places meet up and where they go, there’s a couple of ways to do this too and it’s a fun one to try and figure out.
Pingback: Kusto Detective Agency Season 2: Case 6 - Hack this rack! - OpsMan