Kusto Detective Agency: Hints and my experience

Challenges

Challenge 1: This article
Challenge 2: Here
Challenge 3: Here
Challenge 4: Here
Challenge 5: Here

So, what is the Kusto Detective Agency?

This set of challenges is an amazing, gamified way to learn the Kusto Query Language (KQL), which is the language used by several Azure services including Azure Monitor, Sentinel, M365 Defender and Azure Data Explorer (ADX) to name a few. Using your skills, you will help the citizens of Digitown solve mysteries and crimes to make the city a better place!

How do I get started?

The challenges are available here https://detective.kusto.io/, follow a few basic steps to get started by creating an ADX cluster here https://aka.ms/kustofree and copy the Cluster URI you need this as a part of the onboarding answer.

Now answer a simple question using KQL that being to calculate the sum of the “Score” column

If you are just getting started learning KQL check out Rod Trents ‘Must Learn KQL’ series!

https://aka.ms/MustLearnKQL https://azurecloudai.blog/2021/11/17/must-learn-kql-part-1-tools-and-resources/

as well as these cool resources

Watch this basic explainer on how the query language works: http://aka.ms/StartKqlVideo
Check out the documentation here: Kusto Query Language (KQL) overview- Azure Data Explorer | Microsoft Docs

For help with the first query click the spoiler tag below

Onboarding Query

Onboarding //This is the name of the table we will be running our query against
| summarize sum(Score) //the sum command will add up all the numbers in the “Score” column

General advice

Each challenge has up to three hints that can be accessed through the hints section of your Detective UI, the hints are quite useful, and I would recommend using them if you get stuck especially as some of them include information which is important to confirm assumptions. There are also different ways to get to the answers which shows the power of creative thinking.

Challenge 1: The rarest book is missing!

The first challenge is quite interesting you are tasked with finding a rare missing book. Most people I’ve spoken to have figured out the method but get stuck on the KQL query I’ve included an extra hint below to get you started.

Query Hint

In order to solve this you’ll need to work with the weights of the books on the selves.
KQL commands that will be helpful are sum() and join

The rarest book is missing challenge1 text

This was supposed to be a great day for Digitown’s National Library Museum and all of Digitown.
The museum has just finished scanning more than 325,000 rare books, so that history lovers around the world can experience the ancient culture and knowledge of the Digitown Explorers.
The great book exhibition was about to re-open, when the museum director noticed that he can’t locate the rarest book in the world:
“De Revolutionibus Magnis Data”, published 1613, by Gustav Kustov.
The mayor of the Digitown herself, Mrs. Gaia Budskott – has called on our agency to help find the missing artifact.

Luckily, everything is digital in the Digitown library:

– Each book has its parameters recorded: number of pages, weight.
– Each book has RFID sticker attached (RFID: radio-transmitter with ID).
– Each shelve in the Museum sends data: what RFIDs appear on the shelve and also measures actual total weight of books on the shelve.

Unfortunately, the RFID of the “De Revolutionibus Magnis Data” was found on the museum floor – detached and lonely.
Perhaps, you will be able to locate the book on one of the museum shelves and save the day?

Query challenge 1

//This query will calculate the weight of the books on each shelf and compare that to the weight registered by the sensor, find the shelf with extra weight and we’ll find our book!
Shelves
| mv-expand rf_ids
| extend RID = tostring(rf_ids)
| join (Books) on $left.RID == $right.rf_id
| summarize sum(weight_gram) by shelf, total_weight
| extend diff = total_weight – sum_weight_gram
| order by diff

I will be talking about the rest of the challenges in a later series so be sure to check back soon, in the meantime good luck Detective!

OpsMan

Cloud Monitoring and Management Blog