osquery: endpoint telemetry via SQL

CyberScan

Vulnerability assessment and automated pentest. Continuous asset discovery, AI risk prioritisation, integrated NIS2 compliance manager.

Discover CyberScan →

Cybersecurity

CISO-as-a-service consulting: posture, remediation roadmap, ongoing support.

Discover →

“The operating system is a database”

In 2014, Mike Arpaia and Teddy Reed, in Facebook’s Security Infrastructure team, release osquery: a tool that exposes the operating system state as a SQL database. Virtual tables represent processes, users, network connections, file system, kernel configuration, installed packages.

A query like:

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

returns running processes whose on-disk image no longer exists (a classic indicator of in-memory malware).

The open-source release happens on 29 October 2014 under Apache 2.0 licence (originally BSD-3; the project later moved to Apache 2.0). osquery supports Linux, macOS, Windows, FreeBSD.

Architecture

Two main components:

• osqueryi — interactive REPL for ad hoc queries
• osqueryd — daemon scheduling queries on defined packs (periodic queries, e.g. every 60s) and emitting JSON log results

Logs can be sent to local file, syslog, TLS endpoint, Kafka, Kinesis, AWS Firehose via logger plugins.

Security-relevant tables

• processes, process_events — process auditing
• users, logged_in_users, last — user and session management
• file_events, yara_events — integrity monitoring with integrated YARA
• listening_ports, process_open_sockets — network posture
• crontab, launchd, systemd_units — persistence
• kernel_modules, kernel_extensions — rootkit detection
• osquery_info, osquery_extensions — self-monitoring

Query packs and ATT&CK

Query packs are collections of queries targeted at a purpose (CIS compliance, ATT&CK detection, vulnerability management). Reference public packs: Facebook Threat Hunting, Palantir osquery-configuration, BSidesLV pack.

Fleet management

For fleets beyond a handful of machines, osquery needs a controller:

• Kolide Fleet (later Fleet by Fleet DM) — Open Source, dashboard for querying thousands of endpoints
• osctrl, Doorman, Zentral — open alternatives
• Uptycs, Kolide K2 — commercial, osquery-based

Integration with SIEM and SOAR

osquery integrates naturally with SIEMs (Elastic Agent includes an osquery beats, Splunk has TA-osquery, Sentinel receives logs via TLS logger) and with SOAR playbooks (hunt an IoC across the whole fleet in seconds).

In the Italian context

osquery is used in:

• Companies with heterogeneous IT — Linux/macOS/Windows unified via SQL
• DevSecOps — compliance automation, drift detection
• University Blue teams — threat hunting teaching
• MSSPs — EDR-lite on endpoints without budget for commercial agents

The simplicity of the SQL model and cross-platform support make it a promising tool in Open Source endpoint visibility strategies.

References: osquery, Open Source release 29 October 2014 from Facebook (Mike Arpaia, Teddy Reed). Apache 2.0 licence. Supports Linux, macOS, Windows, FreeBSD. Fleet (Fleet DM) as open controller. Site: https://osquery.io.