Facebook open-sourced osquery in 2014 and it's still the least-appreciated ops tool of the last decade. The pitch: every piece of live OS state — running processes, listening sockets, kernel modules, cron jobs, logged-in users, USB devices, browser extensions, launchd/systemd units, the ARP table — is exposed as a virtual SQLite table. You SELECT from it. That's it. The same query works on Linux, macOS, Windows, and FreeBSD.

Interactive shell:

$ osqueryi
osquery> .tables
osquery> .schema processes

A malware-hunter's favorite one-liner — processes whose executable has been unlinked from disk (a classic persistence trick where a rootkit deletes its own binary after mmap'ing it):

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

Every listening socket, joined against its owning process, filtered to non-localhost. This replaces roughly forty characters of ss+awk+lsof glue that never quite works the same on two boxes:

SELECT p.pid, p.name, p.cmdline, l.address, l.port, l.protocol
FROM processes p
JOIN listening_ports l USING (pid)
WHERE l.address NOT IN ('127.0.0.1', '::1', '0.0.0.0');

Find every user with a real login shell, plus which ones actually logged in this month:

SELECT u.username, u.shell, l.time
FROM users u
LEFT JOIN last l ON l.username = u.username
WHERE u.shell NOT LIKE '%nologin%' AND u.shell != '/bin/false';

Failed systemd units, sorted by unit name, without ever touching systemctl --failed --no-legend | awk:

SELECT id, load_state, active_state, sub_state
FROM systemd_units WHERE active_state = 'failed';

SUID/SGID binaries not owned by root under /usr/local — the kind of audit that's a shell script from hell otherwise:

SELECT path, uid, gid, mode FROM file
WHERE path LIKE '/usr/local/%%' AND (mode LIKE '%4___' OR mode LIKE '%2___')
AND uid != 0;

Cross-referencing tables is where it stops being cute and starts being irreplaceable. "Which crontabs execute a binary that isn't owned by root?" is one query. "Which kernel modules were loaded after the last reboot from a path outside /lib/modules?" is one query. Try writing either in bash without introducing three bugs.

The daemon side. osqueryd runs scheduled queries on an interval, diffs the result set against last run, and emits JSON deltas to a log file or syslog. That means "new listening port appeared" becomes a two-line config entry with a query and an interval — no watchdog script, no state file, no cron. Feed the JSON into Splunk/Elastic/Loki and you have host-based intrusion detection built on SELECT statements. Facebook ships query packs (incident-response, vuln-management, hardware-monitoring) as starting points.

Why it beats the alternative. Every ops tribe eventually writes the same twelve shell scripts to inventory hosts — and every one of them parses ps differently, breaks when ss changes its column headers, and disagrees on macOS. osquery replaces all of them with one binary, one query language, one JSON schema. And because it's SQLite under the hood, you get subqueries, CTEs, and JSON functions for free.

Install: apt install osquery, brew install osquery, or grab the MSI for Windows. Run osqueryi and try .tables — the list alone will change how you think about your machine.