In case you're stuck, or if you're not participating in this season and just reading along to gain some KQL skills, I hope that you find use in these notes.
SPOILERS AHEAD!
Assignment and data
We start the case by ingesting data into a table named 'StorageArchiveLogs'. The premise is that an interesting podcast recording has been stored in a cloud storage location, but that it was quickly deleted. We're asked to apply our data skills to find remnants of the interview.
Getting our whereabouts
Let's start by taking a look at some of the logs in StorageArchiveLogs.
There are two columns: Timestamp and EventText.
The Timestamp column appears to be just that - a timestamp for the event.
The EventText column contains a string that appears to have useful details, like the transaction type, a URL, and the action count.
It looks like all of our results are of type "Read blob transaction". That's probably not representative of the entire dataset. Let's see what else there is to work with by excluding these log types from the results.
Great, now we can see that there are also logs of types "Delete blob transaction" and "Create blob transaction".
The "Delete blob transaction" logs contain only the URL of the blob that was removed.
The "Create blob transaction" logs, however, also contain a second URL for the location of the backup of the blob.
Very interesting! I think we're ready to get started.
Solution
I suspect that we need to look for a blob that was created and then quickly deleted.
First, I will parse the EventText column using the parse operator. Overall, we should take the path of least resistance, and use our creativity! I opted to use a combination of the simple parsing mode of the parse operator and a function that I don't think sees a lot of use (but is very useful!): the minif aggregation function.
Click here for full KQL
StorageArchiveLogs
| parse EventText with OperationType ```: '``` BlobURL ```' ``` ExtraDetails
| summarize
CreationTime = minif(Timestamp, OperationType == 'Create blob transaction'),
DeletionTime = minif(Timestamp, OperationType == 'Delete blob transaction'),
make_set(ExtraDetails) by BlobURL
| extend LifeTime = DeletionTime - CreationTime
| where isnotempty(LifeTime)
| top 5 by LifeTime ascIn the parse operator, you'll notice that I've opted to use triple backticks. This is my preference for difficult strings that contain characters like ', ", or newlines. I'm extracting three new columns: OperationType, BlobURL, and ExtraDetails.
For my solution, I've decided to focus on the difference between a blob's creation and deletion times. I've called this new column "LifeTime", which is calculated as the difference between the first Delete and Create transactions for a BlobURL. I'm using the make_set aggregation function to include the unique values of ExtraDetails in the output of the search.
Next, I'm filtering out any rows that don't have a LifeTime value, which will be the case for blobs that haven't been deleted (yet). And lastly, I'm using the top operator with the asc parameter to surface the 5 results with the lowest LifeTime value.
If we examine the results, one of them certainly stands out. It seems that this blob was removed 6 minutes after it was created! Very suspicious... We can also infer from the file extension that it is likely a video file, which aligns with the assignment.
When we look at the interesting result more closely, we can see that one of the ExtraDetails values for this blob includes a second URL. This supposedly links to the location of a backup of the file. I wonder... could that backup still be accessible?
Oh yes, and that's the flag! I immediately watched the video. Kudos to the Kusto Detective team, as they really went all-out this time to create an informative and funny 'podcast episode'! The content is indeed very exciting, and I encourage you to take a look. Here is the link: https://storagebackup.azureedge.net/okeexeghsqwmda/vyskl/jqfovf.mp4.