Monthly Archives: May 2023

Kusto Detective Agency Season 2: Case 1 – To bill or not to bill?


Click for challenges

  • Onboarding: Here
  • Challenge 1: This article
  • Challenge 2: Here
  • Challenge 3: Here
  • Challenge 4: Here
  • Challenge 5: Here
  • Challenge 6: Here
  • Challenge 7: Here
  • Challenge 8: Here
  • Challenge 9: Coming soon
  • Challenge 10: Coming soon

In this first case we’re asked to solve a billing problem, not the most exciting thing but certainly interesting with some real-world applications for the use of data. I quite enjoyed this challenge as it reminded me to keep things simple and not discount any ideas as silly just yet.

General advice

For this case the wording tripped me up a little bit, make sure you understand what’s being asked and check out the training if necessary. I will say, while I like the idea of the training it eventually put me on the wrong track, so use it but also keep an open mind.

Challenge: Case 1

Case 1 challenge text

Dear Detective,

Welcome to the Kusto Detective Agency! We’re thrilled to have you on board for an exciting new challenge that awaits us. Get ready to put your detective skills to the test as we dive into a perplexing mystery that has struck Digitown.

Imagine this: It’s a fresh new year, and citizens of Digitown are in an uproar. Their water and electricity bills have inexplicably doubled, despite no changes in their consumption. To make matters worse, the upcoming mayoral election amplifies the urgency to resolve this issue promptly.

But fear not, for our esteemed detective agency is on the case, and your expertise is vital to crack this mystery wide open. We need your keen eye and meticulous approach to inspect the telemetry data responsible for billing, unravel any hidden errors, and set things right.

Last year, we successfully served Mayor Gaia Budskott, leaving a lasting impression. Impressed by our work, the city has once again turned to us for assistance, and we cannot afford to disappoint our client.

The city’s billing system utilizes SQL (an interesting choice, to say the least), but fret not, for we have the exported April billing data at your disposal. Additionally, we’ve secured the SQL query used to calculate the overall tax. Your mission is to work your magic with this data and query, bringing us closer to the truth behind this puzzling situation.

Detective, we have complete faith in your abilities, and we are confident that you will rise to the occasion. Your commitment and sharp instincts will be instrumental in solving this enigma.

Captain Samuel Impson.

Right let’s get down to business and get the citizens of Digitown their correct bills!

Query Hint

There are two things wrong with the billing run this month and you’ll have to find both to get the right answer. KQL commands that will be useful are arg_min and distinct.

There is a bit of an investigation that needs to be done to uncover the issues with the data and there are various angles you can take, such as looking at specific houses or dates just to name a couple.

Solution – Spoilers below

Have you found the two things wrong with the billing?

Query Case 1

//The most obvious issue is that some customers are being double billed, so we need to remove those duplicates. Also, it turns out that some customers are using negative water and electricity, what doesn’t seem possible so let’s get rid of that too.

| where Consumed > 0  
| distinct Timestamp,HouseholdId,MeterType, Consumed
| summarize TotalConsumed = sum(Consumed) by MeterType  
| lookup Costs on MeterType  
| extend TotalCost = TotalConsumed*Cost  
| summarize sum(TotalCost)

Great work detectives! This case gave me a nice opportunity to stretch my “KQL legs” and i found it to be a fun experience. I’m definitely looking forward to the next one.


Kusto Detective Agency Season 2 – Onboarding


Click for challenges

  • Onboarding: This article
  • Challenge 1: Here
  • Challenge 2: Here
  • Challenge 3: Here
  • Challenge 4: Here
  • Challenge 5: Here
  • Challenge 6: Here
  • Challenge 7: Here
  • Challenge 8: Here
  • Challenge 9: Coming soon
  • Challenge 10: Coming soon

It’s exciting to have another season of the Kusto Detective Agency, this is an excellent way to learn KQL and gain some useful skills that are useful with many Microsoft products including Azure MonitorSentinelM365 Defender and Azure Data Explorer (ADX) to name a few.

General advice

If like me, you’re still in full detective mode from last season, then take a moment to reset your “complexity level” we’re starting again with the basics and it’s best to approach these 10 challenges in that way, from simple to complex.

Challenge: Onboarding

Onboarding challenge text

If you have been here for Season 1, you may be surprised to find yourself as a Rookie again. You see, it’s all about innovation and hitting refresh. So, it’s a fresh start for everyone. Yet we believe in excellence and that’s why we need your detective skills to unveil the crème de la crème of detectives from the past year, 2022. This is like the ultimate leaderboard challenge where we crown the “Most Epic Detective of the Year.” Exciting, right?

Imagine our agency as a buzzing beehive, like StackOverflow on steroids. We have a crazy number of cases popping up every day, each with a juicy bounty attached (yes, cold, hard cash!). And guess what? We’ve got thousands of Kusto Detectives scattered across the globe, all itching to pick a case and earn their detective stripes. But here’s the catch: only the first detective to crack the case gets the bounty and major street cred!

So, your mission, should you choose to accept it, is to dig into the vast archives of our system operation logs from the legendary year 2022. You’re on a quest to unearth the absolute legend, the detective with the biggest impact on our business—the one who raked in the most moolah by claiming bounties like a boss!

Feeling a bit rusty or want to level up your Kusto skills? No worries, my friend. We’ve got your back with the “Train Me” section. It’s like a power-up that’ll help you sharpen your Kusto-fu to tackle each case head-on. Oh, and if you stumble upon a mind-boggling case and need a little nudge, the “Hints” are there to save the day!

Now, strap on your detective hat, embrace the thrill, and get ready to rock this investigation. The fate of the “Most Epic Detective of the Year” rests in your hands!

Good luck, rookie, and remember to bring your sense of humor along for this wild ride!

Lieutenant Laughter

To get started we simply need to identify the detective who won the most bounties from season 1, luckily we have everything we need to get started.

Query Hint
There are a couple of key pieces of information we need to solve this:

  1. We know there are different IDs for each detective
  2. Only the first detective with the correct solution can claim the bounty
  3. Who has the most bounties?

    KQL commands that will be useful to achieve this are extend, summarize arg_min and join.

Solution – Spoilers below

To solve this, we need to find out the bounty for each case and then join that with the winner of each case.

Query Onboarding

//Who is the winner
let Bounties =
| extend Bounty = toint(Properties.Bounty)
| project CaseId, Bounty;
let Winner =
| where EventType == “CaseSolved”
| summarize arg_min(Timestamp, DetectiveId) by CaseId;
| join kind=inner Winner on CaseId
| join kind=inner Bounties on CaseId
| summarize sum(Bounty) by DetectiveId1
| top 1 by sum_Bounty desc

Bonus answer in 4 lines of code

Turns out the detective with the most bounties is also the detective with the most entries. Which just goes to show you, there are different ways to get the right answers.

| summarize count() by DetectiveId
| where isnotempty(DetectiveId)
| top 1 by count_

All in all I’m glad season 2 is here and I am excited to crack these cases, good luck detectives and welcome aboard!


Kusto Detective Agency Season 2 is here!

Welcome back detectives, to a new exciting season of Kusto Detective Agency, this time around there are 10 cases to solve and some new tools to help you sharpen those KQL skills!

What is it?

The Kusto Detective Agency is a set of challenges that is designed to help you 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. The challenges are gamified and interactive and consist different exciting cases across two seasons.

Each case has a different scenario that you need to solve using KQL queries, where you can earn badges, and they get progressively more difficult as you help the citizens of Digitown.

Season 1 is still available, and I talk about my experience with those challenges here.

Where can I get started?

It’s easy to get started just creating your free ADX cluster and report for duty at the detective agency!

Access the challenges here –
Create your free ADX cluster here –

What’s new?

Hints return from season 1 but the new and exciting feature is a set of training that you can complete to prepare you for each case. This highlights specific commands and techniques that are relevant to solving the various puzzles. Just click “Train me for the case to get started”.

My thoughts

KQL is very valuable considering all of the products that make use of the language and being able to write a basic query does make working with those products much easier. Learning in this gamified way also makes the process more interesting and if the cases from season 2 are anything like season 1 we’re in for a lot of fun. I will be documenting my experience with season 2 and would highly recommend the Kusto detective Agency for anyone who could benefit from KQL skills.


Supercharge your Career Development Plan with a little help from AI!

Career development planning can be challenging, time consuming and even overwhelming. I’ve looked at using popular AI tools such as ChatGPT to see if this process can be easier and let me tell you, it certainly can be.

Here are several AI prompts and instructions that I’ve used to great effect and wanted to share so that others can benefit from them too.


First CareerGPT which now comes in two flavors both of which will help you create a quality career development plan quickly and easily, which you can then use in future planning and career discussions.

The guided experience (v1) – Step through an easy to uses guided approach to creating a development plan with explanations and tips to help you get the best outcome.

The prompts, instructions and original article can be found here –

The advanced experience (v2) – Join your virtual career panel that will ask you questions and make recommendations to help you build your career plan. This is a more “human” experience and is focused on the output rather than the guided step-by-step approach.

Learn more about the advanced experience here
The prompts and instructions can be found here –
Check out my colleague Werner building his career plan, in under 10 minutes, using the guided experience over on Youtube

Role advisor

Sometimes all we need to get started are some ideas for future roles, this conversation can unfortunatly be a little bit like asking a child “What do you want to be when you grow up?” If all we’ve even been exposed to are doctors, teachers and tiktokers then it’s hard to think outside that box.

Enter Role Advisor here to help you find career options suited to your skillset, read more about it here

The prompts and instructions can be found here –


Hopefully this has been useful, it would be awesome if you could take 1 minute to fill out a short survey to let me know how this worked for you, so that I can make this better in the future!

Find the survey here –

Enjoy and happy career planning!