Background: Someone has hacked into the Digitown municipality system and stolen these documents.
Mission: Shed some light on who is behind these crimes and clear Gaia’s name.
For all the marbles: Who is behind all this?
A guy got in and out of a system with a document.
.execute database script <|
.create-merge table IpInfo (IpCidr:string, Info:string)
//clear any previously ingested data if such exists
.clear table IpInfo data
.ingest into table IpInfo (@'https://kustodetectiveagency.blob.core.windows.net/kda2c4network/ip-lookup.csv.gz')
.create-merge table NetworkMetrics (Timestamp:datetime, ClientIP:string, TargetIP:string, BytesSent:long, BytesReceived:long, NewConnections:int)
.clear table NetworkMetrics data
.ingest async into table NetworkMetrics (@'https://kustodetectiveagency.blob.core.windows.net/kda2c4network/log_00000.csv.gz')
.ingest async into table NetworkMetrics (@'https://kustodetectiveagency.blob.core.windows.net/kda2c4network/log_00001.csv.gz')
// Last command is running sync, so when it finishes the data is already ingested.
// It can take about 1.5min to run.
.ingest into table NetworkMetrics (@'https://kustodetectiveagency.blob.core.windows.net/kda2c4network/log_00002.csv.gz')
We need to find the anomaly.
Anomaly by time:
For each client, let’s find the high level time they were active in the hub.
NetworkMetrics
| summarize arg_min(Timestamp, *) by ClientIP
| join kind=inner(NetworkMetrics
| summarize arg_max(Timestamp, *) by ClientIP)
on ClientIP
| project ClientIP, Duration=datetime_diff('day', Timestamp1, Timestamp), BytesSent, BytesReceived
| distinct Duration
We notice that there is a client IP that connected for less than a day.
NetworkMetrics
| summarize arg_min(Timestamp, *) by ClientIP
| join kind=inner(NetworkMetrics
| summarize arg_max(Timestamp, *) by ClientIP)
on ClientIP
| project ClientIP, Duration=datetime_diff('day', Timestamp1, Timestamp), BytesSent, BytesReceived
| order by Duration asc
| take 1
| evaluate ipv4_lookup(IpInfo, ClientIP, IpCidr)
Anomaly by bytes:
NetworkMetrics
| summarize sum(BytesSent) by ClientIP
| order by sum_BytesSent asc
| take 1
| evaluate ipv4_lookup(IpInfo, ClientIP, IpCidr)