Learn KQL with Kusto Detective Agency!

by Posted on

What is Kusto Detective Agency? It is a set of challenges which you solve by using KQL (Kusto Query Language) and Azure Data Explorer. It is a great way to start learning and have some fun while doing that. As it often happens, challenges are easier in the beginning but get harder and harder towards the end and I can say that last challenges are pretty hardcore especially if you are not that familiar with KQL.

How can I get started? Just browse to https://detective.kusto.io/, follow the instructions and you’ll be solving puzzles in no time. 🙂

Onboarding

First task you are given is to create a free Kusto cluster for you to use during upcoming challenges. You can create your cluster at https://aka.ms/kustofree. Note that you need to have either Microsoft Account (MSA) or Azure Active Directory (AAD) identity. After you have added your Cluster URI into the field of the task you can start with the onboarding assignment.

In the onboarding task you need to sum up the values in ‘Score’ column. You can do this by using summarize operator and sum() function. After you add correct answer in the field you will get your first badge.

Onboarding
| summarize TotalNumber = sum(Score)

Case #1

When you have completed onboarding task it’s time to dive in to your first real case! A rare book has gone missing and you’ll need to use your KQL skills to find it. You have been provided with table of 325000 books and their locations in shelves.

There are three hints for this case so remember to check them if you get stuck!

You will need to find out which shelf’s total weight doesn’t add up. First, of course, you need to find out what is the weight of this rare book that we are looking for. This can be easily achieved using where operator and searching for book name from ‘book_title’ column or author name from ‘author’ column. Or if you are just lazy you can search any column that contains part of the name.

Books
| where * has "Kustov"

After finding out what is the weight of the book we are looking for it’s time to proceed. Next thing is to figure out which self doesn’t have enough weight on it. Here we need to take advantage of operators like mv-expand, lookup and project. Of course we also need the ones we used in the onboarding task. I didn’t try to find just that one shelf but sorted all of the selves so that biggest difference between weight of the books and shelf’s total weight was on the top.

Shelves
| mv-expand rf_id = rf_ids to typeof(string)
| lookup Books on rf_id
| project shelf, weight_gram, total_weight
| summarize booksweight=sum(weight_gram) by shelf, totalweight=total_weight
| where totalweight != booksweight
| project difference = totalweight-booksweight, shelf
| sort by difference

Case #2

TBA

Case #3

TBA

Case #4

TBA

Case #5

TBA

Published by jukka