In case 4 of the Kusto Detective Agency, you need to search for a hacker that hacked into the Digitown municipality system and stole documents. All you got is a 30-day traffic statistics report captured by the Digitown municipality system network routers. After the challenge, I wondered, can I get more information about the hacker, like where the hacker is located. Let’s play and dive deeper into the data…
Note: Not to spoiler anything, I didn’t include screenshots or any queries based on my answer.
Azure Data Explorer supports multiple geographic functions. In this case, I will only use one of them.
In the challenge, we only got the client’s IP-address. To get more information about that IP-address, I used the function
geo_info_from_ip_address. This function will retrieve a dynamic object with geolocation information. The dynamic object may contain country, state, city, longitude and latitude. With those last two columns (longitude and latitude) I can plot the hacker on a map.
So, how does it work? First, start to retrieve the geolocation information:
NetworkMetrics | extend ip_location = geo_info_from_ip_address(ClientIP)
Next, I extended the dataset with longitude and latitude based on information of the ip_location column to make it easier for myself in the next steps.
| extend ip_location = geo_info_from_ip_address(ClientIP)
| extend latitude = ip_location.latitude, longitude = ip_location.longitude
The result, when executing the query, will look something like:
Visualize it on a map
Now we have the longitude and latitude in a column, the next step is to plot them on a map. For this, I use a scatter plot. When using the scatter plot add the option
kind = map this plots the points on a map. In the example below, I limited the number of records to a 1000 (because of the performance) and also appointed the x- and y-columns.
NetworkMetrics | take 1000 | extend ip_location = geo_info_from_ip_address(ClientIP) | extend latitude = ip_location.latitude, longitude = ip_location.longitude | render scatterchart with (kind = map, xcolumn = longitude, ycolumns = latitude)
This will result in a map with all the IP-addresses plotted on a map.
With the use of the function
geo_info_from_ip_address and a scatter plot, it is possible to locate someone based on an IP-address.