Challenges
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
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.
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.
//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!
