osquery — SQL-Powered Operating System Instrumentation

Introduction

osquery, originally created at Facebook, turns your operating system into a relational database. Instead of learning dozens of OS-specific commands, you write standard SQL to ask questions about system state—processes, users, network connections, file integrity, and more.

What osquery Does

• Exposes OS internals (processes, files, users, hardware) as virtual SQL tables
• Provides an interactive shell (osqueryi) for ad-hoc investigation
• Runs a daemon (osqueryd) that executes scheduled queries and logs results
• Supports macOS, Linux, Windows, and FreeBSD with platform-specific tables
• Enables fleet-wide security monitoring and compliance auditing

Architecture Overview

osquery embeds a SQLite-based virtual table engine. Each "table" is backed by a C++ plugin that reads live OS data on demand. The daemon reads a configuration file defining scheduled queries, runs them at set intervals, writes differential results to structured JSON logs, and optionally forwards them to a TLS server for centralized collection.

Self-Hosting & Configuration

• Configuration lives in /etc/osquery/osquery.conf (JSON format)
• Flag files at /etc/osquery/osquery.flags set daemon behavior
• Query packs group related scheduled queries (e.g., incident-response, compliance)
• Logging outputs include filesystem, TLS endpoint, AWS Kinesis, and Kafka
• Fleet management options include osquery Fleet Manager and Kolide

Key Features

• Over 200 built-in virtual tables across supported platforms
• Differential logging captures only changed rows between query runs
• File integrity monitoring (FIM) via the file_events table
• Extensions SDK allows custom tables in C++, Python, or Go
• Watchdog process guards against runaway queries consuming too many resources

Comparison with Similar Tools

• Wazuh — full SIEM with HIDS; osquery focuses on SQL-based host querying
• OSSEC — agent-based intrusion detection; less flexible ad-hoc investigation
• Sysdig — container and system call visibility; osquery covers broader OS tables
• Auditd — Linux audit framework; osquery wraps audit data into queryable tables
• Velociraptor — DFIR-focused agent with its own query language (VQL)

FAQ

Q: Does osquery modify the system it runs on? A: No. osquery is read-only by design. It queries OS state without altering files, processes, or configurations.

Q: How much CPU and memory does the daemon use? A: The watchdog enforces configurable limits (default 12% CPU, 200 MB RAM). Queries exceeding limits are killed automatically.

Q: Can I query Docker containers? A: Yes. The docker_containers, docker_images, and related tables expose container metadata on hosts running Docker.

Q: What log format does osqueryd produce? A: JSON by default, with each scheduled query result stored as a structured event including added/removed rows.

Sources

• https://github.com/osquery/osquery
• https://osquery.readthedocs.io/