Osquery, a key component of the Kolide Agent, is an open-source host instrumentation system that runs on Mac, Windows, and Linux devices. It provides deep introspection capabilities and consistent access to a large number of data sources. Using a SQL query language, this power is accessible to users from a wide range of technical backgrounds.

This article serves as a primer to understanding osquery, the use-cases it is intended to solve, its underlying architecture, and how Kolide uses it throughout our platform.

The Problem

Thanks to today’s highly complex governance, IT, and security landscape, organizations have never been more in need of highly reliable and timely data about the devices in their fleet.

From IT verifying their fleet meets the minimum requirements of SOC2, to detection engineers looking to search for indicators of compromise (IOCs), to Identity and Access (IAM) teams ensuring devices meet minimum posture requirements before allowing them to authenticate, it’s clear that understanding what’s happening on a device in a reliable way is a highly valued commodity.

Unfortunately, despite generous spending from IT and Security on highly sophisticated endpoint-based agents, these agents often fail to enumerate anything beyond the basic high-level facts about a device, often leaving it up to IT teams to write shell scripts and directly parse text files to get the data they need.

Solving this problem isn’t easy. Modern operating systems hold a wealth of data about their operation. This data is spread across APIs, files, and formats, rendering it difficult to find and track the important information.

Before Osquery existed, IT teams had often built their own in-house solutions which often suffered from extreme fragility, lack of cross-platform support, poor performance, and even incorrect data.

Goals

Osquery is designed to provide the data organizations need without recreating many of the pitfalls common in bespoke solutions. At a high level, the osquery project achieves the following objectives:

• First-class support for macOS, Windows, and Linux platforms.
• Performant operation for deployment across corporate and production infrastructure.
• Capabilities exposed with no need for end users to write additional code.

Created at and later open-sourced by Facebook, osquery is now under the ownership and governance of The Osquery Foundation, which is under The Linux Foundation. Osquery is the underlying agent for dozens of security solutions and runs on millions of devices across the globe.

From its open-source release in 2014, osquery has supported the macOS and Linux platforms. Full support for Microsoft Windows was added in late 2016. Across all of these platforms, osquery provides statically linked binaries that provide easy-to-deploy capabilities across an entire fleet with no dependencies required.

IT and Security staff are empowered to focus on their information retrieval goals by the standardized query language. Osquery uses SQLite under the covers, enabling all of the productivity and expressiveness of SQL. Operating system concepts mostly wrap neatly to this row-based format, smoothing differences across the formats and APIs that traditionally provide this information.

Kolide Use-Cases / Features

Osquery is a central pillar of Kolide’s offering and a major component of Kolide’s Agent. Kolide uses osquery to obtain information about a device’s posture, which powers features like Device Properties and Checks. Beyond this, Kolide serves as a fully compliant osquery remote server, offering features that allow customers to directly interact with the agent. These include the Log Pipeline and Live Query.

Writing Osquery SQL

Osquery is an endpoint visibility tool that translates native API calls into SQLite virtual tables, allowing a Mac, Linux, or Windows computer to be queried in real-time as if it were a relational database:

SELECT name, pid, parent FROM processes LIMIT 5;

+----------------+-----+--------+
| name           | pid | parent |
+----------------+-----+--------+
| kernel_task    | 0   | 0      |
| launchd        | 1   | 0      |
| syslogd        | 67  | 1      |
| UserEventAgent | 68  | 1      |
| uninstalld     | 71  | 1      |
+----------------+-----+--------+

If you are interested in writing your own queries, we recommend reading through Osquery’s official SQL Introduction guide.

Once you’ve worked your way through that guide, we recommend reading the following supplemental articles.

• Tutorial - How to Find a Mac’s Manufacture Date Using Osquery
• Tutorial - How to Deal With Dates and Times in Osquery
• Tutorial - The File Table: Osquery’s Secret Weapon
• Tutorial - How to Spotlight Search Across Every Mac With Osquery
• Tutorial - How to Read Nested Complex Plists in Osquery
• Deep Dives - Why You Can’t Trust Your NULLs in Osquery