osquery for fleet visibility

Query the host as tables, schedule differentials.

osquery presents a running host as a set of database tables (processes, listening sockets, users, SUID files, systemd units and about 150 more) that you query with SQL. The value is consistency: the same question returns the same columns on every host, so you can compare machines, schedule the question and log only what changed. In this lesson you install osquery 5.23.1 from the vendor's repository, query the tables that matter to a defender, schedule a differential query that catches a planted SUID file, and learn where osquery's view stops. The lab host is Ubuntu 26.04; osquery publishes the same packages for RHEL.

Install from the vendor's repository

osquery is not in the Ubuntu or RHEL archives. The vendor publishes an apt repository at pkg.osquery.io signed with its own key, and the safe pattern is to store that key in a keyring file and name it with signed-by on the repository line, so it can vouch for that repository and nothing else (the pattern from "Packages and updates" in Linux essentials). First fetch the key and check its fingerprint.

The fingerprint 1484120A C4E9F8A1 A577AEEE 97A80C63 C9D8B80B is the one osquery publishes on its download page. Compare it before you trust the file; a key fetched over HTTPS from the right host can still be the wrong key. The vendor's line hard-codes arch=amd64; the lab host is arm64, so the architecture comes from dpkg.

apt followed the repository's redirect to pkg.osquerypackages.com, and the only new package is osquery 5.23.1 for arm64 (the full output also shows needrestart's usual report). It installs under /opt/osquery and puts osqueryi (the interactive shell) and osqueryd (the daemon) on the path.

The daemon is installed but neither enabled nor running, so nothing happens until you configure it. osqueryi needs no daemon at all.

The host as tables

osquery tables are virtual: nothing is stored, and each query collects its answer at that moment from /proc, the kernel and system files. .tables lists them and .schema shows a table's columns. Run queries with sudo when you want the whole host; as an ordinary user some tables see only what that user may read.

Joins are where SQL pays off. This query lists root processes running from /usr/local (outside the package manager's territory) with the name of their parent.

The one hit is lima-guestagent, the lab VM tool's agent, started by systemd. On a production server the same query surfaces anything someone installed by hand and runs as root, which is where you want to look first. The next query joins listening sockets to processes and keeps only addresses reachable from outside the host.

Protocol 6 is TCP and 17 is UDP. sshd on port 22 (IPv4 0.0.0.0 and IPv6 ::) and the DHCP client on port 68 are what a default Ubuntu server exposes. The multicast DNS listener on 5353 comes from the VM tool's resolver setting, not from Ubuntu, which is exactly the kind of line this query exists to make you explain. Two more checks: accounts with UID 0, and the SUID and SGID programs.

Only root has UID 0; a second UID 0 account is a classic backdoor. suid_bin reports S for set-user-ID and G for set-group-ID programs, the owner and group whose identity they lend. The sudo, sudo-rs and su-rs names are symlinks into /usr/lib/cargo/bin (sudo-rs), and the table reports the bits of the file they point to. There is a trap in the row count.

54 rows, but only 27 under /usr. Ubuntu 26.04 is usr-merged: /bin and /sbin are symlinks to /usr/bin and /usr/sbin, and the table walks both, so every file appears twice. Filter on /usr/ paths, or you will count and alert on everything twice. Last, local systemd units, a favourite persistence spot.

Units under /etc/systemd/system were written locally rather than shipped by a package, so each one should have an owner you can name. Here it is the VM tool's agent again.

Watching for change: a scheduled differential query

osqueryd runs queries on a schedule and writes results to a log. By default a scheduled query is differential: each run is compared with the previous one and only rows that appeared (added) or disappeared (removed) are logged. Setting "snapshot": true logs the whole result every time instead. This configuration watches the SUID set every 10 seconds; that interval and schedule_splay_percent: 0 (no random jitter) are for the lab, where an hour and the default jitter suit a fleet.

{
  "options": {
    "schedule_splay_percent": 0
  },
  "schedule": {
    "suid_watch": {
      "query": "SELECT path, username, permissions FROM suid_bin;",
      "interval": 10
    }
  }
}

osqueryctl config-check validates the file before the daemon reads it; the database.cpp line is osquery creating its local state database on first use. Once the first run has finished, count what it logged.

All 54 rows arrived as added. The first run of a differential query has nothing to compare against, so the whole current state is logged once; expect that burst from every host when you roll a query out, and do not page on it. Now plant a harmless test file, a SUID-root copy of true, in a directory the table watches.

One line of JSON, written without anyone asking: the query name, "action":"added" and the columns of the new row. Remove the file and the next run reports that too.

The results log, /var/log/osquery/osqueryd.results.log with the default filesystem logger, is the thing to ship off the host with the rest of your telemetry (linux-det/auditpipe). Across a fleet, osquery agents are usually pointed at a management server over TLS, which hands them their configuration, collects results and runs live queries on every host at once; the TLS remote API is documented by osquery, and several open-source and commercial servers implement it.

A scheduled query can also go quiet with nothing in the results log to say so. osqueryd runs as two processes, a watchdog and the worker that executes the schedule. Its own health lives in tables such as osquery_schedule, which only the daemon fills, so osqueryi --connect queries the running daemon through its extension socket.

The first osqueryd (100229), started by systemd with the flag file, is the watchdog; the second is the worker it spawned. suid_watch has run 3 times and is not denylisted. watchdog_level 0 is the normal set of limits, with the memory and CPU overrides unset (0), enforced from 60 seconds after the worker starts (watchdog_delay). The watchdog kills a worker that exceeds its CPU or memory limits and starts a new one, and osquery's debugging guide says the query that was running is then denylisted for 24 hours: that detection simply stops, and denylisted turns 1. Differential state lives in osquery's local database (/var/osquery/osquery.db), so a reinstall or a lost database replays the full added burst. And an agent that root stops just stops reporting. So collect osquery_schedule from every host, and alert centrally when a host's results or check-ins stop arriving.

Where osquery's view stops

suid_bin does not scan the whole filesystem. Its source lists fixed directories: /bin, /sbin, /usr/bin, /usr/sbin, /usr/local/bin, /usr/local/sbin and /tmp. Put the same test file in /var/tmp and compare with the generic file table, which reads exactly the directory you name.

suid_bin does not see /var/tmp/oq-probe at all, while file finds it with mode 4755. find shows four SUID files under /usr/lib that suid_bin never scans directly: ssh-keysign and the D-Bus launch helper are simply absent from the table, and the two sudo-rs binaries appear only through their /usr/bin symlinks. Know each table's scope before you trust a clean result; pair suid_bin with a file query on writable directories, or with the baseline scan from linux-det/pedetect.

The other limits follow from how osquery works. Scheduled queries poll, so something that starts and exits between two runs never appears. osquery can subscribe to process events instead, through one of two publishers, both off by default (the configuration above uses neither). The audit-based one needs the kernel's audit connection, and osquery's documentation says auditd must not run alongside it. That matters most on RHEL, where auditd runs out of the box (linux-det/auditpipe), and on Ubuntu once you install it. The eBPF publisher (the bpf_process_events table) avoids that conflict. Some tables, such as hash or file with wide patterns, read a lot of disk; give them long intervals. And every answer comes through the kernel of the host being asked.

The lab stopped the daemon here and then purged the package, the repository line, the keyring and /var/log/osquery, so no agent keeps running.

Try this

With osquery installed on a lab host, run sudo osqueryi "SELECT count(*) FROM suid_bin WHERE path LIKE '/usr/%';" and note the number (27 on a default Ubuntu 26.04 lab). Create /etc/osquery/osquery.conf from this lesson, start osqueryd, and wait until /var/log/osquery/osqueryd.results.log exists; count its added rows with the jq pipeline above and confirm it is twice your number. Create sudo install -m 4755 -o root /usr/bin/true /usr/local/bin/probe, wait ten seconds and find one new added row for it; remove the file and find the removed row. Then repeat the experiment in /var/tmp and confirm nothing is logged. Stop the daemon when you are done.

Takeaway

Treat osquery results as a baseline plus a change log: expect the first differential run to report everything, filter out the usr-merge duplicates, and alert on later added rows. Check each table's scope before trusting a clean answer, and ship the results log off the host.

Related