Locate Assets with Osquery

A simple query for IP-Geolocation

This simple strategy for obtaining the location of an osquery device utilizes the ipapi.co API to retrieve the IP geolocation of the device. Note that the device must be able to connect to the internet over HTTP, and the calculated location may be skewed by VPN, proxies, etc.

Query:

SELECT JSON_EXTRACT(result, '$.ip')      AS ip,
       JSON_EXTRACT(result, '$.city')    AS city,
       JSON_EXTRACT(result, '$.region')  AS region,
       JSON_EXTRACT(result, '$.country') AS country
FROM   curl
WHERE  url = 'http://ipapi.co/json';

Sample result:

+--------------+------------+------------+---------+
| ip           | city       | region     | country |
+--------------+------------+------------+---------+
| 71.92.162.65 | Sacramento | California | US      |
+--------------+------------+------------+---------+

Other techniques

A common technique for geolocation of macOS devices with osquery is to use the wifi_survey table in combination with the Google Geolocation API. This strategy has become more difficult to use due to security controls on macOS 10.15, and poses privacy concerns due to the precision of the location data returned by the API.


Work with the experts to craft your open-source security strategy. Contact us to arrange a consultation.