osquery: When You Want Answers From Your Systems — in Plain SQL
Sometimes, logs are messy. Tools are inconsistent. You just want to ask your systems a straight question: “Which processes have listening ports?”, “What apps were just installed?”, or “Which users logged in last night?” That’s where osquery steps in.
It turns your operating system into something queryable — like a database. You run SQL against your endpoints. Suddenly, host monitoring feels a lot more natural.
What osquery Does Well
| Feature | Why It’s Useful |
| SQL-based interface | Ask structured questions using familiar syntax |
| Real-time and snapshot | Track live changes or get current state of the system |
| Tables for everything | Processes, users, network, kernel modules, packages, USB, and more |
| Scheduled queries | Run checks on interval, push logs to your SIEM |
| Cross-platform | Windows, macOS, Linux — same schema, same syntax |
| daemon + shell modes | Use interactively or run as a service |
| Fleet compatibility | Integrates with management layers like FleetDM |
| Lightweight | Minimal overhead, good for workstations and servers alike |
Where osquery Fits Best
– Security: Detect persistence mechanisms, suspicious binaries, privilege changes
– Compliance: Prove USB lockdown, audit software inventory, track config drift
– Ops: Check if required software is deployed, look for stale cron jobs
– IR/DFIR: Pull host data fast during investigation — without deploying new tools
– Monitoring: Feed into Prometheus/Grafana pipelines or security dashboards
You can build alerting on top, but osquery itself is about visibility — exposing host data in structured form.
Installation Quickstart (Ubuntu Example)
sudo apt install gnupg lsb-release
sudo mkdir -p /etc/apt/keyrings
curl -L https://osquery.io/gpg | gpg –dearmor | sudo tee /etc/apt/keyrings/osquery.gpg > /dev/null
echo “deb [signed-by=/etc/apt/keyrings/osquery.gpg] https://pkg.osquery.io/deb deb main” | \
sudo tee /etc/apt/sources.list.d/osquery.list
sudo apt update
sudo apt install osquery
Launch interactive shell:
osqueryi
Example query:
SELECT name, path FROM processes WHERE pid > 1000;
To run as a daemon:
sudo systemctl start osqueryd
You can then schedule queries via config files or use Fleet to manage them centrally.
Benefits and Friction Points
Strengths:
– Familiar SQL interface lowers entry barrier
– Huge variety of data exposed — and consistent across platforms
– Easy to script, integrate, or plug into bigger observability stacks
– Works well at scale when managed via something like Fleet or Kolide
– Open-source, actively maintained
Challenges:
– Query writing takes precision — bad queries = missed data or heavy load
– Data is only as fresh as your query schedule
– Learning curve exists for organizing queries at scale
– Needs hardening — access to osqueryi gives a lot of power
– No native visualization — best used alongside external dashboards
Final Thoughts
osquery isn’t a monitoring tool. It’s a microscope. You point it at the system and ask exactly what you want to know — in real time or at a regular cadence. For teams who want more than just metrics, who need forensic-level detail or fine-grained visibility across fleets — it’s one of the cleanest ways to see what your systems are really doing.
