On 29 October 2014 Facebook’s Security Infrastructure team published osquery on GitHub, a tool that represents the state of an operating system as a set of queryable SQL tables. Running processes, loaded kernel modules, open network connections and listening ports become rows in a relational database, read with the same SELECT you would write against any application table.

Context

Visibility into the state of an endpoint has long been split across heterogeneous tools: ps for processes, netstat or ss for sockets, lsmod for kernel modules, find for the file system, ad hoc parsers for logs. Each has its own output, its own options, its own format to normalise downstream. Across a fleet of dozens of hosts, collecting the same fact takes a script per platform and a parsing pipeline per command.

The idea behind osquery is to reduce that heterogeneity to a single declarative interface. Instead of recalling which flags to pass to which binary, you write a query against a table name. The release announcement shows the canonical example: find processes whose on-disk image no longer exists, a frequent indicator of code running from binaries deleted after launch.

SELECT name, path, pid FROM processes WHERE on_disk = 0;

The on_disk column is an attribute computed at query time: osquery resolves the binary path tied to each PID and checks whether the file is still there. It is not a field the operating system exposes directly; it is the result of a correlation that, outside osquery, would mean joining the output of ps with a series of stat calls.

Architecture

The project ships two executables.

osqueryi is an interactive console: a REPL that opens an SQL session against the local host and lets you try queries, inspect the table schema and explore machine state without a fixed plan. You use it for off-the-cuff work, for analysis during an incident, for a check by hand.

osqueryd is a daemon that schedules queries at defined intervals and records their results. In monitoring the difference between successive runs matters more than the single snapshot: the daemon compares a query’s result against the previous one and logs the rows added and the rows removed. A query scheduled against listening_ports thus produces a stream of “port opened” / “port closed” events instead of the full list on every cycle.

The SQL engine is SQLite, used through its virtual-table mechanism. A virtual table holds no persistent data: each SELECT invokes C++ code that gathers state at query time and returns it as rows. osquery’s tables are therefore always consistent with the host’s current state, but the cost of a query is the cost of the underlying collection, not that of an indexed read.

Related queries are grouped into packs, configuration files that bind each query to a name, an interval and a target platform. A pack is where a collection of checks is distributed and versioned.

The crux

Joining tables separates osquery from a set of commands wrapped in SQL. The announcement gives the example of associating a listening port with its owning process.

SELECT DISTINCT process.name, listening.port, listening.address, process.pid
FROM processes AS process
JOIN listening_ports AS listening
ON process.pid = listening.pid;

Reaching the same result on the command line means correlating the output of netstat -lnp with that of ps by hand, aligning the PIDs one by one. The SQL join makes the correlation key (pid) explicit and leaves the cross-product to the engine. The same pattern holds for other relations within system state: a user and their active sessions, a file and the events that modified it, a process and the sockets it opened.

The limit of this model should be stated plainly: a JOIN over virtual tables may materialise both sides before correlating them, because the engine does not necessarily have indexes on tables generated on the fly. On a host with thousands of processes, a poorly written query pays in collection what a traditional database would pay in reads. Scheduled queries therefore have to be tuned against the interval and the cost of the queried table, not just against their logical correctness.

Implications

The uniform interface cuts the downstream normalisation work. A detection rule written as a query — processes with no on-disk binary, unsigned kernel modules, unexpected listening ports — stays the same SQL sentence whatever the underlying distribution, as long as the table exists on that platform. The data comes out already structured, as JSON in osqueryd’s logs, ready to be shipped to an aggregation system without a parser dedicated to each command.

Anyone working in security spends less on collection and more on phrasing the right questions. The query library becomes the artefact to maintain and version; the collection tool stays the same on every host.

Limits

At release osquery runs on Ubuntu, CentOS and Mac OS X. For Windows there is nothing yet: the announcement does not mention it, and a sizeable share of the tables depends on Linux- and OS X-specific mechanisms. Anyone with a heterogeneous fleet that includes Windows hosts cannot, today, use the same SQL sentence everywhere.

The tool, moreover, only observes. It collects and records; it does not block, does not quarantine, does not act on the process it flagged. Response stays delegated to whatever consumes the logs downstream. And the difference-between-runs model sees only what is present at the moment of the scheduled query: a process born and dead between two cycles leaves no trace in a periodic query against processes. Event tables soften this for some domains, but the principle holds — the sampling frequency decides what is visible.

It remains to be seen how table coverage will grow and how much the community outside Facebook will contribute schemas for platforms and domains not yet covered.

Cover image: A Facebook engineer holds up a server motherboard inside a data center, with rows of racks in the background — photo by Intel Free Press, CC BY 2.0 — https://commons.wikimedia.org/wiki/File:Facebook_Data_Center_Server_Board.jpg