Challenges
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
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
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
//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!
I guess there is problem in the query, correct me if I am wrong.
When you first filter by location and then by recent timestamp it is going to take the vehicles first appearance to that area. when you first find find least timestamp and then filter by location it will give yo the vehicles who first appeared from that location. so the method you have used is actually having large values in the list basically all vehicles who came to those areas (parking lots).
Since we are checking for the VIN change we are interested in the VINS which first appeared at the Parking lot and then you can find their last appearance and summarize by locations.
Something like this, I know this is not really a big change but I think it explains the logic.
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);
//From the list of all the suspected cars finding their last location and grouping its by location
CarsTraffic
| where VIN in (Suspects)
| summarize arg_max(Timestamp, *) by VIN
| summarize Vins = count(VIN) by Ave, Street
| order by Vins
Great catch and this does show the value in ordering filters correctly. Like next time coffee first then KQL 🙂
Pingback: Kusto Detective Agency Season 2 – Onboarding - OpsMan
Pingback: Kusto Detective Agency Season 2: Case 4 - Triple trouble! - OpsMan
Pingback: Kusto Detective Agency Season 2: Case 2 - Catch the Phishermen! - OpsMan
Pingback: Kusto Detective Agency Season 2: Case 1 - To bill or not to bill? - OpsMan
Pingback: Kusto Detective Agency Season 2: Case 5 - Blast into the past - OpsMan
Pingback: Kusto Detective Agency Season 2: Case 7 - Mission 'Connect' - OpsMan
Pingback: Kusto Detective Agency Season 2: Case 8 - Catchy Run - OpsMan