osquery for fleet visibility

System state as SQL you can query.

A running Linux machine already knows everything you would want to audit about it. Which programs are running. Which network ports are open and listening. Who logged in, when, and from where. What got installed overnight. Which files can quietly hand out root. The knowledge was never the hard part. Getting at it in a consistent way was. You stitch together ps, ss, dpkg -l, find, and crontab -l, parse the text with awk, then do the whole thing slightly differently on the next box. osquery replaces that. It reads the live state of the machine and hands it back as database tables you query with SQL (Structured Query Language, the standard way to ask a database questions).

Here is the shift in plain terms. The old way is walking a warehouse with a clipboard, reading labels box by box, hoping you copied them down right. osquery is the warehouse's inventory system: you ask 'show me every open box that arrived after midnight' and get one clean, structured answer. The boxes never move. The system reads their current state and returns it as rows. Ask the same question on a thousand machines and you get a thousand answers in the same shape, which is the whole point for a fleet.

A host as a live database

You talk to a single machine with osqueryi, an interactive shell. Underneath it is SQLite (a small, self-contained database engine), but the tables are unusual: they are virtual. Nothing is stored. Each time you run a SELECT, osquery goes out and collects the answer right then, from the kernel (the core of the operating system that talks to the hardware) and the files and system interfaces around it. A table like processes is less a saved spreadsheet and more a live camera feed. Every query is a fresh look.

Two dot-commands orient you fast. .tables lists what you can query (there are over 200 tables). .schema <name> shows a table's columns, so you know what you can ask for before you ask.

Questions worth asking

Now point it at the things a defender actually cares about. Three quick queries cover a lot of ground. First, root processes. Anything running as user ID 0 can do anything on the box, so you want to know exactly what does.

Second, exposed listeners. A port bound to 0.0.0.0 is open to every network the machine can reach. Compare that with the loopback address 127.0.0.1, which never leaves the box. An unexpected 0.0.0.0 listener is worth a hard look. The listening_ports table gives you the port but not the program, so join it to processes on the shared pid (process ID) to turn a bare number into a name.

Third, SUID binaries. Think of the SUID (Set User ID) bit as a signed permission slip stapled to a program: whoever runs it borrows the owner's authority for the length of the run, usually root's, no matter who they are. That is normal for a small set of system tools, and a classic hiding spot for a backdoor. List them and you have a baseline of what is legitimate.

This is the same enumeration you would do by hand, except every answer arrives in the same columns, on every host, ready to compare. Other tables carry the rest of the audit: deb_packages for what is installed, crontab for scheduled jobs, authorized_keys for who can SSH (Secure Shell, encrypted remote login) in, last for recent logins, shell_history for what commands ran and when.

Watching for change over time

A single snapshot tells you how the house looks right now. Security usually cares about a different question: what changed since yesterday? osquery answers that with scheduled queries, run by a background service called osqueryd (the osquery daemon). You give it a query and an interval, and by default it runs in differential mode. Instead of dumping the full result every time, it compares this run to the last one and reports only the rows that appeared or disappeared, each tagged with an action of added or removed. It is the difference between re-reading the whole ledger every hour and being handed a note that says 'one new line, here it is.'

{
  "options": {
    "logger_path": "/var/log/osquery",
    "schedule_splay_percent": 10
  },
  "schedule": {
    "suid_watch": {
      "query": "SELECT path, username, permissions FROM suid_bin;",
      "interval": 3600,
      "snapshot": false
    },
    "exposed_listeners": {
      "query": "SELECT lp.address, lp.port, p.name, p.path FROM listening_ports lp JOIN processes p ON lp.pid = p.pid WHERE lp.address NOT IN ('127.0.0.1', '::1');",
      "interval": 600,
      "snapshot": false
    }
  }
}

Two details in that config matter. snapshot: false is the default, and it is what you want here; snapshot: true would log the entire result set every interval instead of only the changes. And the very first time a differential query runs, there is no previous result to compare against, so osquery treats the current rows as the starting baseline and logs all of them once as added. Expect that initial burst on deploy, then quiet, then only real change after.

One caution before you schedule anything aggressively. Most tables are cheap to read. A few are not. hash, file with wildcard paths, and process_open_files can walk large parts of the disk. Put one of those on a ten-second interval across a fleet and you burn CPU (the processor) and disk I/O (input and output) on every host at once. Start heavy queries on long intervals, an hour or more, watch osquery's own resource use, and shorten only where you genuinely need the resolution.

Here is the payoff. An attacker who already has a foothold plants a setuid-root shell to keep their access: cp /bin/bash /var/tmp/.cache/sh then chmod u+s /var/tmp/.cache/sh. Your suid_watch query notices a SUID binary that was not in last hour's list. On the next interval it lands in the results log as one event. To confirm the change is live you do exactly what you would for any service: validate the config, restart the daemon, and watch the log fill in.

That is one line of JSON (JavaScript Object Notation, a plain-text data format), landing automatically, on every host, every hour, forever. Ship that log to wherever you keep alerts and the backdoor announces itself the same way everywhere. No custom parser, no per-host script that rots the moment someone renames a flag.

One question to the whole fleet

So far, one machine at a time. The last mode is why osquery runs in serious shops. Point the agents at a central server over TLS (Transport Layer Security, the encryption behind HTTPS) and you can ask a question of every host at once, with answers back in seconds. The common open-source server for this is Fleet. This is the live distributed query: it runs once, right now, across the whole estate, unlike the scheduled queries that tick along independently on each host.

This is your incident-response reach. A vulnerability drops, say the xz-utils backdoor (CVE-2024-3094, a supply-chain compromise planted in a compression library, affecting versions 5.6.0 and 5.6.1; CVE stands for Common Vulnerabilities and Exposures, the public catalog of known flaws). At 2 a.m. the only question that matters is 'are we running it, and where?' That is one query against the fleet, the same SBOM (software bill of materials, the ingredient list for your software) drill answered live instead of host by host.

One host out of 428, found in seconds, named. You can bundle the queries you care about into query packs (reusable sets, such as the incident-response pack) so a hunt is a saved question, not something you retype under pressure while an incident channel fills up.

Start today with three differential queries pointed at your log pipeline: suid_bin, listening_ports filtered to non-loopback addresses, and new accounts from the users table. That is a working change-detection tripwire on every host, catching planted backdoors, surprise listeners, and rogue users, before you have built a single dashboard.

Try this

Work through “One question to the whole fleet” yourself on a sandbox you can throw away, following the commands above in order. Then break one step deliberately and re-run, so you have seen the failure before it finds you.

Takeaway

The trap worth remembering here: osquery trusts the same kernel a rootkit can own. Check that on your own systems before you need to, because it is cheaper to find on a quiet afternoon than during an incident.

Related