osquery: SQL-Powered Endpoint Visibility

Disclaimer: This article is provided strictly for educational purposes and authorized security testing. Only run these techniques against systems you own or have explicit written permission to assess. Unauthorized access to computer systems is illegal in virtually every jurisdiction and can carry severe penalties.

Introduction

osquery, originally built by Facebook and now a Linux Foundation project, exposes an operating system’s state as a set of relational tables that can be queried with standard SQL. Instead of learning a different tool and output format for processes, listening sockets, loaded kernel modules, installed packages, browser extensions, scheduled tasks, and USB devices, an analyst runs SELECT statements against tables like processes, listening_ports, autoexec, and crontab — the same query pattern works whether the host is Windows, macOS, or Linux, because osquery normalizes each OS’s native APIs into a common table schema.

This matters operationally because it collapses two previously separate skills — “how do I enumerate persistence on Windows” and “how do I enumerate persistence on Linux” — into one SQL query pattern, and because SQL is trivially composable: joins, subqueries, and aggregates let an analyst ask compound questions (“which processes are both listening on a network socket and were not installed by the package manager”) that would otherwise require stitching together several separate tools. Deployed as a fleet-wide daemon (osqueryd) with scheduled queries and a log pipeline, it becomes a continuous endpoint-visibility and detection layer rather than a one-off triage tool.

Attack Prerequisites

Framed as what’s needed to get value from osquery, since it is a defensive visibility tool rather than an attack technique:

• osquery installed on the target — the osqueryi interactive shell for ad hoc queries, or osqueryd running as a scheduled daemon for continuous collection; both ship the same table set.
• Sufficient host privilege for the tables queried — most tables (processes, listening_ports) work unprivileged, but some (kernel_modules, certain user_events/audit-backed tables) require root/Administrator to populate fully.
• A log destination for fleet use — TLS logger plugin to a log pipeline (Kolide Fleet, osquery’s own TLS server, Splunk/Elastic via a log forwarder) so scheduled-query results and osquery_events/FIM data actually reach the SIEM instead of staying local.

How It Works

Under the hood, each osquery table is backed by a small C++ “virtual table” module that, on query, calls the relevant native OS API (/proc on Linux, WMI/ETW/registry on Windows, various sysctl/IOKit calls on macOS) and materializes the results as rows for SQLite’s query planner — osquery embeds SQLite and implements every table as a SQLite virtual table extension. That means the full expressive power of SQL (WHERE, JOIN, GROUP BY, subqueries, LIKE/GLOB pattern matching) is available against live OS state, not just a static export.

Two categories of tables matter most for security use: point-in-time state tables (processes, listening_ports, users) that reflect what’s true right now, and event tables (process_events, socket_events, file_events) that, once the OS audit backend is enabled (Linux audit framework, macOS EndpointSecurity, Windows ETW), accumulate a rolling history so osquery can answer “what happened” retrospectively. osqueryd runs queries on a schedule, diffs results against the previous run, and ships only the deltas to the log pipeline — what makes continuous fleet monitoring affordable.

The autoexec table deserves particular mention for persistence hunting: on Windows it enumerates the union of nearly every documented auto-start/persistence mechanism — Run/RunOnce registry keys, Startup folder items, scheduled tasks, services, WMI event subscriptions, Winlogon Notify/Userinit/Shell entries — into one normalized table, so a single query against autoexec covers most of the persistence techniques an analyst would otherwise check one registry key at a time.

Practical Example / Configuration

Valid osquery SQL against real, commonly used tables. These run as-is in osqueryi on a matching platform (autoexec is Windows/macOS; crontab is Linux/macOS; processes and listening_ports are cross-platform):

-- Processes with a network listener, joined to show the binary path and
-- the port together -- classic "what's actually listening and why" query
SELECT p.pid, p.name, p.path, p.cmdline, lp.address, lp.port, lp.protocol
FROM listening_ports lp
JOIN processes p ON lp.pid = p.pid
WHERE lp.address != '127.0.0.1'
ORDER BY lp.port;

-- Processes with a network listener, joined to show the binary path and
-- the port together -- classic "what's actually listening and why" query
SELECT p.pid, p.name, p.path, p.cmdline, lp.address, lp.port, lp.protocol
FROM listening_ports lp
JOIN processes p ON lp.pid = p.pid
WHERE lp.address != '127.0.0.1'
ORDER BY lp.port;

-- Processes running from suspicious/writable locations, a common
-- staging-directory heuristic for dropped malware
SELECT pid, name, path, cmdline, parent, uid
FROM processes
WHERE path LIKE '/tmp/%'
   OR path LIKE '/dev/shm/%'
   OR path LIKE '%\\AppData\\Local\\Temp\\%';

-- Processes running from suspicious/writable locations, a common
-- staging-directory heuristic for dropped malware
SELECT pid, name, path, cmdline, parent, uid
FROM processes
WHERE path LIKE '/tmp/%'
   OR path LIKE '/dev/shm/%'
   OR path LIKE '%\\AppData\\Local\\Temp\\%';

-- Persistence sweep: every auto-start mechanism osquery knows about,
-- restricted to entries pointing outside Program Files/System32
SELECT name, path, source, s.username
FROM autoexec
LEFT JOIN users s ON s.uid = autoexec.uid
WHERE path NOT LIKE 'C:\\Program Files%'
  AND path NOT LIKE 'C:\\Windows\\System32%';

-- Persistence sweep: every auto-start mechanism osquery knows about,
-- restricted to entries pointing outside Program Files/System32
SELECT name, path, source, s.username
FROM autoexec
LEFT JOIN users s ON s.uid = autoexec.uid
WHERE path NOT LIKE 'C:\\Program Files%'
  AND path NOT LIKE 'C:\\Windows\\System32%';

-- Cron persistence: every user crontab entry, cross-referenced with
-- whether the invoked command path still exists on disk
SELECT c.path AS crontab_file, c.command, c.minute, c.hour, c.event
FROM crontab c
ORDER BY c.path;

-- Same idea for systemd timers/services (a more common modern persistence
-- vector than raw cron on many Linux endpoints)
SELECT name, source, status
FROM systemd_units
WHERE unit_type = 'service' AND (name LIKE '%.service')
ORDER BY name;

-- Cron persistence: every user crontab entry, cross-referenced with
-- whether the invoked command path still exists on disk
SELECT c.path AS crontab_file, c.command, c.minute, c.hour, c.event
FROM crontab c
ORDER BY c.path;

-- Same idea for systemd timers/services (a more common modern persistence
-- vector than raw cron on many Linux endpoints)
SELECT name, source, status
FROM systemd_units
WHERE unit_type = 'service' AND (name LIKE '%.service')
ORDER BY name;

-- Unsigned or unexpectedly-signed binaries currently running (macOS/Windows)
SELECT p.pid, p.name, p.path, hash.sha256, sig.signed, sig.identifier
FROM processes p
JOIN hash ON hash.path = p.path
LEFT JOIN signature sig ON sig.path = p.path
WHERE sig.signed = 0 OR sig.signed IS NULL;

-- Unsigned or unexpectedly-signed binaries currently running (macOS/Windows)
SELECT p.pid, p.name, p.path, hash.sha256, sig.signed, sig.identifier
FROM processes p
JOIN hash ON hash.path = p.path
LEFT JOIN signature sig ON sig.path = p.path
WHERE sig.signed = 0 OR sig.signed IS NULL;

Walkthrough / Exploitation

A realistic triage session starts interactive (osqueryi) to validate queries, then graduates the good ones into a scheduled pack for fleet-wide, continuous collection.

# 1. Interactive triage on one host -- validate the query shape first.
# on_disk = 0 flags a running process whose backing binary was deleted
# from disk -- a classic fileless-persistence tell
osqueryi --line "SELECT pid, name, path, cmdline FROM processes "
                "WHERE on_disk = 0;"

# 2. Ad hoc listening-port sweep, CSV output for a handful of hosts
osqueryi --csv "SELECT pid, name, address, port, protocol "
               "FROM listening_ports WHERE address NOT IN ('127.0.0.1','::1');"

# 1. Interactive triage on one host -- validate the query shape first.
# on_disk = 0 flags a running process whose backing binary was deleted
# from disk -- a classic fileless-persistence tell
osqueryi --line "SELECT pid, name, path, cmdline FROM processes "
                "WHERE on_disk = 0;"

# 2. Ad hoc listening-port sweep, CSV output for a handful of hosts
osqueryi --csv "SELECT pid, name, address, port, protocol "
               "FROM listening_ports WHERE address NOT IN ('127.0.0.1','::1');"

// 3. Promote the validated query into a scheduled pack (osquery.conf)
//    so osqueryd runs it fleet-wide and ships only the diffs to the logger
{
  "schedule": {
    "listening_ports_hunt": {
      "query": "SELECT p.name, p.path, lp.address, lp.port \
        FROM listening_ports lp JOIN processes p ON lp.pid = p.pid \
        WHERE lp.address NOT IN ('127.0.0.1','::1');",
      "interval": 3600,
      "description": "External-facing listeners, hourly"
    },
    "autoexec_persistence": {
      "query": "SELECT name, path, source FROM autoexec \
        WHERE path NOT LIKE 'C:\\Windows\\%';",
      "interval": 86400,
      "description": "Non-default persistence entries, daily"
    }
  }
}

// 3. Promote the validated query into a scheduled pack (osquery.conf)
//    so osqueryd runs it fleet-wide and ships only the diffs to the logger
{
  "schedule": {
    "listening_ports_hunt": {
      "query": "SELECT p.name, p.path, lp.address, lp.port \
        FROM listening_ports lp JOIN processes p ON lp.pid = p.pid \
        WHERE lp.address NOT IN ('127.0.0.1','::1');",
      "interval": 3600,
      "description": "External-facing listeners, hourly"
    },
    "autoexec_persistence": {
      "query": "SELECT name, path, source FROM autoexec \
        WHERE path NOT LIKE 'C:\\Windows\\%';",
      "interval": 86400,
      "description": "Non-default persistence entries, daily"
    }
  }
}

# 4. Confirm the config parses and the daemon picks it up before rollout
sudo osqueryd --config_path=/etc/osquery/osquery.conf \
  --config_check
sudo systemctl restart osqueryd
journalctl -u osqueryd -f

# 4. Confirm the config parses and the daemon picks it up before rollout
sudo osqueryd --config_path=/etc/osquery/osquery.conf \
  --config_check
sudo systemctl restart osqueryd
journalctl -u osqueryd -f

Note: SELECT * FROM processes and similarly unbounded queries on tables backed by expensive syscalls (hash, file, yara) can be slow or resource-heavy on a large host — always constrain with a WHERE clause (a specific path, a pid list) rather than scanning everything, especially for scheduled queries that will run on every endpoint in the fleet every interval.

Opsec: Some event tables (process_events, socket_events) are empty by default until the underlying OS audit subsystem is enabled — Linux audit framework rules, or --enable_bpf_events/EndpointSecurity extensions — so a SELECT * FROM process_events returning zero rows usually means the backend isn’t wired up, not that nothing happened. Verify with SELECT * FROM osquery_events; to see which publishers are active.

Detection and Defense

osquery is a visibility platform, so “defense” here means deploying it as part of the detection stack rather than defending against it:

• Schedule persistence and listener sweeps (autoexec, crontab/systemd_units, listening_ports joined to processes) at a cadence that balances freshness against load — hourly for network-facing state, daily for slower-changing persistence.
• Enable event tables (process_events, socket_events, file_events) via the platform audit backend for retrospective coverage that point-in-time state tables can’t provide.
• Diff-based alerting — alert on *new* rows appearing in a scheduled query’s result set rather than the full result each run, since a new autoexec entry or listener is the actionable signal.
• Baseline and hunt — join installed_applications/deb_packages/rpm_packages against hash to catch binaries outside a known-good manifest, and periodically hunt with yara/yara_events.
• Protect the osquery config and log pipeline — an attacker with local admin can tamper with osquery.conf/disable osqueryd, so treat agent integrity as its own security control.

Keep a small library of pre-validated SQL like the queries above under version control — writing correct joins under incident-response time pressure is worth having ready in advance.

Real-World Impact

osquery is widely deployed across large tech companies (originating at Facebook/Meta) and is the query engine behind commercial fleet-visibility products such as Kolide and Fleet, as well as being embedded in several EDR offerings. Its SQL model has become influential enough that other tools — notably Velociraptor’s VQL — cite the same “treat the OS as a queryable database” philosophy, and osquery packs are a standard component of continuous-monitoring architectures in regulated environments.

Conclusion

osquery’s contribution is turning dozens of platform-specific enumeration techniques into one consistent SQL surface, practical for both ad hoc incident hunting and continuous fleet monitoring. The highest-value pattern is the same in triage and production: join processes against listening_ports for network exposure, sweep autoexec/crontab/systemd_units for persistence, and promote anything useful from an interactive osqueryi session into a scheduled pack.

You Might Also Like

If you found this useful, these related deep-dives cover adjacent techniques and their defenses:

• Building a Detection Pipeline with the Elastic Stack
• DNS-Based Threat Hunting
• Threat Hunting: Building and Testing Hypotheses
• MITRE ATT&CK: Building a Detection Coverage Map