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.

Geographic functions

Azure Data Explorer supports multiple geographic functions. In this case, I will only use one of them.

Geolocation information

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.

NetworkMetrics
| 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.

Conclusion

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.