osquery Guide: Endpoint Visibility With SQL Queries

Most security teams cannot answer simple questions about their own hosts quickly, such as which machines have a new SSH key in an authorized_keys file or which laptops carry a cloud credential file. osquery closes that gap by exposing the operating system as a set of SQL tables, so the same query can run on one laptop or across tens of thousands of servers. This guide covers how osquery works, the defender queries that pay off first, safe deployment and performance controls, how it complements EDR and SIEM, and where it helps find machine credentials on hosts. It is written for detection engineers, endpoint and platform teams, and incident responders. It describes defensive use only, based on the project's own documentation.

Key takeaways

SQL over operating system state. osquery presents processes, listening ports, packages, startup items, browser extensions and SSH keys as relational tables you can join and filter.
Two binaries, two jobs. osqueryi is the interactive shell for exploration and triage; osqueryd is the daemon that runs a scheduled query set and logs only what changed.
The watchdog is a safety feature, not an option. It limits CPU and memory per worker and denylists queries that exceed limits, so leave it on and profile queries before rollout.
Visibility, not prevention. osquery does not block anything. Its value comes from the questions you ask, how often you ask them and where the results go.
A strong NHI hunting tool. Queries over authorized_keys, known_hosts and credential file paths find SSH keys, tokens and cloud credentials sitting on hosts that no vault knows about.

What the tool does

osquery describes itself as an operating system instrumentation framework for Windows, macOS and Linux that exposes the system as a relational database. Tables represent concepts such as running processes, loaded kernel modules, open network connections, browser plugins and file hashes. Tables are implemented through a plugin and extensions API, and the published schema lists which tables exist on which platform.

osquery was created at Facebook in 2014. In June 2019 the Linux Foundation announced its intent to form an osquery Foundation to support the community, and development continues in the open on GitHub. The code is dual licensed: the repository's licence file states Apache-2.0 OR GPL-2.0-only, and users may choose either licence. Check this against your Open Source Licence Compliance Guide process if you redistribute or modify it.

Component	What it is	Typical use
osqueryi	Interactive SQL shell, no daemon needed	Writing and testing queries, live triage on a single host
osqueryd	Daemon that runs a schedule, caches results in RocksDB and logs differences	Continuous fleet monitoring and inventory
Configuration	JSON with `options`, `schedule`, `packs`, `file_paths`, `decorators`	Defining what runs, how often and on which platforms
Query packs	Named groups of scheduled queries, optionally gated by discovery queries	Shipping a themed set (for example incident response or compliance) to matching hosts only
Event tables	Tables fed by OS eventing APIs (file_events, process_file_events, ntfs_journal_events)	File integrity monitoring and change capture between query runs
Logger plugins	`filesystem` (default), `tls`, `syslog` and AWS options	Shipping results to a SIEM or data lake
Remote (TLS) API	Enroll, config, logger and distributed read and write endpoints	Central management by a fleet manager

Scheduled queries: differential versus snapshot

Each entry under schedule has a query and an interval in seconds (maximum 604,800, one week), plus optional platform, version, shard (run on a percentage of hosts), snapshot, removed and denylist keys. By default results are differential: the first run logs every row as added, later runs log only rows added or removed since the last run. Setting snapshot to true logs the full result set every interval to a separate snapshot log, which the documentation warns can generate a large amount of output. The interval counts daemon uptime, not wall clock time, so it pauses while a laptop sleeps.

Packs named under packs appear in logs as pack_<packname>_<queryname>. A pack with discovery queries runs only when every discovery query returns at least one row, re-evaluated every 60 minutes by default (pack_refresh_interval). Decorator queries (load, always, interval) add columns such as a hostname or serial number to every result line.

Defender use cases

The tables below exist in the osquery schema; the queries are described in general terms so you can adapt them to your own baseline. Platform suffixes in the schema matter: authorized_keys, known_hosts, crontab and shell_history are POSIX tables, deb_packages and rpm_packages are Linux, programs is Windows.

Question	Tables	What to look for	ATT&CK technique
What is listening on the network?	`listening_ports` joined to `processes` on pid	Services bound to 0.0.0.0 that are not in the host's role baseline	Context for most techniques
Which processes run without a file on disk?	`processes` (`on_disk = 0`)	Binaries deleted after launch, a classic sign of tampering	Context for defence evasion
What starts automatically?	`startup_items`, `launchd`, `crontab`, Windows services	New autostart entries, unusual paths, entries owned by unexpected users	T1547 Boot or Logon Autostart Execution, T1053.003 Cron, T1543 Create or Modify System Process
Who can log in with an SSH key?	`authorized_keys` joined to `users`	Keys added outside your provisioning process, keys with no comment, keys on service accounts	T1098.004 SSH Authorized Keys
Which browser extensions are installed?	`chrome_extensions` (covers Chromium browsers including Edge and Brave), `firefox_addons`	Extensions not from the web store, broad permissions, identifiers not on an allow list	T1176.001 Browser Extensions
What software and versions are present?	`deb_packages`, `rpm_packages`, `programs`	Vulnerable versions, unapproved tools, drift between hosts with the same role	Supports vulnerability management
Where are connections going?	`process_open_sockets`	Unexpected remote ports or processes talking out that should not	Context for command and control

Inventory: package and program tables feed asset and software inventory and give a second opinion against your scanner. See the Vulnerability Management Guide.
Threat hunting: a distributed (live) query lets an analyst ask one question of the whole fleet and get answers within minutes. See the Threat Hunting Guide.
Incident response: osqueryi on an affected host gives structured, repeatable triage output; for deep artefact collection pair it with a dedicated DFIR tool. See the Velociraptor Guide.
File integrity monitoring: with events enabled, file_events (inotify on Linux, FSEvents on macOS) and ntfs_journal_events on Windows record changes under file_paths categories, hashing changed files with MD5, SHA-1 and SHA-256 where possible.

Where identity fits: finding machine credentials on hosts

Hosts accumulate non-human identity credentials: SSH private keys, cloud CLI credential files, package registry tokens and kubeconfig files. osquery can locate them without reading their contents, which keeps the hunt safe and the logs clean.

SSH trust paths: authorized_keys shows who can log in to a host; known_hosts shows which hosts an account has connected to, a useful lateral movement map. See the SSH Key Management Guide.
Credential files: query the file table for paths and metadata (owner, mode, modification time) of well known credential locations such as ~/.aws/credentials, ~/.npmrc, ~/.kube/config and ~/.ssh/id_*. Select only path and metadata columns, never file contents.
Why it matters: the Shai-Hulud npm worm first wave read npm tokens from ~/.npmrc and ran a secret scanner over developer filesystems. A fleet query for those files tells you which hosts held credentials that need rotating. These map to T1552.001 Credentials In Files and T1552.004 Private Keys.
Next step: move what you find into a vault and short lived credentials. See the Secrets Management Guide.

Deployment and safe use

Write and profile queries first. Develop in osqueryi, then run the project's profiler (./tools/analysis/profile.py --config osquery.conf --count 1 --rounds 4) to rate CPU and memory impact. The documentation says high impact queries should be avoided or run less often.
Keep the watchdog on. Defaults are --watchdog_level=0 (normal: 200 MB memory, 10% CPU), with 1 as restrictive (100 MB, 5%) and -1 disabled. --watchdog_delay (default 60 seconds) delays enforcement at start-up, and queries breaching limits are denylisted unless denylist is false. Use the restrictive level on latency sensitive servers.
Pilot on a shard. Use the shard key or a small host group, watch status logs (osqueryd.INFO, WARNING, ERROR) for denylisting, then widen.
Set a stable host identity. --host_identifier defaults to hostname; use uuid where hostnames repeat, such as cloned VMs.
Enable events deliberately. File events are off by default. Turn on --disable_events=false and the specific publisher (--enable_file_events=true, --disable_audit=false, --enable_ntfs_event_publisher=true) only for the paths you need, using exclude_paths for noisy directories.
Centralise with a fleet manager. The TLS remote API uses enroll, config, logger and distributed endpoints. Distributed queries are off by default (--disable_distributed=true) and need --distributed_plugin=tls. The osquery README lists managers such as Fleet, Kolide, OSCTRL and Zentral and says the project does not endorse, recommend or test them.
Roll back cleanly. Keep the previous configuration in version control; reverting the config served by the manager, or removing a pack, stops the queries on the next config refresh.

Data handling

Treat the enrollment secret as a credential: provide it through --enroll_secret_path or --enroll_secret_env, restrict file permissions, and rotate it. Each node then gets its own node_key, and the server can force re-enrolment with node_invalid.
Control live query access: distributed queries are remote read access to every host, so restrict who can run them in the fleet manager and log every query run.
Avoid sensitive columns: tables such as shell_history can capture secrets typed on the command line; schedule them only with a clear purpose and retention limit.
Protect local logs: results go to /var/log/osquery/osqueryd.results.log by default (Windows: C:\Program Files\osquery\log), so ship them with TLS and keep local copies owner readable only.

Detection and hardening value

osquery sits between configuration management and EDR. It does not replace an EDR agent's kernel telemetry or response actions, but it answers state questions that EDR consoles often make awkward, and its results are plain JSON that any SIEM can ingest. See the SIEM Guide and the EDR Guide.

Framework	Reference	How osquery supports it
NIST CSF 2.0	DE.CM-09	Monitoring computing hardware, software and runtime environments for adverse events
NIST CSF 2.0	ID.AM-01, PR.PS-01	Hardware inventory; evidence that configuration baselines are applied
NIST SP 800-53 Rev. 5	CM-8, CM-6	System component inventory; checking configuration settings
NIST SP 800-53 Rev. 5	SI-4, SI-7, AU-12	System monitoring; integrity checking through file events and hashes; audit record generation
CIS Controls v8.1	Controls 1, 2 and 8	Enterprise asset and software inventory; audit log management

Metrics that show it is working

Coverage: percentage of managed endpoints that checked in within the last 24 hours, by platform.
Query health: number of scheduled queries denylisted by the watchdog per week (target near zero).
Time to answer: minutes from a new indicator to a fleet-wide distributed query result.
Credential exposure: count of hosts with unmanaged credential files or unexpected authorized keys, trending down.
Drift: number of new autostart entries or browser extensions not on the allow list, per week.

Common failure patterns

Snapshot everything: marking large tables as snapshot at short intervals floods the SIEM and hides the signal. Use differential results and reserve snapshots for small inventories.
Disabling the watchdog to stop denylisting: this trades a visibility gap for production performance incidents. Fix or slow down the query instead.
No baseline: listening port and startup item results are noise until you define what is normal per server role.
Unrestricted live queries: anyone with fleet manager access can read state from every host. Apply role based access and audit logging.
Collecting secrets by accident: selecting file contents or shell history copies credentials into logs, which then need the same protection as a vault.
Finding credentials and stopping there: a list of hosts with ~/.aws/credentials only helps if owners rotate and remove them, as the Shai-Hulud case shows.

Alternatives

Tool category	Strength compared with osquery	When to choose it
EDR / XDR agent	Kernel telemetry, behavioural detection, containment	You need prevention and response; keep osquery for flexible state queries
Wazuh agent	Built-in log analysis, FIM, configuration assessment and alerting rules	You want an integrated open source monitoring stack. See the Wazuh Guide.
Velociraptor	Deep forensic artefact collection and hunting	Incident response at scale; often deployed alongside osquery
Native logging (auditd, Sysmon)	Event streams with no extra agent schema	You need process and syscall history rather than state. See the Linux Logging and auditd for Defenders Guide and the Sysmon Guide.

Small versus large teams: a small team can start with osqueryd, a filesystem config and a log shipper, and use osqueryi for triage. A large estate needs a fleet manager, query packs per role, sharded rollouts and an owner for query review.

Practitioner checklist

Define 10 to 20 core questions (ports, autostarts, SSH keys, extensions, packages) and write them in osqueryi first.
Profile every scheduled query before deployment and record its interval rationale.
Keep the watchdog enabled, using the restrictive level on latency sensitive servers.
Pilot on a shard and watch status logs for denylisted queries before widening.
Store the enrollment secret in a protected file or environment variable and rotate it.
Restrict and audit distributed query permissions in the fleet manager.
Add a credential file and authorized_keys hunt pack that collects metadata only.
Route results to the SIEM with host identifiers and decorators for context.
Review the query set quarterly against new ATT&CK techniques and retired systems.

Standards and references

Related NHI Mgmt Group resources: Endpoint Security Guide · Detection Engineering Guide · Windows Internals for Defenders Guide · Security Onion Guide

osquery Guide: Endpoint Visibility and Fleet Queries With SQL