This simple query leverages Live Discover using cURL to geolocate devices. Here's how it works:
- cURLs out to ifconfig.me/ip to grab the devices' WAN IPs
- using the response of step one as input, cURLS out to ipapi.co to find location information, including city, state, latitude, and longitude.
Although this is a basic query to get a rough idea of the location of devices, you can leverage the syntax with other queries, e.g. "Devices that have a Remote Desktop connection" to locate remote connections, or add you own enrichment for use whenever you see an IP you want more data on.
SELECT
json_extract(curl.result,'$.ip') IP_Address,
json_extract(curl.result,'$.city') City,
json_extract(curl.result,'$.region') State,
json_extract(curl.result,'$.latitude') Latitude,
json_extract(curl.result,'$.longitude') Longitude
FROM curl
WHERE url = 'http:' || '/' || '/' || 'ipapi.co/' || (SELECT result FROM curl WHERE url = 'http:' || '/' || '/' || 'ifconfig.me/ip') || '/' || 'json'
