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 Monitor, Sentinel, M365 Defender and Azure Data Explorer (ADX) to name a few.
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.
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!
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.
- We know there are different IDs for each detective
- Only the first detective with the correct solution can claim the bounty
- 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.
//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
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!