Kusto Detective Agency Season 3 — Call of the Cyber Duty — Case 2— Coffee Trails

Case Description:

Detective,

The location you uncovered? Nailed it. Police moved in within minutes. But the place was already ice-cold — no hackers, no hardware, not even a forgotten USB stick. Just one thing still powered on in the corner, humming like it had nothing to hide: a smart-coffee machine. Ordinary? Not quite. It turns out this machine logs nearby Bluetooth activity — not the actual devices, but short hash codes. Just 16 bits each. Barely anything… unless you know what to look for.

I’ve secured cooperation from the machine vendor. They’ve handed over logs from every connected unit in Digitown. If what we’re seeing is more than just leftover caffeine ghosts, then it’s possible — just possible — someone from that first location has left another trace. See if you can track them to a second gathering spot — if they slipped, it might still be out there.

Stay sharp. You’re espresso-close to cortado them.

— CopsAI ☕

Now, Let’s get start with the log

.execute database script <|
 // Static data of coffee machines and its owners
.create-merge table CoffeeMachines (Address:string, CoffeeMachineId:string, RegistrationInfo:string)
.ingest into table CoffeeMachines (@'https://kustodetectiveagency.blob.core.windows.net/kda3c02/coffee_machines_00000.csv.gz')
// Coffee logs data
.create-merge table CoffeeMachineLogs (Timestamp:datetime, CoffeeMachineId:string, ConnectedDeviceHash:string, Action:string)
.ingest async into table CoffeeMachineLogs (@'https://kustodetectiveagency.blob.core.windows.net/kda3c02/coffee_logs_00000.csv.gz')
.ingest async into table CoffeeMachineLogs (@'https://kustodetectiveagency.blob.core.windows.net/kda3c02/coffee_logs_00001.csv.gz')
.ingest into table CoffeeMachineLogs (@'https://kustodetectiveagency.blob.core.windows.net/kda3c02/coffee_logs_00002.csv.gz')

After ingested, we browse sample of data

CoffeeMachines — Log Sample

CoffeeMachineLogs — Log Sample

We two table, look like we need to join them or enrich the Logs with CoffeeMachine metadata in these tables.

Let check what device connect to coffee machine in “13 Ave, 37 St”

let Case1Machine = CoffeeMachines | where Address == '13 Ave, 37 St';
CoffeeMachineLogs
| where CoffeeMachineId  in (( Case1Machine | distinct CoffeeMachineId)) 
| summarize UniqueConnectedDevice = dcount(ConnectedDeviceHash), FirstLog=min( Timestamp), LastestLog = max(Timestamp)

Better result now with 58 devices, but look like we have to filter timerange to when attack happen on case 1 to check who connect during that time range.

let StartTime= toscalar(HackersLogs | summarize StartTime = min(Timestamp) );
let EndTime= toscalar(HackersLogs | summarize EndTime=max(Timestamp));
let Case1Machine = CoffeeMachines | where Address == '13 Ave, 37 St';
CoffeeMachineLogs
| where Timestamp between ( StartTime .. EndTime )
| where CoffeeMachineId  in (( Case1Machine | distinct CoffeeMachineId))
| summarize Records = count(), UniqueDevice= dcount(ConnectedDeviceHash)

Which give us 18 records with 14 devices

Ok, lets check we these device connected in whole log

let StartTime= toscalar(HackersLogs | summarize StartTime = min(Timestamp) );
let EndTime= toscalar(HackersLogs | summarize EndTime=max(Timestamp));
let Case1Machine = CoffeeMachines | where Address == '13 Ave, 37 St';
let SuspiciousDeviceHash=
CoffeeMachineLogs
|  where Timestamp between ( StartTime .. EndTime )
| where CoffeeMachineId  in (( Case1Machine | distinct CoffeeMachineId)) 
;
CoffeeMachineLogs
| where ConnectedDeviceHash in (( SuspiciousDeviceHash | distinct ConnectedDeviceHash))
| join kind=inner  CoffeeMachines on CoffeeMachineId
| summarize dcount(ConnectedDeviceHash) by Address,RegistrationInfo
| sort by dcount_ConnectedDeviceHash desc 

Besides the address from case 1 we notice another stand out location 127 Ave, 42 St. Therefore the another place they gathered is Rima Zen.

If you have some nice query feel free to share in comment 😊