Monthly Archives: November 2022

Kusto Detective Agency: Challenge 5 – Big heist


The ADX team upped their game once again. Time for a proper forensic investigation, track down the baddies, find clues and decipher their meaning all while racing against the clock. Can you come up with the date and location of the heist in time to stop them?

General advice

This challenge requires a bit of creative thinking, even with the hints there are multiple paths to go down and not all of them are going to lead to the right outcome. the key to this one, keep it simple and logical.

Challenge 5: Big heist

This challenge also has multiple parts, first we need to identify four chatroom users from over three million records and then we need to “hack” their IPs to get more clues.

Query Hint Part 1

Trying to identify the right user behaviors here is super tricky, I got tripped up here by adding a level of complexity that was unnecessary. At its simplest we would have to find a room where only 4 people joined and no one else. Some KQL commands that will be useful here are tostring, split, extend, row_cumsum

Big heist challenge text - Part 1

Hello. It’s going to happen soon: a big heist. You can stop it if you are quick enough. Find the exact place and time it’s going to happen.
Do it right, and you will be rewarded, do it wrong, and you will miss your chance.

Here are some pieces of the information:
The heist team has 4 members. They are very careful, hide well with minimal interaction with the external world. Yet, they use public chat-server for their syncs. The data below was captured from the chat-server: it doesn’t include messages, but still it may be useful. See what you can do to find the IPs the gang uses to communicate.
Once you have their IPs, use my small utility to sneak into their machine’s and find more hints:<ip>

El Puente

Feeling uncomfortable and wondering about an elephant in the room: why would I help you?
Nothing escapes you, ha?
Let’s put it this way: we live in a circus full of competition. I can use some of your help, and nothing breaks if you use mine… You see, everything is about symbiosis.
Anyway, what do you have to lose? Look on an illustrated past, fast forward N days and realize the future is here.

Query challenge 5 - Part 1

let rooms =
| where Message contains “joined”
| extend user = tostring(split(Message,” “,1))
| extend chan = tostring(split(Message,” “,5))
| distinct user, chan
| summarize count() by chan
| where count_ == 4
| project chan;
let chatroom =
| extend action = tostring(split(Message,” “,2))
| where action contains “joined” or action contains “left”
| extend A1 = iif(action contains “joined”, 1, -1)
| extend user = tostring(split(Message,” “,1))
| extend chan = tostring(split(Message,” “,5))
| where chan in (rooms)
| order by Timestamp asc
| extend total=row_cumsum(A1, chan != prev(chan))
| where total ==4
| distinct chan;
let users =
| extend chan = tostring(split(Message,” “,5))
| where chan in (chatroom)
| extend user = tostring(split(Message,” “,1))
| distinct user;
| extend user = tostring(split(Message,” “,1))
| where user in (users)
| where Message contains “logged”
| extend IP = tostring(split(Message,” “,5))
| distinct IP

Alright we’ve got some IPs, so time to “hack”, using the provided tool you’ll gather a set of clues from each of the gang members, there are a few key things you need to find, these are an email, some pictures, a cypher tool, an article and a pdf copy of it and of course a video from the nefarious professor Smoke.

From here on out it’s all investigative skills, you now have everything you need to find the date and location of the heist and save that datacenter!

Final hint

In order to decrypt the secret message, you’re going to need a special key, the format looks familar right? Spot on you’ll need the answer from challenge 4!

Congratulations Detective!

If you’ve found this blog series useful, please let me know via LinkedIn or drop a comment below. These challenges have been super fun and I for one am looking forward to season 2!


Kusto Detective Agency: Challenge 4 – Ready to play?


Just when you thought these challenges couldn’t get any cooler along comes your very own nemesis and a multi-part puzzle taking you on a street tour of New York City.

General advice

First, we need to import the data ourselves this time around, using Ingest from Blob under our data blade, you can also change the column name I used “Primes”
Calculating the prime numbers can be a little tricky as our free ADX cluster requires us to be clever with our query in order to allow it to complete, luckily, we get a free lesson on “special primes”

Challenge 4: Ready to play?

This challenge has two parts and we’ll look at them in turn, first we need to identify a specific prime number and then use that to get the second clue and then we have to find a specific area in New York City,

Query Hint Part 1
Calculating the largest special prime under 100M can be done in a variety of ways, the trick is working within the limited capacity of our free ADX cluster. KQL commands that are useful are serialize, prev, next and join
Ready to play? challenge text - Part 1

Hello. I have been watching you, and I am pretty impressed with your abilities of hacking and cracking little crimes.
Want to play big? Here is a prime puzzle for you. Find what it means and prove yourself worthy.


Start by grabbing Prime Numbers from and educate yourself on Special Prime numbers (, this should get you to{Largest special prime under 100M}

Once you get this done – you will get the next hint.

El Puente.

Query challenge 4 - Part 1

//Method 1 – This query will calculate the largest prime under 100M using the Sieve of Eratosthenes method to test each prime

| serialize
| order by Primes asc
| extend prevA = prev(Primes,1)
| extend NextA = next(prevA,1)
| extend test =  prevA + NextA + 1
| where test % 2 != 0 // skip even numbers
| extend divider = range(3, test/2, 2) // divider candidates
| mv-apply divider to typeof(long) on
  summarize Dividers=countif(test % divider == 0) // count dividers
| where Dividers == 0 // prime numbers don’t have dividers
| where test < 100000000 and test > 99999000
| top 1 by test

//Method 2 – This query will calculate the largest prime under 100M by comparing special primes to the data set of all prime numbers

| serialize
| project specialPrime = prev(Primes) + Primes + 1
| join kind=inner (Challenge4) on $left.specialPrime == $right.Primes
| where specialPrime < 100000000
| top 1 by Primes desc

Now that we have our prime number we can move on to part 2
Largest special prime under 100m

The number we want is 99999517 so we can now head over to

A-ha a message from our nemesis and we need to meet them in a specific area marked by certain types of trees!

Ready to play? challenge text - Part 2

Well done, my friend.
It's time to meet. Let's go for a virtual sTREEt tour...
Across the Big Apple city, there is a special place with Turkish Hazelnut and four Schubert Chokecherries within 66-meters radius area.
Go 'out' and look for me there, near the smallest American Linden tree (within the same area).
Find me and the bottom line: my key message to you.

El Puente.

PS: You know what to do with the following:


.execute database script <|
// The data below is from 
// The size of the tree can be derived using 'tree_dbh' (tree diameter) column.
.create-merge table nyc_trees 
       (tree_id:int, block_id:int, created_at:datetime, tree_dbh:int, stump_diam:int, 
curb_loc:string, status:string, health:string, spc_latin:string, spc_common:string, steward:string,
guards:string, sidewalk:string, user_type:string, problems:string, root_stone:string, root_grate:string,
root_other:string, trunk_wire:string, trnk_light:string, trnk_other:string, brch_light:string, brch_shoe:string,
brch_other:string, address:string, postcode:int, zip_city:string, community_board:int, borocode:int, borough:string,
cncldist:int, st_assem:int, st_senate:int, nta:string, nta_name:string, boro_ct:string, ['state']:string,
latitude:real, longitude:real, x_sp:real, y_sp:real, council_district:int, census_tract:int, ['bin']:int, bbl:long)
with (docstring = "2015 NYC Tree Census")
.ingest async into table nyc_trees ('')
.ingest async into table nyc_trees ('')
.ingest async into table nyc_trees ('')
// Get a virtual tour link with Latitude/Longitude coordinates
.create-or-alter function with (docstring = "Virtual tour starts here", skipvalidation = "true") VirtualTourLink(lat:real, lon:real) { 
	print Link=strcat('', lat, ',', lon, ',4a,75y,32.0h,79.0t/data=!3m7!1e1!3m5!1s-1P!2e0!5s20191101T000000!7i16384!8i8192')
// Decrypt message helper function. Usage: print Message=Decrypt(message, key)
.create-or-alter function with 
  (docstring = "Use this function to decrypt messages")
  Decrypt(_message:string, _key:string) { 
    let S = (_key:string) {let r = array_concat(range(48, 57, 1), range(65, 92, 1), range(97, 122, 1)); 
    toscalar(print l=r, key=to_utf8(hash_sha256(_key)) | mv-expand l to typeof(int), key to typeof(int) | order by key asc | summarize make_string(make_list(l)))};
    let cypher1 = S(tolower(_key)); let cypher2 = S(toupper(_key)); coalesce(base64_decode_tostring(translate(cypher1, cypher2, _message)), "Failure: wrong key")

Using the census data, we now need to figure out the location in the clue, luckily, it’s only a KQL query away

Query Hint - Part 2
Getting the right size area can be tricky, a KQL command that will be extremely helpful will be geo_point_to_h3cell

Query challenge 4 - Part 2

//This query will filter a specific size area until it makes the set of trees given in the clue

let locations =
| extend h3cell = geo_point_to_h3cell(longitude, latitude, 10)
| where spc_common == “‘Schubert’ chokecherry”
| summarize count() by h3cell, spc_common
| where count_ == 4
| summarize mylist = make_list(h3cell);
let final =
| extend h3cell = geo_point_to_h3cell(longitude, latitude, 10)
| where h3cell in (locations)
|where spc_common ==  “Turkish hazelnut” or spc_common == “American linden”
| summarize count() by h3cell, spc_common
| where spc_common == “Turkish hazelnut” and count_ ==1
| project h3cell;
| extend h3cell = geo_point_to_h3cell(longitude, latitude, 10)
| where h3cell in (final)
| where spc_common == “American linden”
| top 1 by tree_dbh asc
| project latitude, longitude
| extend TourLink = strcat(‘’, latitude, ‘,’, longitude, ‘,4a,75y,32.0h,79.0t/data=!3m7!1e1!3m5!1s-1P!2e0!5s20191101T000000!7i16384!8i8192’)

Now that we have a location, we’re not done yet and here’s where the fun really starts, using our generated link will take us on a “Tour of the City” and give us a google maps street view link. Have a look around for our mysterious “El Puente” you may need to walk around a little bit.

Now that we’ve found the message, there’s one more thing we need to do and that’s to use the decrypt function to figure out the message from out detective portal, this part was a little tricky and took a few tries to get the right string to use.

Decryption Key

Using the mural the phrase we are looking for is “ASHES to ASHES”

There we have it, another secret message! Keep a hold of this answer as you’ll need it to complete the final challenge.

Well done Detective, we’ve been on quite the journey. See you in the next challenge!


Kusto Detective Agency: Challenge 3 – Bank robbery!


I must admit that the difficulty spike on the challenges is both refreshing and surprising. The level of care that went into crafting each of these scenarios is outstanding and the ADX team have certainly outdone themselves, if you like these cases as much as I do you can let the team know at

General advice

Again, this case requires some pretty heavy assumptions to solve, some of which the hints will give you clarity on. It’s very easy when trying to solve the bank robbery to end up with a very overcomplicated solution that may take you in the wrong direction, try keep this one simple.

Challenge 3: Bank robbery!

For this challenge you need to track down the hideout of a trio of bank robbers, it seems simple, you have the address of the bank and are provided with all the traffic data for the area now it’s just a case of figuring out where the robbers drove off to.

Query Hint
The trick with this challenge is you need to be able to create a set of vehicles that weren’t moving during the robbery, of course the catch is that only moving vehicles have records in the traffic data. KQL commands that will be useful for this challenge are join, remember that there are different kinds of joins and arg_max

Bonus cool tip

Thanks to my colleague Rogerio Barros for showing me this one because it is awesome! Due to the nature of the traffic data, it is actually possible to plot the route of any number of cars using | render scatterchart. Below is a visual representation of three random cars as they move about Digitown, this is quite interesting once you have identified the three suspects.

Bank robbery challenge text

We have a situation, rookie.
As you may have heard from the news, there was a bank robbery earlier today.
In short: the good old downtown bank located at 157th Ave / 148th Street has been robbed.
The police were too late to arrive and missed the gang, and now they have turned to us to help locating the gang.
No doubt the service we provided to the mayor Mrs. Gaia Budskott in past – helped landing this case on our table now.

Here is a precise order of events:

  • 08:17AM: A gang of three armed men enter a bank located at 157th Ave / 148th Street and start collecting the money from the clerks.
  • 08:31AM: After collecting a decent loot (est. 1,000,000$ in cash), they pack up and get out.
  • 08:40AM: Police arrives at the crime scene, just to find out that it is too late, and the gang is not near the bank. The city is sealed – all vehicles are checked, robbers can’t escape. Witnesses tell about a group of three men splitting into three different cars and driving away.
  • 11:10AM: After 2.5 hours of unsuccessful attempts to look around, the police decide to turn to us, so we can help in finding where the gang is hiding.

Police gave us a data set of cameras recordings of all vehicles and their movements from 08:00AM till 11:00AM. Find it below.

Let’s cut to the chase. It’s up to you to locate gang’s hiding place!
Don’t let us down!

Query challenge 3

//This query will calculate a set of cars not moving during the robbery, which then started moving after it occurred and track vehicles heading to the same address

let Cars =
| where Street == 148 and Ave == 157
| where Timestamp > datetime(2022-10-16T08:31:00Z) and Timestamp < datetime(2022-10-16T08:40:00Z) | join kind=leftanti ( Traffic | where Timestamp >= datetime(2022-10-16T08:17:00Z) and Timestamp <= datetime(2022-10-16T08:31:00Z)
) on VIN
| summarize mylist = make_list(VIN);
| where VIN in (Cars)
| summarize arg_max(Timestamp, *) by VIN
| summarize count(VIN) by Street, Ave
| where count_VIN == 3

Now just wait for the police to swoop in and recovery the stolen cash, another job well done detective!


Kusto Detective Agency: Challenge 2 – Election fraud in Digitown!


These challenges are a fantastic hackathon approach to learning KQL, every week poses a new and unique approach to different KQL commands and as the weeks progress, I’ve learned some interesting tricks. Let’s take a look at challenge 2.

General advice

I’ve mentioned previously that there are hints that can be accessed from the detective UI, from this challenge onwards the hints provide critical information and without them there are assumptions you need to make, which if incorrect will throw you off the correct solution.

This is also the first challenge that has multiple mays to get to the answer, in this post i will be discussing the more interesting one.

Challenge 2: Election fraud?

The second challenge ramps up the difficulty, you’ve been asked to verify the results of the recent election for the town mascot.

Query Hint
In order to solve challenge, you need to be figure out if any of the votes are invalid and if any are, removed them from the results.
KQL commands that will be helpful are anomaly detection, particularly series_decompose_anomalies and bin, alternatively you can also make use of format_datetime and a little bit of guesswork
Election Fraud challenge text

Query challenge 2

//This query will analyze the votes for the problem candidate and look for anomalies, if any are found they will be removed from the final count give the correct results for the election!

let compromisedProxies = Votes
| where vote == “Poppy”
| summarize Count = count() by bin(Timestamp, 1h), via_ip
| summarize votesPoppy = make_list(Count), Timestamp = make_list(Timestamp) by via_ip
| extend outliers = series_decompose_anomalies(votesPoppy)
| mv-expand Timestamp, votesPoppy, outliers
| where outliers == 1
| distinct via_ip;
| where not(via_ip in (compromisedProxies) and vote == “Poppy”)
| summarize Count=count() by vote
| as hint.materialized=true T
| extend Total = toscalar(T | summarize sum(Count))
| project vote, Percentage = round(Count*100.0 / Total, 1), Count
| order by Count

Digitown can sleep easy knowing that they have their correct town mascot due to your efforts! Stay tuned for some excitement in challenge 3.