Kusto Detective Agency Season 2: Case 4 – Triple trouble!


Click for challenges

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

Uh oh, the mayor is in trouble, and it seems like there’s a mastermind behind all of the nefarious ongoings. These cases are getting more interesting, and the difficulty is starting to ramp up significantly.

General advice

The training helps significantly with this case, there is only one thing that I would change, which I will mention in the query hint section. I also feel like a cybersecurity background would make this challenge significantly easier but that’s just my personal opinion.

Challenge: Case 4

Case 4 challenge text

Dear Kusto Detective Agent,

It’s me, Gaia Budskott, the mayor of Digitown… I am writing to you because I am in desperate need of your help.

Recently, I have been caught up in not one, not two, but three different police investigations. The police suspect that I am behind a series of crimes that I have nothing to do with. My personal electricity and billing account was apparently undercharged for the past few months, and phishing calls were made from numbers associated with my office. To top it off, secret documents that belong to me were found in a garage where stolen cars were placed.

I am at my wit’s end and I don’t know who to turn to. I can’t ask the police for help because they think I’m the one behind all of this. That’s why I’m turning to you – perhaps you can help me figure out who is really behind all of this.

I suspect that someone has hacked into the Digitown municipality system and stolen these documents. Our system is a known data hub and hosts various information about the town itself, real-time monitoring systems of the city, tax payments, etc. It serves as a real-time data provider to many organizations around the world, so it receives a lot of traffic.

Unfortunately, I don’t have much data to give you. All I have is a 30-day traffic statistics report captured by the Digitown municipality system network routers.

I am hoping that your expertise and knowledge in big data analytics can help shed some light on who is behind these crimes and clear my name.

Please, can you help me?


There is a lot to unpack to crack this case and several interesting learnings about the capability of KQL along the way.

Query Hint

Understanding what this case wants you to do can be a bit tricky, I found the training to be very useful this time around except for the focus on series_decompose_forecast, none of the solutions I’ve seen make use of this command, so I feel in this instance it is a little misleading. Instead check out these KQL commands make-series and series_decompose_anomalies.

Solution – Spoilers below

There are several different ways to get to the result in this case but they all lead to the same conclusion.

Query Case 4

//One of the great things about these challenges is the community collaboration that comes from them. Big thanks to the master Matt Zorich for giving me a hand with this one as my boxing match with the series_decompose_anomalies syntax did not end in my favour.

let cidr=
let potentials=
| make-series Clients=sum(BytesSent) on Timestamp step 12h by ClientIP
| extend outlier=series_decompose_anomalies(Clients)
| mv-expand outlier
| where outlier == 1
| distinct ClientIP
| evaluate ipv4_lookup(cidr, ClientIP, IpCidr)
| summarize count()by Info
| top 10 by count_
| distinct Info;
| evaluate ipv4_lookup(cidr, ClientIP, IpCidr)
| where Info in (potentials)
| make-series Bytes=sum(BytesSent) default=0 on Timestamp step 12h by Info
| render timechart

//I’d also suggest checking out Lizel Hughs solution for a very elegant approach and bonus use of render anomalychart Season 2 Case 4: Triple trouble! | Liesel’s Tech Ramblings Blog (lieselhughes.com)

I wonder how long before we discover who is behind this sudden rise in crime, stay vigilant detectives!


8 thoughts on “Kusto Detective Agency Season 2: Case 4 – Triple trouble!

  1. Tomas Hedin

    I manged to solve this by using none of the fancy stuff.

    First i checked the first visisted ip by timestamp, and i found a suspect
    | summarize arg_min(Timestamp,*) by ClientIP
    Verified it with
    | summarize arg_max(Timestamp,*) by ClientIP

    Then to lookup the suspect
    | where ClientIP == ‘’
    | evaluate ipv4_lookup(IpInfo,ClientIP,IpCidr)

  2. Pingback: Kusto Detective Agency Season 2: Case 5 - Blast into the past - OpsMan

  3. Pingback: Kusto Detective Agency Season 2: Case 3 - Return Stolen cars! - OpsMan

  4. Pingback: Kusto Detective Agency Season 2 – Onboarding - OpsMan

  5. Pingback: Kusto Detective Agency Season 2: Case 2 - Catch the Phishermen! - OpsMan

  6. Pingback: Kusto Detective Agency Season 2: Case 8 - Catchy Run - OpsMan

  7. Pingback: Kusto Detective Agency Season 2: Case 6 - Hack this rack! - OpsMan

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.