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!
