Author Archives: Warren Kahn

SCOM 2022: Update Rollup 2 is now available!

A decent update with a few good fixes for issues that will improve the quality of life for SCOM users. Of course a really nice new feature is the ability to do an Azure migrate assessment via the SCOM console. I for one think that’s fantastic!

Get it here

What’s new?

  • Discover Azure Migrate, generate Business Case & Assessment through Operations Console.

What’s fixed?

  • Fixed an issue which was causing hang in SCXOM modules because of infinite loop due to incorrect usage of GetExitCodeThread in the Thread::Thread class
  • Updated documentation mentioning that domain accounts are not supported for maintenance, which was earlier a confusion and was causing SSH error for customers.
  • Added DISTINCT Keyword to Maintenance Mode Query in StateChangeEventList which improved query time.
  • Fixed an issue which was blocking virtualization of Candidates Report for several languages.
  • Fixed the issue of RHEL 9.1 not being added to SCOM.
  • Fixed an issue which was causing NullReferenceException when criteria Is Empty.

Security Enhancements

  • Fixed several Web Console Security Vulnerabilities.

Unix/Linux/Network monitoring fixes and changes

  • Fixed an issue where msgAuthenticationParameters needs to have 0 length during engine discovery of SNMPv3 devices. Also
  • Fixed an issue related to SNMP Discovery where we see MonitoringHost.exe crashes.
  • Fixed the issue where user was unable to run Get-SCXAgent and Invoke-SCXDiscovery remotely using Invoke-Command.
  • Added supportability on Operations Manager Linux Agent for New distros Like Rocky Linux 8, Alma Linux 8.
  • Added Supportability on Operations Manager Linux Agent for Openssl3.0 Distros Like Ubuntu22, RHEL9, Rocky9, Alma9 and Oracle 9.
  • Fixed an issue leading to Linux agent crashes when the DSC provider is installed.
  • Fixed Linux Agent Crash issues due to XML Parsing issues.
  • Addressed various Codeql issues.
  • Enhanced Linux Agent with important compiler mitigation
  • Fixed a Linux Agent installation issue on Solaris 10X86 due to older openssl versions.
  • Fixed a Linux Agent installation issue caused by Disabled SELINUX in the customer environment.
  • Fixed an issue with Linux Agent Causing 100% CPU Usage
  • Fixed an issue with Script Timeout not working when Elevation type is Sudo
  • Increased OMI Query Filter size to 256 to support more complex query
  • Incorporated important Cipher Suite as Defaults.

Loading

Kusto Detective Agency Season 2: Case 8 – Catchy Run

Challenges

Click for challenges

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

While I did find this challenge to be quite fun there was a minor issue with the answer submission which prevented me from solving the case until much later in the day. Initially you had to get the right location to within 25cm which is a little difficult while running around Barcelona.

General advice

The clues for this case are decent, even with them it can be a little tricky to get started and the “Train me” for this case is very specific as we learned the geo skills in the previous challenge.

Challenge: Case 8

Case 8 challenge text

Hi again, Detective,

First things first, let’s give you a round of applause for your jaw-dropping detective skills on the previous case! You totally nailed it when you pointed to Barcelona as Krypto’s secret hideout. We were blown away by your incredible intuition and deductive prowess. Seriously, you’re like a real-life Sherlock Holmes, but even cooler!

Now, let’s dive into the current situation. We were thiiis close to catching Krypto, but he slipped through our fingers in the bustling city of Barcelona. Our intelligence sources provided a lead indicating that Krypto is a die-hard runner, hitting the pavement 3-4 times a week and running 8-12 kms each time. We managed to obtain fitness data from the past two weeks of all runners in Barcelona, thanks to our National Security Office’s (NSO) extensive reach. However, despite our best efforts, we couldn’t pinpoint where he starts his runs. It’s up to you, detective, to crack this case wide open!

But wait, there’s more. We intercepted a message sent by Krypto to his Kuanda associates. As expected, he encrypted the message using a sophisticated Krypto-code, rendering it unreadable without the key. It appears that the Kuanda possess a tribal knowledge of how to obtain these keys based on specific cities. While our agents have managed to decipher half of the required 16 numbers, we’re still missing the full Barcelona city code.
Good luck, detective, and may your wits guide you through this challenge!

Cheers,
NSO Agent Stas Fistuko

Right lets get tracking

Secret Message Hint

To get started first we need to decode the message, we know in order to do this we need the missing numbers from the grid, and we suspect it may have something to do with the Sagrada Familia, particularly the Nativity and Passion facades, keep those eyes open.

Spoilers below

Secret Message Spoiler

Over the entrance of the Passion facade there is a very well-known magic square, which looks to match our number grid lets decode that message.

The decoded message reads as follows:

Listen up, esteemed members of Kuanda, for we have encountered a slight hiccup in our grand scheme.
I can sense your concern, as rumors of our true intentions have reached the ears of the KDA.
But fear not, my loyal comrades, for we shall not waver from our path! If anything, we shall intensify our efforts until the KDA crumbles beneath our feet.
I cannot share too much at this time, but rest assured, we are running our “smoke tests”, both figuratively and quite literally.
They shall expose the KDA’s weaknesses and herald its epic downfall.

Now, let us address the matter of my well-being. I understand that there is a great deal of curiosity regarding my safety.
Let me assure you, it was all a matter of impeccable timing. No doubt my connecting flight was an experience of a lifetime!
Too bad my luggage failed to join me on this thrilling journey! 🙂

But fear not, my friends, leaving things to chance is not my style. I have assembled a team of loyal bodyguards,
who move with me like elusive phantoms, ensuring my invincibility. At any given time, at least two of them discreetly
shadow my every move, even during my exhilarating runs through the city. Truly, I feel untouchable. And let me tell you,
this city is a hidden gem! It offers an abundance of marvelous spots where one can indulge in a refreshing shake after
conquering a breathtaking 10K run. It is a perfect blend of mischief and rejuvenation, a delightful concoction that fuels my strength.

So, my fellow rogues, let us keep our eyes fixed on the target. I will reveal more details about our plans in due time.
Prepare yourselves to witness the spectacular downfall of the KDA, as we relentlessly drill into its core at full speed!

Krypto

Now on to finding Krypto!

Query Hint

We have the runner’s data and some information about Kryptos habits. Lets try use the geo_point_to_s2cell and between commands to track him and his bodyguards.

Solution – Spoilers below

We know where each runner starts their run and we know Krypto likes to get a shake at the end and runs tailed by at least two bodyguards. We also know it’s a 10km run so let’s see what we can do.

Query Case 8

//We’re going to determine the runners that are running a 10km, two to three times a week and then see which ones are running with a group of at least 3 and see where they start.

let potentials=
Runs
| where Distance between (8 .. 12)
| summarize runs=make_set(Timestamp) by RunnerID, startofweek(Timestamp)
| extend numberofruns=array_length(runs)
| where numberofruns in (3,4)
| distinct RunnerID;
let bodyguards=
Runs
| where RunnerID in~ (potentials)
| summarize make_set(RunnerID) by geo_point_to_s2cell(StartLon, StartLat,22), startofday(Timestamp)
| where array_length(set_RunnerID) >= 3
| extend set_RunnerID=tostring(set_RunnerID)
| summarize count() by set_RunnerID
| where count_ > 2
| mv-expand todynamic(set_RunnerID)
| extend RunnerId=tostring(set_RunnerID)
| distinct RunnerId;
Runs
| where RunnerID in (bodyguards)
| where Distance between (8 .. 12)
| summarize make_set(RunnerID) by Cell=geo_point_to_s2cell(StartLon, StartLat,22)
| join kind=inner(Runs
| where RunnerID in (bodyguards)
| where Distance between (8 .. 12)
| extend Cell=geo_point_to_s2cell(StartLon, StartLat,22)
) on Cell
| distinct StartLat, StartLon
//| distinct round(StartLat,5), round(StartLon,5) //round for answer

//This gives us some locations which when entered into our VirtualTourLink reveal a pretty cool place to grab a post-run shake!

VirtualTourLink(41.384673980870026, 2.1833562706513296)




Well look at that I think we’ve got him, great job detectives!

This challenge was far less frustrating than the previous one, the clues were interesting, and it was great to get a look around Barcelona as well. Overall not my favorite case but definitely fun.


Loading

Kusto Detective Agency Season 2: Case 7 – Mission ‘Connect’

Challenges

Click for challenges

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

I really enjoyed this week’s challenge, it has a fun story element and works well with some of the more interesting KQL features. I definitely get to keep my Kusto card after this one!

General advice

This time arounds the clues are not only crucial but also pretty cool, this case also has some similarities to case 4 from season 1 but if you aren’t super familiar with this KQL capability the “train me” does a good job of laying the groundwork.

Challenge: Case 7

Case 7 challenge text

Hi Detective,

It’s been awesome witnessing your progress. Seriously, you’ve climbed to new heights in uncovering the misdeeds of sly cyber-criminal, Krypto. We, the National Security Office (NSO), had our eyes on him for ages, and thanks to your information, we finally managed to track him down. I’ll spare you the thrilling details, but guess what? Turns out our guy held a high-ranking position as City Manager in the Mayor’s office, and he was tight with Ms. Gaia Budskott, the Mayor of Digitown. And yes, he’s also the mastermind behind the infamous KUsto ANti-Detective Agency (Kuanda.org) that you brilliantly exposed. However, here’s the unfortunate part: he slipped through the fingers of Digitown’s law enforcement. Given the new international nature of the case, we (the NSO) are taking over.

So, let’s cut to the chase. Time is of the essence, and we need your expertise and experience to help us find the final destination of Krypto.
While we have gathered significant information about him, it is not enough to capture him. Our sources indicate that he was spotted at the Doha airport on August 11, 2023, between 03:30 AM and 05:30 AM (UTC). However, by the time our agents arrived, he had already made his escape, presumably utilizing a private jet. We have deployed dozens of officers to all potential landing destinations, but he has evaded us so far. We have a single lead that suggests Krypto may have attempted a plane-to-plane jump, given his skills as a wingsuit expert. Here is where we got stuck.

Fortunately, we have you (and full access to the public and private jet plane schedules on this day). Your mission, should you choose to accept it, is to determine the destination to which Krypto has fled.

Hoping to hear back from you soon,
NSO Agent Stas Fistuko

Alright detectives lets find that fugitive!

Query Hint

This is a geo challenge and you’re going to need to check out the geo_point_to_h3cell and geo_point_to_s2cell commands for their awesome capabilities.

Solution – Spoilers below

Well, we know the point and time of departure and we also know some pretty fancy flying would have to take place to allow Krypto to jump between planes Mission Impossible style.

Query Case 7

//First we need to find the first airport, you can use the code but the municipality works just as well

Airports
| where municipality == “Doha”
| project lat, lon


//Then we need to find flights leaving from Doha, between 03:30 AM and 05:30 AM, that have a close encounter with another plane where our suspect flight is the one flying above the other plane.

let doha=
Flights
| where Timestamp between (datetime(2023-08-11T03:30:00Z) .. datetime(2023-08-11T05:30:00Z))
| where onground==true
| summarize callsign=make_set(callsign) by geo_point_to_s2cell(51.608056,25.273056,12)
| mv-expand callsign
| extend callsign=tostring(callsign)
| distinct callsign;
let potentialplanes=
Flights
| where Timestamp between (datetime(2023-08-11T05:30:00Z) .. now() )
| where callsign in (doha)
| summarize Planes=make_set(callsign), Heights=make_set(geoaltitude) by geo_point_to_s2cell(lat,lon,15), bin(Timestamp, 1m)
| extend CountofPlanes=array_length(Planes),HeightCount=array_length(Heights)
| where CountofPlanes == 2
| where HeightCount == 2
| extend H1=toint([‘Heights’][0]), H2=toint([‘Heights’][1])
| extend HeightDelta=H1-H2
| where HeightDelta >=0 and HeightDelta<=10
| mv-expand Planes
| extend callsign=tostring(Planes)
| distinct callsign;
Flights
| summarize arg_max(Timestamp, *) by callsign
| where onground== true
| where callsign in~ (potentialplanes)
| extend key=geo_point_to_s2cell(lon, lat, 13)
| join kind=inner(
Airports
| extend key=geo_point_to_s2cell(lon, lat, 13)
) on key
| distinct municipality

//Bingo one suspect and it’s flying to Barcelona!




Bonus observation: I noticed the name of our new friend NSO Agent Stas Fistuko looked a little strange, turned out it’s an anagram for Kusto is fast and I couldn’t agree more!

Great job detectives, with 3 cases to go we’re hot on the trail of Krypto and Kuanda, hopefully we can catch them before they do any real damage!

Note: There are some fun things you can do with mapping the flights onto a map in order to see the two places meet up and where they go, there’s a couple of ways to do this too and it’s a fun one to try and figure out.


Loading

Kusto Detective Agency Season 2: Case 6 – Hack this rack!

Challenges

Click for challenges

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

I must say I enjoy a good puzzle as much as the next Kusto Detective, but this week was a real head scratcher.

General advice

For this challenge I found the clues to be of almost no help at all, the “train me” though is critical as is having a lot of patience for the final riddle.

Challenge: Case 6

Case 6 challenge text

Hey there! I’ve got some juicy details for you regarding the elusive https://kuanda.org

So, the bad news is that despite my best efforts, I still don’t have a ton of info on these guys. But, the good news is that I did stumble upon a lead that might just crack this case wide open! You ready for this? Kuanda.org isn’t just some run-of-the-mill fishing organization. Nope, my sources tell me it’s a brand spanking new cyber organization that’s all about digital data repositories. Talk about cutting-edge technology, am I right?

But wait, it gets even better. They’ve been recruiting cyber-crime specialists like there’s no tomorrow. Which means that this organization is serious about their work, and they have something big planned. And here’s the kicker – every new member has to spend a week at the National Gallery of Art! Yeah, you heard that right. The same National Gallery of Art that houses all those fancy paintings and sculptures. What could they possibly be doing in there for a whole week? Studying Leonardo da Vinci’s brushstrokes? I smell something fishy, and it’s not just the art restoration chemicals.

And to top it all off, my sources managed to snag some instructions for the new recruiters. If you can decode them, you might just have a shot at infiltrating their system and finding out what they’re really up to. Who knows, you might even find the smoking gun that proves they’re behind all those cyber-crimes. Good luck, detective – I sense you will need one!

Cheers,
El Puente

Decrypting the message is the first stop and there are a couple of different ways to do this, I took a more manual approach due to early morning lack of coffee.

Query Hint

You can take a basic start with the extract_all command or you can tackle the entire message at once with replace_string. Either way works and you’ll end up with an interesting yet cryptic message

Solution – Spoilers below

The community keeps growing around these challenges, I was part of many debates as tot he meaning of certain phrases in the secret message so lets have a look.

Query to decrypted the message

//This is the manual way by substitution each pair of numbers into the ObjectId and Index section of the query

NationalGalleryArt
| where ObjectId == “46081”
| extend Words=extract_all(@'(\w+)’, ProvenanceText)
| mv-expand with_itemindex=Index Words
| project Index, Words
| where Index == 105

//Here is the much smarter way of doing this which I must credit to Aviv Yaniv who operates much better on far less coffee than I do.

let RecruitInstructions =
“`12204/497 62295/24 50883/678 47108/107 193867/3,
45534/141 hidden 100922/183 143461/1 1181/505 46187/380.
41526/155 66447/199 30241/114, 33745/154 12145/387 46437/398 177191/131:
293/64 41629/1506 210038/432, 41612/803 216839/1.
404/258 rules 40/186 1472/222 122894/2 46081/105:
41594/650 32579/439 44625/141 184121/19 33254/348 357/273 32589/821,
46171/687 punctuations 62420/10 50509/48 1447/128,
176565/82’56721/591 561/225 insensitive, 30744/129 76197/32.
1319/42 41599/216 68/457 136016/146, 42420/126’46198/389 42429/158 40091/108 41667/252,
1515/555 177593/223 176924/73 45889/65 159836/96 35080/384 32578/199.
1607/167 124996/9 71/56, 1303/187 45640/1114 72328/247 75802/11,
1168/146 163380/12 57541/116 206122/738 365/267 46026/211 46127/19.
119295/425 45062/128 12198/133 163917/238 45092/8 54183/4 42453/82:
561/433 9/387 37004/287 1493/118 41676/38 163917/238 3159/118 63264/687
1/905 1493/109 43723/252, 136355/1 1159/134 40062/172 32588/604,
158574/1 45411/8 10/892 127587/175 – 633/9 72328/247 1514/615 42940/138.
164958/84 221014/479 151526/7 111124/138, 41668/206 34109/46 1514/555,
147789/2 3228/152 993/323 166477/167 178042/167, 50753/91’207786/8 12/372.
1108/158’42423/150 12/309 66154/9 213566/11 44981/158 1197/300
40184/149 92994/63-71071/179 75093/7 211718/18 74211/5 46144/399.“`;
let ProvenanceTextWords = materialize(
NationalGalleryArt
| extend Tokens = extract_all(@'(\w+)’, ProvenanceText));
let CleanedRecruitInstructions = replace_string(
                                 replace_string(
                                 replace_string(
                                 replace_string(
                                 replace_string(
                                 replace_string(RecruitInstructions,
                                “-“, ” – “),
                                “‘”, ” ‘ “),
                                “,”, ” , “),
                                “.”, “”),
                                “\r\n”, ” \n “),
                                “:”, ” : “);
let CleanedRecruitInstructionsTokens = split(CleanedRecruitInstructions, ” “);
let TokensCount = array_length(CleanedRecruitInstructionsTokens);
let DecipheredRaw = tostring(toscalar(
range token_index from 0 to TokensCount step 1
| extend original_token = CleanedRecruitInstructionsTokens[token_index]
| extend cipher_indexes = split(original_token, “/”)
| extend should_translate = array_length(cipher_indexes) == 2
| extend ObjectId = tolong(cipher_indexes[0])
| extend WordIndex = tolong(replace_string(tostring(cipher_indexes[1]), “,”, “”))
| join kind=leftouter ProvenanceTextWords on ObjectId
| extend Word = iff(should_translate, Tokens[WordIndex], original_token)
| sort by token_index asc
|  summarize array_strcat(make_list(Word), ” “)));
let Deciphered = replace_string(replace_string(replace_string(replace_string(DecipheredRaw, ” : “, “:”), ” ‘ “, “‘”), ” , “, “, “), ” – “, “-“);
print(Deciphered)



Decrypted message

in catalogue of titles Grand,
three hidden words Demand your Hand
when found all, they form A line:
A clear timeline, simply Fine

words rules are simple to Review:
at least three Letters have in view,
all punctuations Mark the End,
they’re case insensitive, my friend

to find all words, you’ll need some skill,
seeking the popular will guide you still
below The King, the first word mounts,
the Second shares with Third their counts

reveal the last word with Wise thought:
take first two letters from word most sought
into marked dozen, and change just one,
and with those two – the word is done

so search the titles, high and low,
and when you find it, you’ll know
you’ve picked the Image that revealed
the pass-code to the World concealed

Now a word of warning, how you proceed here could lead you down many hours of dead ends because depending on how you sort the words you might not get the right list to work off. Let’s break this down, section by section.

1. We’re looking for three words that form a timeline
2. The words are case insensitive, at least 3 letters long and end in punctuation
3. The first word is below King
4. The second word shares it’s count with the word Third (this took me longer than I’d care to admit)
5. The third word needs us to do some work with the first and 12th word
6. We’re looking for an image what will reveal a passcode

Query Case 6

//As I mentioned before sorting this correctly, makes a world of difference, my solution was anything but elegant so in this case I’d rather present this steamlined piece of art curtesy of Aviv

let punctuations = “`[[:punct:]]“`;
NationalGalleryArt
| extend TitleUnified = toupper(Title)
| extend TitleWords = extract_all(@'(\w+)’, TitleUnified)
| mv-expand TitleWords to typeof(string)
| extend punctuation_index = indexof_regex(TitleWords, punctuations)
| extend last_index = iff(punctuation_index>=0, punctuation_index,
trlen(TitleWords)+1)
| extend Word = substring(TitleWords, 0, last_index)
| where strlen(Word) >= 3
| summarize WordCount=count() by Word
| sort by WordCount desc

//look at that a timeline we can look for, along with a strange piece of art https://api.nga.gov/iiif/64c9eb07-5e01-40fe-8fd0-886cfb4a70c7/full/!900,900/0/default.jpg
NationalGalleryArt
| where Title has_all(“day”, “month”, “year”)

We’re not done yet, lets head over to Kuanda.org and see what we can find. In order to join the club we need a passcode

What happens if we use our image as the login hint?

A-ha! We’re onto something now, lets try unscrambling the letters being held by the octopus and we get the nefarious stopkusto!

And we’re in and can see all the trickery that Kuanda has been up to so far including the name of their leader.

Another case solved Detectives well done!

This case was very challenging, and a lot of the difficulty comes down the interpretation of the riddle, even assuming that all your KQL was spot on, making the wrong assumptions about which words to look for could take hours of hunting. While I did feel like I needed to hand in my Kusto card for a while I did get there eventually.

Loading

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 4 – Triple trouble!

Challenges

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?

Sincerely,
Gaia

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=
IpInfo;
let potentials=
NetworkMetrics
| 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;
NetworkMetrics
| 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!

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 2 – Catch the Phishermen!

Challenges

Click for challenges

  • Onboarding: Here
  • Challenge 1: Here
  • Challenge 2: This article
  • 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

Time to catch some phishermen! I’m sure we have a special dislike for unsolicited phone calls, worse if it’s a potential scammer! Yet another great real-world use case for the power of KQL, I’m sure my Cybersecurity colleagues had a great time with this one.

General advice

For this case there are some assumptions that have to be made about certain behavior, I would suggest starting as simply as possible as once you go down certain rabbit holes you can go off down a long path which will not get you to the right answer. Check out those clues!

Challenge: Case 2

Case 2 challenge text

Hey Detective,

We’ve got another case that needs your expertise! The people of our city are being targeted by phishermen, and they need your help to stop them in their tracks.

The complaints are pouring in, and people are fed up with the sudden increase in phishing calls attempting to steal their identity details. We can’t let these scammers get away with it, and we need your help to catch them!

The police have asked for our assistance, and we’ve got a massive data set to work with. We’ve got listings of all the calls that have been made during the week, and we need to find the source of the phishing calls.

It’s not going to be easy, but we know you’re up for the challenge! We need you to analyze the data and use your detective skills to find any patterns or clues that could lead us to the source of these calls.

Once we have that information, the police can take action and put a stop to these scammers once and for all! Are you ready to take on this challenge, detective?

We’ve got your back, and we know you can do this! Let’s catch those phishermen!

Best regards,
Captain Samuel Impson.

Time to find that scammer!

Query Hint

The are a couple of different way to get to the right phone number but the command we’ll use are the same. Check out these KQL commands for some help extend, join and dcount.

Depending on the assumptions you’ve made about the scammer behavior it will also affect how you start writing your query.

Solution – Spoilers below

What were your assumptions detective?

Query Case 2

//The key behaviors that come to mind are firstly that the scammer probably called a lot of people, secondly those calls were probably on the short side because we know the citizens are getting fed up.

//Who called the most citizens?

PhoneCalls
| where EventType == ‘Connect’
| extend Origin = tostring(Properties.Origin)
| extend Dest = tostring(Properties.Destination)
| extend IsHid = tostring(Properties.IsHidden)
| where IsHid == “true”
| join kind=inner
    (PhoneCalls
    | where EventType == ‘Disconnect’
  | extend DiscBy = tostring(Properties.DisconnectedBy)
    | where DiscBy == “Destination”)
    on CallConnectionId
| summarize Scammer = dcount(Dest) by Origin
| top 1 by Scammer

Another criminal brought to justice, great work detectives! This wasn’t the most exciting case but the application of useful KQL commands more than made up for it, this challenge was a great learning exercise, nicely done Kusto Detective Agency team!

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