It’s time for season 2 of Kusto Detective Agency, a fun way to test and hone your Kusto Query Language (KQL) skills!
The first mission of season 2 (Onboarding) asks you to find the id of the detective that earned the most money in 2022, based on the data present in the DetectiveCases table.
After injecting the data, we can take a quick look at the table (I’ve edited the timestamps to reduce the width of the table).
DetectiveCases
| take 10
| Timestamp | EventType | DetectiveId | CaseId | Properties |
|---|---|---|---|---|
| 2022-06-05 04:52:00 | CaseAssigned | kvc39927c2b59a3c5b70d1 | CASE_0515946 | |
| 2022-06-05 04:52:00 | CaseUnsolved | kvc78dac427567a91ce316 | CASE_0462853 | |
| 2022-06-05 04:52:00 | CaseUnsolved | kvc6e5f6a0b5b659238109 | CASE_0477589 | |
| 2022-06-05 04:52:00 | CaseUnsolved | kvc424fe2f4d2b5b501400 | CASE_0465830 | |
| 2022-06-05 04:52:00 | CaseUnsolved | kvc41b3343db2fd4bcb71f | CASE_0458904 | |
| 2022-06-05 04:53:00 | CaseOpened | CASE_0521475 | {“Bounty”:6167} | |
| 2022-06-05 04:53:00 | CaseUnsolved | kvc7324095bda78cf91517 | CASE_0477512 | |
| 2022-06-05 04:53:00 | CaseUnsolved | kvc658c44632d1f2d525db | CASE_0455097 | |
| 2022-06-05 04:53:00 | CaseAssigned | kvc33a2c659ab5f725d12b | CASE_0518729 | |
| 2022-06-05 04:53:00 | CaseUnsolved | kvc6bf01a86d80faf6a3cf | CASE_0470580 |
So there are events associated to detectives and cases. What kind of events are there?
DetectiveCases
| distinct EventType
| CaseAssigned |
| CaseUnsolved |
| CaseSolved |
| CaseOpened |
The CaseId seems to be common to all records, so let’s take a look at one example.
| Timestamp | EventType | DetectiveId | Properties |
|---|---|---|---|
| 2022-06-03 08:17:00 | CaseOpened | {“Bounty”:5450} | |
| 2022-06-04 02:06:00 | CaseAssigned | kvc1487d972b63c7fbf7b5 | |
| 2022-06-04 04:42:00 | CaseAssigned | kvc27ae220a01d416e70f9 | |
| 2022-06-04 21:14:00 | CaseAssigned | kvc169b30a1b5e4104e02a | |
| 2022-06-05 04:52:00 | CaseAssigned | kvc39927c2b59a3c5b70d1 | |
| 2022-06-05 06:30:00 | CaseAssigned | kvc57d800cd46e28013346 | |
| 2022-06-05 09:48:00 | CaseAssigned | kvc6b174d5b26dcaad80c9 | |
| 2022-06-05 19:45:00 | CaseAssigned | kvc56118f70631428091df | |
| 2022-06-06 06:51:00 | CaseAssigned | kvc1b0d48165cdaf5c22b2 | |
| 2022-06-06 14:46:00 | CaseAssigned | kvc733f22c0f597958f303 | |
| 2022-06-06 14:52:00 | CaseAssigned | kvc7bbe4bf4f5807f986b0 | |
| 2022-06-06 20:43:00 | CaseAssigned | kvc56a9431b61632d5e0c7 | |
| 2022-06-07 09:18:00 | CaseAssigned | kvc77df12ed3ce726cc202 | |
| 2022-06-07 20:39:00 | CaseAssigned | kvc29d392ca965f09646f8 | |
| 2022-06-08 06:03:00 | CaseAssigned | kvc5c8c994dac519e175a2 | |
| 2022-06-08 23:08:00 | CaseAssigned | kvc0242571a3473d65b591 | |
| 2022-06-09 00:48:00 | CaseAssigned | kvc17b66524120a286000b | |
| 2022-06-09 08:27:00 | CaseAssigned | kvc066be95ff4bc08fa532 | |
| 2022-06-09 16:48:00 | CaseAssigned | kvc03ccfc7926d25469596 | |
| 2022-06-09 19:06:00 | CaseAssigned | kvc123e17dd44ef7d5c581 | |
| 2022-06-09 19:44:00 | CaseAssigned | kvc75edecf777cd1e63402 | |
| 2022-06-09 21:25:00 | CaseAssigned | kvc5800cf32381e9ba41b9 | |
| 2022-06-10 04:14:00 | CaseAssigned | kvc7556ead7abedba2970e | |
| 2022-06-10 09:19:00 | CaseAssigned | kvc6b522887583e178d3a5 | |
| 2022-06-10 18:13:00 | CaseAssigned | kvc2a5aa0209a24ab3c04a | |
| 2022-06-10 19:44:00 | CaseSolved | kvc17b66524120a286000b | |
| 2022-06-11 13:05:00 | CaseSolved | kvc1b0d48165cdaf5c22b2 | |
| 2022-06-13 12:38:00 | CaseSolved | kvc066be95ff4bc08fa532 | |
| 2022-06-14 11:43:00 | CaseSolved | kvc6b522887583e178d3a5 | |
| 2022-06-16 17:14:00 | CaseSolved | kvc733f22c0f597958f303 | |
| 2022-06-17 06:29:00 | CaseSolved | kvc5c8c994dac519e175a2 | |
| 2022-06-18 02:07:00 | CaseUnsolved | kvc1487d972b63c7fbf7b5 | |
| 2022-06-18 04:43:00 | CaseUnsolved | kvc27ae220a01d416e70f9 | |
| 2022-06-18 21:15:00 | CaseUnsolved | kvc169b30a1b5e4104e02a | |
| 2022-06-19 04:53:00 | CaseUnsolved | kvc39927c2b59a3c5b70d1 | |
| 2022-06-19 06:31:00 | CaseUnsolved | kvc57d800cd46e28013346 | |
| 2022-06-19 09:49:00 | CaseUnsolved | kvc6b174d5b26dcaad80c9 | |
| 2022-06-19 19:46:00 | CaseUnsolved | kvc56118f70631428091df | |
| 2022-06-20 14:53:00 | CaseUnsolved | kvc7bbe4bf4f5807f986b0 | |
| 2022-06-20 20:44:00 | CaseUnsolved | kvc56a9431b61632d5e0c7 | |
| 2022-06-21 09:19:00 | CaseUnsolved | kvc77df12ed3ce726cc202 | |
| 2022-06-21 20:40:00 | CaseUnsolved | kvc29d392ca965f09646f8 | |
| 2022-06-22 23:09:00 | CaseUnsolved | kvc0242571a3473d65b591 | |
| 2022-06-23 16:49:00 | CaseUnsolved | kvc03ccfc7926d25469596 | |
| 2022-06-23 19:07:00 | CaseUnsolved | kvc123e17dd44ef7d5c581 | |
| 2022-06-23 19:45:00 | CaseUnsolved | kvc75edecf777cd1e63402 | |
| 2022-06-23 21:26:00 | CaseUnsolved | kvc5800cf32381e9ba41b9 | |
| 2022-06-24 04:15:00 | CaseUnsolved | kvc7556ead7abedba2970e | |
| 2022-06-24 18:14:00 | CaseUnsolved | kvc2a5aa0209a24ab3c04a |
It appears that cases are opened (with a bounty), assigned to a number of detectives, then each detective either solves the case or not.
Only the first detective that solves a case earns the bounty, so let’s find them for each case, with the help of arg_min:
DetectiveCases
| where EventType == "CaseSolved"
| project Timestamp, DetectiveId, CaseId
| summarize arg_min(Timestamp, DetectiveId) by CaseId
And then we want to know the bounty for each case:
DetectiveCases
| where EventType == "CaseOpened"
| project CaseId, Properties
The Properties column contains JSON data, so let’s parse it to have a more useful integer:
DetectiveCases
| where EventType == "CaseOpened"
| project CaseId, Bounty=toint(parse_json(Properties)["Bounty"])
Now let’s do a join to find the bounties collected by every detective:
DetectiveCases
| where EventType == "CaseSolved"
| project Timestamp, DetectiveId, CaseId
| summarize arg_min(Timestamp, DetectiveId) by CaseId
| join kind=inner
(
DetectiveCases
| where EventType == "CaseOpened"
| project CaseId, Bounty=toint(parse_json(Properties)["Bounty"])
) on CaseId
Finally, let’s remove columns we don’t need and find the detective with the highest total sum of bounties:
DetectiveCases
| where EventType == "CaseSolved"
| project Timestamp, DetectiveId, CaseId
| summarize arg_min(Timestamp, DetectiveId) by CaseId
| join kind=inner
(
DetectiveCases
| where EventType == "CaseOpened"
| project CaseId, Bounty=toint(parse_json(Properties)["Bounty"])
) on CaseId
| project-away CaseId, CaseId1, Timestamp
| summarize sum(Bounty) by DetectiveId
| order by sum_Bounty desc
The first DetectiveId is the answer, case closed.
Nino Burini 