Mission: 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!
For all the marbles…: Who is the detective that earned most money in 2022?
Create the table.
.execute database script <|
.create table DetectiveCases(Timestamp:datetime, EventType:string, DetectiveId:string, CaseId: string, Properties:dynamic)
//clear any previously ingested data if such exists
.clear table DetectiveCases data
// Load the data:
.ingest async into table DetectiveCases (@'https://kustodetectiveagency.blob.core.windows.net/kda2start/log_00000.csv.gz')
.ingest async into table DetectiveCases (@'https://kustodetectiveagency.blob.core.windows.net/kda2start/log_00001.csv.gz')
.ingest into table DetectiveCases (@'https://kustodetectiveagency.blob.core.windows.net/kda2start/log_00002.csv.gz')
The boss, in each case, has to be the one who solved the case first.
DetectiveCases
| where EventType == 'CaseSolved'
| summarize arg_min(Timestamp, *) by CaseId
This gives us a table of the detectives who got loot in each case.
The top chef, is the one who got the most loot!
To do this, we have to join this table, with one that has information of the case’ bounty.
DetectiveCases | distinct EventType
Let’s look at how the data looks at the time a case is opened.
DetectiveCases
| where EventType == 'CaseOpened'
| take 5
DetectiveCases
| where EventType == 'CaseSolved'
| summarize arg_min(Timestamp, *) by CaseId
| join kind=inner(DetectiveCases
| where EventType == 'CaseOpened'
| extend Bounty = Properties.Bounty)
on CaseId
| project CaseId, DetectiveId, Bounty
| summarize sum(toreal(Bounty)) by DetectiveId
| top 5 by sum_Bounty
