Tag Archives: kusto detective agency

Kusto Detective Agency Season 2: Case 5 – Blast into the past

Challenges

Click for challenges

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

Prof. Smoke is back and so is the mysterious El Puente but an interview on a new secret KQL technology has gone missing! Luckily we have all the tools to track down the missing file.

General advice

I must say the clues this time around felt very fluffy, it seems that they are intended to be vague, and the meat of the tips will be presented in the “train me” content. I feel like this is a step in the wrong direction as it’s sometimes helpful to have a better logic clue then just training.

Challenge: Case 5

Case 5 challenge text

Hey there!

It’s been a while since we talked, my friend. I’ve been keeping an eye on your remarkable detective work, and you never cease to amaze me. Well done! Just a friendly word of caution, though—I have a feeling you’re getting closer to the edge. Are you ready to take the leap?

Anyway, I’ve got an offer that’ll pique your interest. I’ve stumbled upon some seriously valuable intel about Kuanda org, and I know it could be a game-changer for the case you’re currently tackling. But before I speak, I need your assistance with a little something.

You know about Scott Hanselman’s incredible video podcasts, right? The guy’s a legend! He usually drops them every week, and they rack up thousands of views. But here’s the twist: Something mysterious went down with his 900th episode, slated for release a few weeks back. Rumor has it that Prof. Smoke, the renowned Big Data expert, spilled the beans on some top-secret functionality during the interview. The thing is, he had a change of heart and insisted the video be scrapped, so it never saw the light of day.

Now, hold on to your detective hat, because this is where it gets intriguing. I have a hunch that the interview was actually published before its deletion. Everyone assumed it was gone for good, but I managed to sneak my way into the public archive logs. These logs have all the juicy details—creation and replication timestamps, access records, and even deletion operations. My hope is that the deletions weren’t fully synced into the archive, leaving behind some remnants of that epic interview.

So, here’s the deal, my friend. If you can crack this case and unearth the elusive interview link, I promise to tell everything about Kuanda.org. Believe me, the information I have is pure gold.

Are you ready to dive headfirst into this thrilling challenge? Find that elusive video URL, and I’ll do everything in my power to dig up every last detail about Kuanda.org.

Let me know if you’re in, and let the game begin, detective!

Cheers,
El Puente

This case is awesome in that there are several different routes to arrive at the answer depending on your logical approach.

Query Hint

Depending on how you go about solving this puzzle you can make use of several different KQL commands ones I found useful were parse, let and split and found it very helpful to tackle this challenge in stages.

Solution – Spoilers below

Chatting to the community on this one has been awesome, I’ve seen 4 completely unique approaches to solving this puzzle and I’m going to talk about mine and one other below.

Query Case 5

//I tackled this challenge in a set of logical steps, looking for blobs that had no views, which had been deleted but not completely deleted. Using this in combination with hosts that had a dip in usage got to several suspect files one of which was the missing interview. Not elegant by any means but it got the job done.

let hosts=
   StorageArchiveLogs
| parse EventText with TransactionType ” blob transaction: ‘” BlobURI “‘” *
| parse EventText with * “(” Reads:long “reads)” *
| extend Host = tostring(parse_url(BlobURI).Host)
| summarize Deletes=countif(TransactionType  == ‘Delete’),
        Creates =countif(TransactionType == ‘Create’),
        Reads=sumif(Reads, TransactionType == ‘Read’) by Host
| where Deletes > 0
| where Creates > 0
| where Creates > Deletes
| order by Reads
| take 50;
let Scott =
StorageArchiveLogs
| parse EventText with TransactionType ” blob transaction: ‘” BlobURI “‘” *
| parse EventText with * “(” Reads:long “reads)” *
| extend Host = tostring(parse_url(BlobURI).Host)
| where Host in~ (hosts)
| make-series Count=sumif(Reads, TransactionType == ‘Read’) default=0 on Timestamp step 12h by Host
| project series_periods_validate(Count,14,10), Host, Count
| extend Score = tostring(series_periods_validate_Count_scores[0])
| where Score != “0.0”
| project Host;
let deletes=
StorageArchiveLogs
| parse EventText with TransactionType ” blob transaction:” *
| parse EventText with * “blob transaction: ‘” BlobURI “‘” *
| where EventText has “completely”
| distinct BlobURI;
let uri=
StorageArchiveLogs
| parse EventText with TransactionType ” blob transaction:” *
| parse EventText with “‘ read access (” ReadCount:long ” reads) were detected on the origin”
| parse EventText with * “blob transaction: ‘” BlobURI “‘” *
| extend Host = tostring(parse_url(BlobURI).Host)
| where Host in (Scott)
| project EventText, BlobURI, TransactionType, ReadCount, Host
| summarize Deletes=countif(TransactionType  == ‘Delete’),
        Creates =countif(TransactionType == ‘Create’),
        Reads=sumif(ReadCount, TransactionType == ‘Read’) by Host, BlobURI
| sort by Reads asc
| where Deletes > 0
| where Creates > 0
| distinct BlobURI;
StorageArchiveLogs
| parse EventText with TransactionType ” blob transaction:” *
| parse EventText with * “blob transaction: ‘” BlobURI “‘” *
| where BlobURI in~ (uri) and BlobURI !in (deletes)
| where EventText has_all (“backup”,”create”)
| distinct EventText
| extend SplitAll=split(EventText, ‘ ‘)
| extend Backup=tostring(SplitAll[8])
| project-away  EventText, SplitAll
| where Backup contains “mp4”

//A far neater approach by my colleague Nabeel Prior

// get the blob accounts that had 1 new creation each week for 4 weeks
let weeklyCreates =
StorageArchiveLogs
| where EventText startswith “Create blob”
| project
    createTS=Timestamp,
    blobAccount=substring(EventText, indexof(EventText, “https://”), indexof(EventText, “/”, 1, 1000, 3) – indexof(EventText, “https://”))
| summarize accountCreatesPerWeek = count() by blobAccount, week_of_year(createTS)
| where accountCreatesPerWeek == 1
| summarize count() by blobAccount
| where count_ >= 4;
// get all the creates for the above blob accounts
let allCreates =
StorageArchiveLogs
| where EventText startswith “Create blob”
| project
    blobAccount=substring(EventText, indexof(EventText, “https://”), indexof(EventText, “/”, 1, 1000, 3) – indexof(EventText, “https://”)),
    createUrl=substring(EventText, indexof(EventText, “https://”), indexof(EventText, “‘ backup is created on “) – indexof(EventText, “https://”)),
    backupUrl=tostring(split(replace_string(EventText, “Create blob transaction: ‘”, “”), “‘ backup is created on “, 1))
| join kind=inner weeklyCreates on $left.blobAccount == $right.blobAccount;
// for all creates above, find those that were partially deleted
StorageArchiveLogs
| where EventText startswith “Delete blob”
| extend deleteUrl=substring(EventText, indexof(EventText, “https://”), indexof(EventText, “‘ backup is”) – indexof(EventText, “https://”))
| extend deleteAction=iif(EventText contains “completely”, 1, 0)
| join kind=inner allCreates on $left.deleteUrl == $right.createUrl
| summarize completelyDeleted=sum(deleteAction), firstDelete=min(Timestamp), lastDelete=max(Timestamp), numberOfDeletes=countif(deleteAction == 0) by deleteUrl, backupUrl
| where completelyDeleted==0;

This weeks puzzle was great fun, and I really enjoyed the discussions about the various approaches to solving it. Well done detectives and kep an eye out for El Puente because we don’t know where they may strike next!

Loading

Kusto Detective Agency Season 2: Case 3 – Return Stolen cars!

Challenges

Click for challenges

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

There sure is a lot of strange things happening in Digitown at the moment. This time cars are being stolen and it’s up to us to try and catch the thieves! This was an enjoyable case and requires a great spread of KQL and puzzle solving to catch those crooks.

General advice

If you have completed season 1 this case may seem familiar to you, except this time, there’s a twist. The clues are quite good this time around and the training has improved. Tackling this one in stages can make it quite a bit easier to crack this case.

Challenge: Case 3

Case 3 challenge text

Hey there Detective,

We’ve got an urgent case that needs your expertise! There has been a sudden increase in unsolved cases of stolen cars all across our city, and the police need our help again to crack the case.

We’ve been given access to a massive dataset of car traffic for over a week, as well as a set of cars that have been stolen. It’s possible that the car’s identification plates were replaced during the robbery, which makes this case even more challenging.

We need you to put on your detective hat and analyze the data to find any patterns or clues that could lead us to the location of these stolen cars. It is very likely that all the stolen cars are being stored in the same location.

Time is of the essence, and we need to find these cars before they are sold or taken out of the city. The police are counting on us to solve this case, and we can’t let them down!

Are you up for the challenge, detective? We know you are! Let’s get to work and crack this case wide open!

Best regards,
Captain Samuel Impson.

Time to get to work and track those car thieves

Query Hint

This case is setup to use more logic than assumptions. Think about how you would find out where the cars are being taken to have their VIN numbers changed. Check out these KQL commands for some help arg_max, join and make_list.

Solution – Spoilers below

This solve can be done more optimally but I did it in two steps

Query Case 3

//First, we need to know where the VIN numbers are being changed, luckily, we can track all of the stolen cars relatively easily at first and we’ll find two locations the cars are being taken to.

CarsTraffic
| join kind = inner (StolenCars)
  on VIN
| summarize arg_max(Timestamp, *) by VIN
| order by Ave
| summarize count(VIN) by Street, Ave

//Now comes the tricky part we need to find cars leaving these locations with unknown VIN numbers and figure out where the stolen cars are being taken, what we do know is now many stolen cars we are looking for. Well look at that a suspicious location!

let Suspects =
CarsTraffic
| summarize arg_min(Timestamp, *) by VIN
| where (Street == 86 and Ave == 223) or (Street == 251 and Ave == 122)
| summarize mylist = make_list(VIN);
CarsTraffic
| where VIN in (Suspects)
| summarize arg_max(Timestamp, *) by VIN
| summarize Vins = count(VIN) by Ave, Street
| where Vins == 20

It was only a matter of time before these thieves were brought to justice. These cases are getting more and more exciting, I wonder where the next one will take us. As always, great work detectives!

Loading

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

Challenges

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.

Sincerely,
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.

Consumption
| 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.

Loading

Kusto Detective Agency Season 2 – Onboarding

Challenges

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 =
DetectiveCases
| extend Bounty = toint(Properties.Bounty)
| project CaseId, Bounty;
let Winner =
DetectiveCases
| where EventType == “CaseSolved”
| summarize arg_min(Timestamp, DetectiveId) by CaseId;
DetectiveCases
| 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.

DetectiveCases
| 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!

Loading

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 – https://detective.kusto.io/
Create your free ADX cluster here – https://aka.ms/kustofree

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.

Loading