Geolocation of Mac OS assets with wifi_survey in Osquery.

As with most tables in Osquery. You can gleam a whole load more information by joining tables together. Or enriching results with external data sources.

The wifi_survey table is a perfect example of this. We’re given all the data we need in order to work out the exact location of our asset via Google’s geolocation services.

Using wifi_survey to get an assets geolocation.

The wifi_survey table is currently only available for Mac OS as of Osquery 4.6.0. It also goes without saying the asset you’re querying will need a working WiFi card. Which you can check the status of by querying the wifi_status table.

osquery> SELECT interface, channel, country_code FROM wifi_status;
+-----------+---------+--------------+
| interface | channel | country_code |
+-----------+---------+--------------+
| en0       | 36      | GB           |
+-----------+---------+--------------+

Now onto the wifi_survey table. This table provides you with a full breakdown of all the available WiFi access points that your computer has access too. Including hidden SSIDs.

There are a quite few fields available to you. However, the ones we’re interested in and the ones we’ll be sending to Google via their API are:

• bssid (mac address)
  This is the remote access points MAC address. Which can actually be used to identify the manufacture of the device. Moreover, the MAC address is used to uniquely identity the device. This information was and still is being collected by Google. As Google identifies devices they also record their location. Which when we send Google a list of MAC address through their API call they can work out where they’ve seen that device before and send us back the geolocation. The rssi and noise help improve the accuracy.
• rssi (signal strength)
  The current received signal strength indication (dbm). The rssi is a measure of power level that an asset is receiving from the access point. At larger distances, the signal gets weaker. The smaller the value the closer the asset is to the device.
• noise (signal to noise ratio)
  The current noise measurement — desired signal to the level of background noise. (dBm).

osquery> SELECT bssid, rssi, noise FROM wifi_survey;
+-------------------+------+-------+
| bssid             | rssi | noise |
+-------------------+------+-------+
| 5e:b1:3e:00:00:00 | -64  | -90   |
| 5c:b1:3e:00:00:00 | -62  | 0     |
| 5c:b1:3e:00:00:00 | -64  | -90   |
| 24:20:c7:00:00:00 | -88  | -90   |
| c4:41:1e:00:00:00 | -39  | 0     |
| 5e:b1:3e:00:00:00 | -64  | -90   |
+-------------------+------+-------+

Once you’ve got that data. We can pass the result to Google’s geolocate API. Which you can get a free API key for here:

Get an API Key | Geolocation API | Google Developers

Its a single API call. Which we’ve put together an example for in Python. Its important that within the wifiAccessPoints key, you provide as many WiFi access points as you can. In order to get the best (accurate) results.

#!/usr/bin/env python3
# -*- coding: utf-8 -*-
import requests
url = 'https://www.googleapis.com/geolocation/v1/geolocate'
params = {
    'key': GOOGLE_CLOUD_API_KEY
}
return requests.post(url, params=params, json={
    'considerIp': False,
    'wifiAccessPoints': [{  # Provide your array of access points
        'macAddress': bssid,
        'signalStrength': rssi,
        'signalToNoiseRatio': noise
    }]
}).json()

Running this Python code will yield Google’s best effort at a geolocation of the asset. If you’re using the above code example you can wrap the requests.post method in a print statement to get the following:

{
  "location": {
    "lat": 37.421925,
    "lng": -122.0841293
  },
  "accuracy": 30
}

This geolocation result can then be used further to translate the lat (latitude) and lng (longitude) into a complete street address. This time we’re using the geocode API. Also provided by Google — though you could use others. The same API key can be used.

url = 'https://maps.googleapis.com/maps/api/geocode/json'
params = {
    'latlng': '{},{}'.format(lat, lng),
    'key': GOOGLE_CLOUD_API_KEY
}
return requests.post(url, params=params).json()

Which will yield (this repose is quite large and we’ve trimmed the result in our example):

{
   "results" : [
      {
         "address_components" : [ .. ],
         "formatted_address" : "145 City Rd, Hoxton, London EC1V 1AZ",
         "geometry" : {
            "location" : {
               "lat" : 37.4224764,
               "lng" : -122.0842499
            },
            ..
         },
         "place_id" : "ChIJ2eUgeAK6j4ARbn5u_wAGqWA",
         "plus_code": {
            "compound_code": "CWC8+W5 Hoxton, London EC1V 1AZ",
            "global_code": "849VCWC8+W5"
         },
         "types" : [ "street_address" ]
      }
   ],
   "status" : "OK"
}

Which of course you can then also use to display the exact location visually using the Google Maps API or embed within your own applications.

Using the wifi_survey table to geolocate assets with Osquery

Its all over!

Hopefully you’ve enjoyed that short post on how the wifi_survey table can be used to geolocate Mac assets. However, that’s all for now. Please feel free to get in touch if you have any questions.