Category Archives: Uncategorized

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!


Monitor better, react faster!

“Perception is the key to reaction; the sharper your perception, the quicker your reaction” – Unknown

This adage holds true in many aspects of life, including cloud monitoring and security. In today’s digital world, cloud infrastructure is the backbone of most organizations, and ensuring the security and availability of these resources is critical. To do this effectively, you need to have a clear view of your cloud infrastructure, and be able to detect and react to threats quickly. This is where perception comes in.

Cloud monitoring is the process of tracking and analyzing the performance, availability, and security of cloud resources. It involves collecting data from various sources and analyzing it to identify trends, anomalies, and potential threats. A key aspect of effective cloud monitoring is having a sharp perception of what’s happening in your cloud environment. This means being able to see and understand the data that’s being generated by your cloud infrastructure, and being able to quickly detect any anomalies or deviations from normal behavior.

One of the biggest challenges with cloud monitoring is the sheer volume of data that’s generated by modern cloud environments. With thousands of resources spread across multiple regions and availability zones, it can be difficult to get a clear view of what’s happening in your cloud environment. This is where cloud monitoring tools such as Azure Monitor and Sentinel come in. These tools are designed to help you collect, analyze, and visualize cloud data in a way that’s easy to understand and act upon.

However, even with the best cloud monitoring tools, perception is still key. You need to be able to interpret the data that’s being generated by these tools and make quick decisions based on that information. This requires not just technical expertise, but also the ability to understand the context and significance of the data that’s being generated.

Cloud security is another area where perception is critical. With cloud environments, security is not just about protecting physical assets; it’s also about protecting data and applications. This means being able to detect and react to threats quickly, before they can cause significant damage. Again, having a sharp perception of what’s happening in your cloud environment is essential for effective security. This also includes tracking and analyzing security events in your cloud infrastructure, such as unauthorized access attempts, data breaches, and malware infections. It requires collecting and analyzing large amounts of security data, and being able to quickly identify and respond to security incidents. This requires not just technical expertise, but also the ability to quickly interpret and understand the significance of security events.

In conclusion, perception is the key to effective cloud monitoring and security. The sharper your perception, the quicker your reaction, and the more effectively you can protect your cloud infrastructure. To achieve this, you need to have the right cloud monitoring tools in place, as well as the expertise to interpret and act on the data that’s being generated. With the right approach, you can ensure the security and availability of your cloud resources and keep your organization safe from cyber threats.


Azure Monitor Basics: Best practices for configuring Azure Monitor alerts

Azure Monitor is a powerful tool that can help you keep track of the performance and health of your Azure resources. One of its most useful features is the ability to set up alerts that notify you when certain conditions are met. However, in order to make the most of this feature, it’s important to follow some best practices when configuring your alerts.

  1. Be specific with your alerts: When setting up alerts, it’s important to be as specific as possible. This means identifying the exact resource or metric that you want to monitor, as well as the specific condition that should trigger the alert. For example, instead of setting up a general alert for “high CPU usage,” set up an alert specifically for “CPU usage on WebApp1 exceeds 80% for 15 minutes.”
  2. Use alert suppression: In some cases, you may not want to receive alerts for certain conditions. For example, you may want to suppress alerts during maintenance periods or when you know that a particular resource is experiencing high load. Azure Monitor allows you to suppress alerts based on specific conditions, such as time of day or the presence of specific keywords in the alert description. For example, you can suppress alerts during non-business hours by setting the suppression time to outside of your business hours.
  3. Use action groups: Azure Monitor alerts can be configured to take a number of different actions when triggered, such as sending an email, creating a ticket in a service management system or even triggering an automation runbook. To make the most of this feature, it’s a good idea to create action groups that group together different actions for different types of alerts. For example, you can create an action group for critical alerts that sends an email to the on-call engineer, creates a ticket in your service management system and triggers an automation runbook to perform a specific action.
  4. Test your alerts: Before you start using your alerts in production, it’s a good idea to test them to make sure that they are configured correctly. You can do this by manually triggering the alert and verifying that the correct actions are taken. For example, you can test your alert by temporarily setting the threshold to a lower value and then verifying that the alert is triggered and the correct action is taken.
  5. Monitor your alerts: Once your alerts are set up, it’s important to keep an eye on them to make sure that they are working as expected. You can do this by monitoring the alert history in the Azure portal, which shows you a record of all alerts that have been triggered and the actions that were taken in response. This will help you to identify any potential issues with your alerts and make any necessary adjustments.

By following these best practices, you can ensure that your Azure Monitor alerts are configured correctly and that they will help you quickly identify and resolve any issues with your Azure resources. By being specific, using alert suppression, action groups, testing the alerts and monitoring them you can make the most out of Azure Monitor alerts and have a more reliable monitoring system.

Note: There are some great example of how to create alerts using JSON templates available here.


Kusto Detective Agency: Challenge 4 – Ready to play?


Just when you thought these challenges couldn’t get any cooler along comes your very own nemesis and a multi-part puzzle taking you on a street tour of New York City.

General advice

First, we need to import the data ourselves this time around, using Ingest from Blob under our data blade, you can also change the column name I used “Primes”
Calculating the prime numbers can be a little tricky as our free ADX cluster requires us to be clever with our query in order to allow it to complete, luckily, we get a free lesson on “special primes”

Challenge 4: Ready to play?

This challenge has two parts and we’ll look at them in turn, first we need to identify a specific prime number and then use that to get the second clue and then we have to find a specific area in New York City,

Query Hint Part 1
Calculating the largest special prime under 100M can be done in a variety of ways, the trick is working within the limited capacity of our free ADX cluster. KQL commands that are useful are serialize, prev, next and join
Ready to play? challenge text - Part 1

Hello. I have been watching you, and I am pretty impressed with your abilities of hacking and cracking little crimes.
Want to play big? Here is a prime puzzle for you. Find what it means and prove yourself worthy.


Start by grabbing Prime Numbers from and educate yourself on Special Prime numbers (, this should get you to{Largest special prime under 100M}

Once you get this done – you will get the next hint.

El Puente.

Query challenge 4 - Part 1

//Method 1 – This query will calculate the largest prime under 100M using the Sieve of Eratosthenes method to test each prime

| serialize
| order by Primes asc
| extend prevA = prev(Primes,1)
| extend NextA = next(prevA,1)
| extend test =  prevA + NextA + 1
| where test % 2 != 0 // skip even numbers
| extend divider = range(3, test/2, 2) // divider candidates
| mv-apply divider to typeof(long) on
  summarize Dividers=countif(test % divider == 0) // count dividers
| where Dividers == 0 // prime numbers don’t have dividers
| where test < 100000000 and test > 99999000
| top 1 by test

//Method 2 – This query will calculate the largest prime under 100M by comparing special primes to the data set of all prime numbers

| serialize
| project specialPrime = prev(Primes) + Primes + 1
| join kind=inner (Challenge4) on $left.specialPrime == $right.Primes
| where specialPrime < 100000000
| top 1 by Primes desc

Now that we have our prime number we can move on to part 2
Largest special prime under 100m

The number we want is 99999517 so we can now head over to

A-ha a message from our nemesis and we need to meet them in a specific area marked by certain types of trees!

Ready to play? challenge text - Part 2

Well done, my friend.
It's time to meet. Let's go for a virtual sTREEt tour...
Across the Big Apple city, there is a special place with Turkish Hazelnut and four Schubert Chokecherries within 66-meters radius area.
Go 'out' and look for me there, near the smallest American Linden tree (within the same area).
Find me and the bottom line: my key message to you.

El Puente.

PS: You know what to do with the following:


.execute database script <|
// The data below is from 
// The size of the tree can be derived using 'tree_dbh' (tree diameter) column.
.create-merge table nyc_trees 
       (tree_id:int, block_id:int, created_at:datetime, tree_dbh:int, stump_diam:int, 
curb_loc:string, status:string, health:string, spc_latin:string, spc_common:string, steward:string,
guards:string, sidewalk:string, user_type:string, problems:string, root_stone:string, root_grate:string,
root_other:string, trunk_wire:string, trnk_light:string, trnk_other:string, brch_light:string, brch_shoe:string,
brch_other:string, address:string, postcode:int, zip_city:string, community_board:int, borocode:int, borough:string,
cncldist:int, st_assem:int, st_senate:int, nta:string, nta_name:string, boro_ct:string, ['state']:string,
latitude:real, longitude:real, x_sp:real, y_sp:real, council_district:int, census_tract:int, ['bin']:int, bbl:long)
with (docstring = "2015 NYC Tree Census")
.ingest async into table nyc_trees ('')
.ingest async into table nyc_trees ('')
.ingest async into table nyc_trees ('')
// Get a virtual tour link with Latitude/Longitude coordinates
.create-or-alter function with (docstring = "Virtual tour starts here", skipvalidation = "true") VirtualTourLink(lat:real, lon:real) { 
	print Link=strcat('', lat, ',', lon, ',4a,75y,32.0h,79.0t/data=!3m7!1e1!3m5!1s-1P!2e0!5s20191101T000000!7i16384!8i8192')
// Decrypt message helper function. Usage: print Message=Decrypt(message, key)
.create-or-alter function with 
  (docstring = "Use this function to decrypt messages")
  Decrypt(_message:string, _key:string) { 
    let S = (_key:string) {let r = array_concat(range(48, 57, 1), range(65, 92, 1), range(97, 122, 1)); 
    toscalar(print l=r, key=to_utf8(hash_sha256(_key)) | mv-expand l to typeof(int), key to typeof(int) | order by key asc | summarize make_string(make_list(l)))};
    let cypher1 = S(tolower(_key)); let cypher2 = S(toupper(_key)); coalesce(base64_decode_tostring(translate(cypher1, cypher2, _message)), "Failure: wrong key")

Using the census data, we now need to figure out the location in the clue, luckily, it’s only a KQL query away

Query Hint - Part 2
Getting the right size area can be tricky, a KQL command that will be extremely helpful will be geo_point_to_h3cell

Query challenge 4 - Part 2

//This query will filter a specific size area until it makes the set of trees given in the clue

let locations =
| extend h3cell = geo_point_to_h3cell(longitude, latitude, 10)
| where spc_common == “‘Schubert’ chokecherry”
| summarize count() by h3cell, spc_common
| where count_ == 4
| summarize mylist = make_list(h3cell);
let final =
| extend h3cell = geo_point_to_h3cell(longitude, latitude, 10)
| where h3cell in (locations)
|where spc_common ==  “Turkish hazelnut” or spc_common == “American linden”
| summarize count() by h3cell, spc_common
| where spc_common == “Turkish hazelnut” and count_ ==1
| project h3cell;
| extend h3cell = geo_point_to_h3cell(longitude, latitude, 10)
| where h3cell in (final)
| where spc_common == “American linden”
| top 1 by tree_dbh asc
| project latitude, longitude
| extend TourLink = strcat(‘’, latitude, ‘,’, longitude, ‘,4a,75y,32.0h,79.0t/data=!3m7!1e1!3m5!1s-1P!2e0!5s20191101T000000!7i16384!8i8192’)

Now that we have a location, we’re not done yet and here’s where the fun really starts, using our generated link will take us on a “Tour of the City” and give us a google maps street view link. Have a look around for our mysterious “El Puente” you may need to walk around a little bit.

Now that we’ve found the message, there’s one more thing we need to do and that’s to use the decrypt function to figure out the message from out detective portal, this part was a little tricky and took a few tries to get the right string to use.

Decryption Key

Using the mural the phrase we are looking for is “ASHES to ASHES”

There we have it, another secret message! Keep a hold of this answer as you’ll need it to complete the final challenge.

Well done Detective, we’ve been on quite the journey. See you in the next challenge!


Kusto Detective Agency: Challenge 3 – Bank robbery!


I must admit that the difficulty spike on the challenges is both refreshing and surprising. The level of care that went into crafting each of these scenarios is outstanding and the ADX team have certainly outdone themselves, if you like these cases as much as I do you can let the team know at

General advice

Again, this case requires some pretty heavy assumptions to solve, some of which the hints will give you clarity on. It’s very easy when trying to solve the bank robbery to end up with a very overcomplicated solution that may take you in the wrong direction, try keep this one simple.

Challenge 3: Bank robbery!

For this challenge you need to track down the hideout of a trio of bank robbers, it seems simple, you have the address of the bank and are provided with all the traffic data for the area now it’s just a case of figuring out where the robbers drove off to.

Query Hint
The trick with this challenge is you need to be able to create a set of vehicles that weren’t moving during the robbery, of course the catch is that only moving vehicles have records in the traffic data. KQL commands that will be useful for this challenge are join, remember that there are different kinds of joins and arg_max

Bonus cool tip

Thanks to my colleague Rogerio Barros for showing me this one because it is awesome! Due to the nature of the traffic data, it is actually possible to plot the route of any number of cars using | render scatterchart. Below is a visual representation of three random cars as they move about Digitown, this is quite interesting once you have identified the three suspects.

Bank robbery challenge text

We have a situation, rookie.
As you may have heard from the news, there was a bank robbery earlier today.
In short: the good old downtown bank located at 157th Ave / 148th Street has been robbed.
The police were too late to arrive and missed the gang, and now they have turned to us to help locating the gang.
No doubt the service we provided to the mayor Mrs. Gaia Budskott in past – helped landing this case on our table now.

Here is a precise order of events:

  • 08:17AM: A gang of three armed men enter a bank located at 157th Ave / 148th Street and start collecting the money from the clerks.
  • 08:31AM: After collecting a decent loot (est. 1,000,000$ in cash), they pack up and get out.
  • 08:40AM: Police arrives at the crime scene, just to find out that it is too late, and the gang is not near the bank. The city is sealed – all vehicles are checked, robbers can’t escape. Witnesses tell about a group of three men splitting into three different cars and driving away.
  • 11:10AM: After 2.5 hours of unsuccessful attempts to look around, the police decide to turn to us, so we can help in finding where the gang is hiding.

Police gave us a data set of cameras recordings of all vehicles and their movements from 08:00AM till 11:00AM. Find it below.

Let’s cut to the chase. It’s up to you to locate gang’s hiding place!
Don’t let us down!

Query challenge 3

//This query will calculate a set of cars not moving during the robbery, which then started moving after it occurred and track vehicles heading to the same address

let Cars =
| where Street == 148 and Ave == 157
| where Timestamp > datetime(2022-10-16T08:31:00Z) and Timestamp < datetime(2022-10-16T08:40:00Z) | join kind=leftanti ( Traffic | where Timestamp >= datetime(2022-10-16T08:17:00Z) and Timestamp <= datetime(2022-10-16T08:31:00Z)
) on VIN
| summarize mylist = make_list(VIN);
| where VIN in (Cars)
| summarize arg_max(Timestamp, *) by VIN
| summarize count(VIN) by Street, Ave
| where count_VIN == 3

Now just wait for the police to swoop in and recovery the stolen cash, another job well done detective!


Kusto Detective Agency: Hints and my experience


So, what is the Kusto Detective Agency?

This set of challenges is an amazing, gamified way to 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. Using your skills, you will help the citizens of Digitown solve mysteries and crimes to make the city a better place!

How do I get started?

The challenges are available here, follow a few basic steps to get started by creating an ADX cluster here and copy the Cluster URI you need this as a part of the onboarding answer.

Now answer a simple question using KQL that being to calculate the sum of the “Score” column

If you are just getting started learning KQL check out Rod Trents ‘Must Learn KQL’ series!

as well as these cool resources

Watch this basic explainer on how the query language works:
Check out the documentation here: Kusto Query Language (KQL) overview- Azure Data Explorer | Microsoft Docs

For help with the first query click the spoiler tag below

Onboarding Query

Onboarding //This is the name of the table we will be running our query against
| summarize sum(Score) //the sum command will add up all the numbers in the “Score” column

General advice

Each challenge has up to three hints that can be accessed through the hints section of your Detective UI, the hints are quite useful, and I would recommend using them if you get stuck especially as some of them include information which is important to confirm assumptions. There are also different ways to get to the answers which shows the power of creative thinking.

Challenge 1: The rarest book is missing!

The first challenge is quite interesting you are tasked with finding a rare missing book. Most people I’ve spoken to have figured out the method but get stuck on the KQL query I’ve included an extra hint below to get you started.

Query Hint
In order to solve this you’ll need to work with the weights of the books on the selves.
KQL commands that will be helpful are sum() and join
The rarest book is missing challenge1 text

This was supposed to be a great day for Digitown’s National Library Museum and all of Digitown.
The museum has just finished scanning more than 325,000 rare books, so that history lovers around the world can experience the ancient culture and knowledge of the Digitown Explorers.
The great book exhibition was about to re-open, when the museum director noticed that he can’t locate the rarest book in the world:
“De Revolutionibus Magnis Data”, published 1613, by Gustav Kustov.
The mayor of the Digitown herself, Mrs. Gaia Budskott – has called on our agency to help find the missing artifact.

Luckily, everything is digital in the Digitown library:

  • – Each book has its parameters recorded: number of pages, weight.
  • – Each book has RFID sticker attached (RFID: radio-transmitter with ID).
  • – Each shelve in the Museum sends data: what RFIDs appear on the shelve and also measures actual total weight of books on the shelve.

Unfortunately, the RFID of the “De Revolutionibus Magnis Data” was found on the museum floor – detached and lonely.
Perhaps, you will be able to locate the book on one of the museum shelves and save the day?

Query challenge 1

//This query will calculate the weight of the books on each shelf and compare that to the weight registered by the sensor, find the shelf with extra weight and we’ll find our book!
| mv-expand rf_ids
| extend RID = tostring(rf_ids)
| join (Books) on $left.RID == $right.rf_id
| summarize sum(weight_gram) by shelf, total_weight
| extend diff = total_weight – sum_weight_gram
| order by diff

I will be talking about the rest of the challenges in a later series so be sure to check back soon, in the meantime good luck Detective!


Azure Monitor Basic Logs

What are Basic Logs?

Relatively new and still in preview Basic Logs are a way to save costs when working with high-volume logs typically associated with debugging, troubleshooting and auditing but they should not be used where analytics and alerts are important.

How do I configure one?

Firstly, it is important to note that tables created with the Data Collector API do not support Basic Logs.
The following are supported:

  • Logs created via Data Collection Rules (DCR)
  • ContainerLogsv2 (Used by Container Insights)
  • Apptraces

All tables by default are set to analytics mode, in order to change this, navigate to Log Analytics Workspaces, select the workspace with the log you want to change. Choose Tables from the navigation rail, select the log and choose Manage Table from the right

Change the table plan to Basic. Note that the default retention changes from 30 days to 8 days. This can of course also be done through the API or CLI

How can I query Basic Logs?

Log queries against Basic Logs are optimized for simple data retrieval using a subset of KQL language, including the following operators:

There are also some other limitations such as time range cannot be specified in the query, and purge is not available, for limitations refer to the official documentation

How much cheaper is it?

Basic Logs $0.615 per GB of data ingested
Standard Pay-as-you-go price $2.76 per GB (5GB free per month) with discounts for purchasing a commitment tier of up to 5000GB per day.

During preview there is no charge for querying basic logs however there will be a small charge once it reaches GA based on the amount of data the query scans, not just the amount of data the query returns. At this time the expected cost is $0.007 per GB of data scanned.

Basic Logs Architecture Reference


SCOM vs. Azure Monitor

Recently I was invited to speak at Silect MP University about SCOM vs Azure Monitor, the session is available for viewing below.

Join me as I discuss the pros and cons of both tool as well as how to leverage them both individually and together for a variety of scenarios.


Azure Monitor Agent (AMA) goes preview

The new Azure Monitor Agent, is available for preview in the Azure Portal, lets have a look at how to configure data collection for this new experience.

If the Azure Monitor blade there are a couple of changes, we’re interested in the new option called Data Collection Rules this is how we’ll tell out new agent what data to collect.

Clicking new we can see there’s a few tabs to configure, under Basics, we need to name our rule, choose a subscription and resource group.

Next we need to choose the Virtual Machines we can to add to the rule, this will also deploy the agent to the VM if necessary. Note that due to the agent being in preview that it is not available in all regions currently.

Below the selected machines are all set and ready to go.

Finally we need to configure what this rule is collecting, you can choose logs or metrics and you can be more granular then before when it comes to log collection with a custom filter.

You can also have log and metrics collections in the same rule.

Once everything is configured simply click create, the agent will be deployed if necessary and the collection will start.

Easy as pie, enjoy the new monitoring experience!