Background: There has been a sudden increase in unsolved cases of stolen cars all across our city. It’s possible that the car’s identification plates were replaced during the robbery, which makes this case even more challenging.
Mission: 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.
For all the marbles: Where are the stolen cars being kept?
Stolen cars → changed plates → storage.
.execute database script <|
// It takes about 1min to script to complete loading the data
.set-or-replace StolenCars <|
datatable(VIN:string)
['LG232761G','SA732295L','MW406687Q','PR843817F','EL438126P',
'GA871473A','IR177866Y','LP489241B','AS483204L','DO255727O',
'BV850698T','YZ347238C','NJ586451R','VB724416I','SI241398E',
'IN149152E','PV340883B','CK552050Z','ZJ786806D','KU388194T']
.create-merge table CarsTraffic(Timestamp:datetime, VIN:string, Ave:int, Street:int)
//clear any previously ingested data if such exists
.clear table CarsTraffic data
.ingest async into table CarsTraffic(@'https://kustodetectiveagency.blob.core.windows.net/kda2c3cartraffic/log_00000.csv.gz')
.ingest async into table CarsTraffic(@'https://kustodetectiveagency.blob.core.windows.net/kda2c3cartraffic/log_00001.csv.gz')
.ingest into table CarsTraffic(@'https://kustodetectiveagency.blob.core.windows.net/kda2c3cartraffic/log_00002.csv.gz')
We need to start by tracking where the cars ended up before their plates got changed. We do this by checking the last place that these cars were seen.
CarsTraffic
| where VIN in (StolenCars)
| summarize arg_max(Timestamp, *) by VIN
| distinct Ave, Street
Next we find the last places cars that come from these two places end up. We are looking for a place holding 20 cars.
CarsTraffic
| summarize arg_min(Timestamp, *) by VIN
| where (Ave == 223 and Street == 86) or (Ave == 122 and Street == 251)
| join kind=inner(CarsTraffic
| summarize arg_max(Timestamp, *) by VIN)
on VIN
| project VIN, Ave=Ave1, Street=Street1
| summarize count() by Ave, Street
| where count_ == 20
