How to Run Osqueryi With Kolide Launcher Tables

Kolide Launcher has been a labor of love since the inception of our company. It provides auto-update functionality and vastly extends the core data collection capabilities of the vanilla osquery agent. Our SaaS users can attest to the fact that our product collects data which could not be gathered otherwise.

Running an Osqueryi Instance Which Is Similar to Kolide’s Live Query

While all of Launcher’s tables are available to query via the Live Query feature, we are frequently asked for a way to locally query a device using the interactive osqueryi CLI tool with those same tables. Although Launcher does not currently support an interactive mode, we do ship the tables as a standalone osquery extension.

The steps below will walk you through configuring a local osqueryi test-environment outside of the Kolide SaaS Live Query feature. This is helpful when trying to understand how a query works, why it might not be returning data, or simply for crafting new queries and rapidly iterating locally.

To run an osqueryi session with all of the Kolide enabled tables, you will need three things:

• The Kolide Launcher tables.ext extension,
• A local config.json file which contains any ATC tables you wish to query
• The osqueryd binary which Kolide’s launcher has installed.

How To Retrieve the Latest Release Version of Launcher’s Tables.ext Extension

To retrieve the tables.ext file:

• Visit the following Launcher Releases Page: GitHub / Kolide / Launcher / Releases

• Scroll down to the latest release and download the appropriate binary for your desired platform (eg. darwin.universal.tar.gz).

• With the tar downloaded and extracted, open the folder and note the path of the tables.ext extension file inside (eg. /Users/fritz/Downloads/darwin.universal/tables.ext)

How To Retrieve Your ATC Configuration Block From the Kolide SaaS Product

Kolide registers Automatic Table Creation (ATC) tables to expand the data collection capabilities of its agent. ATC allows the parsing of local SQLite databases by specifying the following:

• The name of your new table (eg. tcc_system_entries).
• The path to the SQLite file (eg. /Library/Application Support/com.apple.TCC/TCC.db).
• The columns (schema) you expect to return into your new table
• The query which will populate your schema (eg. SELECT column_name FROM table_name).

You can find more information about ATC tables and how they can be utilized in our article: How to Build Custom Osquery Tables Using ATC.

To register your ATC tables, you will need to pass a config file. You can retrieve the Kolide ATC configuration block by scrolling down in your osquery config to find them at the bottom:

For example:

1. Go to a device detail page (by clicking on a device name in Inventory) in Kolide.
2. Click the ‘Actions’ dropdown and select ‘View Osquery Config’.
3. Copy/Paste the section of the JSON file that is within the curly braces of the "auto_table_construction:" section, (including the header: "auto_table_construction:") into an editor and save it as something like kolide-atc-config.json.

If you prefer, you can copy/paste the entire configuration instead of using just that portion. Other parts of the configuration (eg. scheduled queries) will not run when in interactive mode.

How To Locate the Latest Kolide Supplied Version of Osqueryd on Your Device

Kolide auto-updates osquery and places an osqueryd binary on disk in a predictable location:

/usr/local/kolide-k2/bin/osqueryd-updates/

Inside that folder will be one or many numbered folders which represent the unix epoch when that binary was retrieved.

For example:

1645124825/osqueryd

Make note of the complete path to the osqueryd binary for the command we will run.

Pulling It All Together: Invoking Osqueryi

With all of those pieces in place, we can start osqueryi with both our launcher tables and our ATC tables by running the following command (modified to your appropriate path names) in our terminal:

sudo /usr/local/kolide-k2/bin/osqueryd-updates/1645124825/osqueryd -S --allow-unsafe --verbose --extension /Users/fritz/Downloads/darwin.universal/tables.ext --config_path /Users/fritz/Downloads/kolide-atc-config.json

As osqueryi starts, it will list the tables which are being registered from the extension and your ATC config file, and you will see those tables listed out like so:

osquery> I0616 16:04:27.020655 7835648 interface.cpp:137] Registering extension (com.kolide.standalone_extension, 53524, version=, sdk=)
I0616 16:04:27.026579 7835648 registry_factory.cpp:107] Extension 53524 registered table plugin kext_policy
I0616 16:04:27.026597 7835648 registry_factory.cpp:107] Extension 53524 registered table plugin kolide_airdrop_preferences
I0616 16:04:27.026602 7835648 registry_factory.cpp:107] Extension 53524 registered table plugin kolide_airport_util
I0616 16:04:27.026605 7835648 registry_factory.cpp:107] Extension 53524 registered table plugin kolide_apfs_list
I0616 16:04:27.026609 7835648 registry_factory.cpp:107] Extension 53524 registered table plugin kolide_apfs_users
I0616 16:04:27.026612 7835648 registry_factory.cpp:107] Extension 53524 registered table plugin kolide_app_icons

Those tables with the kolide_ prefix are those tables which belong to Launcher.

Lastly, we can verify that our invocation worked and that we are able to query these extension tables successfully.

Let’s try it now on a macOS device:

osquery> SELECT * FROM kolide_filevault;
+------------------+
| status           |
+------------------+
| FileVault is On. |
+------------------+

Fabulous! We can now locally run queries in our terminal as if we were using Kolide’s Live Query feature.

Notes:

• We run the command with sudo because by default the Kolide Launcher agent runs with root permissions, and the behavior of several tables is predicated on the user context under which they are queried. (For more information refer to the following blog article: Running Osquery As Sudo/root vs User.)

• The -S flag in our invocation command is what allows us to initiate the osqueryi interactive mode from the osqueryd binary.

• Do not forget, as updates are made to the osquery agent, your previously run osqueryi invocation command will need to be updated to reference the corresponding updated path.