Writing Osquery Queries: A Practical SQL Guide
The SQLite dialect, the joins that matter, required WHERE constraints, differential scheduling, and the cost model that keeps queries from hurting hosts.
Bad osquery queries do not fail loudly. They return plausible rows, eat CPU, and quietly answer a different question than the one you asked.
Writing osquery queries is not hard because the SQL is hard. The dialect is SQLite, which most engineers can read on sight. It is hard because the tables are live operating system state, the cost model is nothing like a database server, and a query that works in the shell can misbehave when scheduled across 2,000 hosts. This guide covers the dialect, the joins that matter, the constraints that keep queries cheap, and the scheduling decisions that separate useful telemetry from log spam. For the table reference behind these examples, see our guide to the most useful osquery tables.
The lifecycle of a good osquery query
Every query that earns a slot in the schedule moves through the same four gates.
What SQL dialect do osquery queries use?
Osquery embeds SQLite as its query engine, so the language is a superset of SQLite SQL, documented in the official SQL introduction. Everything is a SELECT. There is no INSERT or UPDATE because tables are virtual views over system APIs, not stored data. You get the full SQLite expression library: string functions, date math, CASE expressions, subqueries, aggregation, and JSON functions for the tables that return JSON columns.
The mental shift that matters: in a database, the expensive part is reading disk. In osquery, the expensive part is generating rows. Selecting from the processes table walks the process list at query time. Selecting from a file hashing table hashes files at query time. SQL that looks cheap on paper can be a denial of service against your own endpoint if you let it enumerate the world.
How do you write your first osquery queries?
Start in osqueryi, the interactive shell. It needs no configuration and no daemon. Three meta commands do most of the work: .tables lists what exists on this host, .schema users shows columns and types, and .mode line makes wide rows readable.
The second query is a real detection with one condition: it finds processes whose binary was deleted after launch, which legitimate software almost never does and loaders do constantly. Good osquery queries tend to look like this. Short, one sharp predicate, obvious meaning at a glance.
How do joins work across tables?
Endpoint state is relational, and pid is the foreign key of the operating system. The join that answers "what is exposed to the network, and what program is behind it" is four lines:
Parent and child processes join the table to itself, which is how you catch a web server spawning a shell:
A web server has no business launching an interactive shell, so any row from that query deserves a human within the hour. With exploitation now the top initial access vector at 31 percent of breaches in the Verizon 2026 DBIR, the post exploitation moment that query captures is exactly the one most fleets miss.
Why do some queries need WHERE clauses to run at all?
Several tables refuse unbounded scans by design. The file and hash tables require a path constraint, because "hash every file on the filesystem" is not a query anyone should run. The constraint syntax uses LIKE with % for a single level and %% for recursion:
Even where constraints are optional, they are the difference between a cheap query and an expensive one. Osquery pushes WHERE clauses down into the table implementation when it can, so filtering on an indexed column like pid or path means the agent generates fewer rows in the first place, not just returns fewer. Constrain early, constrain always.
How do scheduled osquery queries behave differently?
The daemon runs your queries on an interval and, by default, logs only the diff: rows added since last run, rows removed. This differential model is the whole trick of osquery at scale. A processes query on a stable server logs almost nothing day to day, and the day it logs something is the day worth reading. Set "snapshot": true on a query when you want the full result every time, which suits inventory reporting but multiplies volume.
Do the math before choosing intervals. A snapshot query returning 300 process rows every 60 seconds on 2,000 hosts is 864 million rows a day into your pipeline. The same query, differential, at 300 seconds, on a typical fleet emits a few thousand rows a day. Same visibility, five orders of magnitude apart. Interval should match how fast the answer changes: persistence mechanisms rarely need better than 10 minutes, inventory rarely better than a day.
How do you filter on time in osquery queries?
Timestamps in osquery tables are Unix epochs, and SQLite date functions convert freely between epochs and human dates. Two patterns cover most needs. Relative windows use strftime against now:
The second query reads as a sentence: files under /etc modified in the last week. Wrapping raw epochs in datetime() for output columns is a small courtesy that saves every downstream reader a conversion, and CASE expressions do the same job for enum columns, turning status 2 into the word the analyst actually needs.
Time filters also interact with the differential model in a way that trips people up. A WHERE clause on a moving time window means rows age out of the result set, and the daemon dutifully logs each one as removed. If your pipeline treats removals as events, a rolling window query generates a steady stream of meaningless ones. For "new thing appeared" detections, prefer letting the differential engine detect newness on a stable query, and save explicit time windows for interactive investigation.
What do production osquery queries look like?
You do not have to invent a query library from scratch. Palantir's published osquery configuration is a working reference from a real security team, with packs for compliance, application security, and registry monitoring, and it encodes operational lessons in small details. Many of their queries run at 28800 second intervals, sized to "once per working day of host uptime" rather than a round number. Query packs let you version and ship these as units.
Borrowed queries still need local judgment. A rule that flags every listening port is noise on a developer laptop and signal on a database server. The query is the easy 20 percent. Deciding what a result means on this host, in this environment, is the 80 percent that burns analyst time, the same economics we walk through in AI alert triage.
How do you keep osquery queries from hurting the host?
The daemon ships with a watchdog that restarts the worker process when a query exceeds roughly 10 percent sustained CPU for 12 seconds or 200MB of memory, per the flags documentation. Leave it on. When results start arriving with gaps, query the osquery_schedule table: it reports executions, wall time, and output size for every scheduled query, which makes the expensive one obvious in seconds.
Fixes come in a reliable order: add a WHERE clause, widen the interval, split one broad query into two narrow ones, and only then consider raising watchdog limits. A query that needs the watchdog loosened is usually a question better asked a different way.
Which anti patterns should you avoid?
Four mistakes account for most osquery query pain, and all four are visible in configs across the industry.
SELECT * in scheduled queries. Every column you do not need is bytes shipped from every host on every interval, forever. Wide rows also make differentials noisier, because a change in any column makes the row "new". Name the columns. Your pipeline bill and your future self both benefit.
Unbounded joins against generated tables. Joining processes to process_open_sockets is fine. Joining two large generated tables with no constraint on either side asks the agent to build both in full, in memory, on a production host. If a join needs no WHERE clause, question the join.
Ignoring per user tables. Tables like chrome_extensions and authorized_keys are scoped by user, and when the daemon queries them as root without a join to the users table, results can silently cover only one home directory. The documented pattern joins through users to enumerate everyone. An empty result here is not absence of extensions, it is a scoping bug.
Fleet wide rollouts of untested queries. Every horror story about osquery melting hosts starts with a query that never saw a pilot group. The blast radius of a bad query is a choice you make with rollout process, not a property of the tool. Ship to 20 hosts, read osquery_schedule for a week, then ship to 2,000.
Frequently asked questions
Can osquery queries modify the system?
No. The engine accepts only SELECT statements. Tables are read only views over system APIs, which is why handing osquery access to an analyst is far safer than handing out shell access.
Do osquery queries support full SQLite syntax?
Nearly all of it: joins, subqueries, CTEs, aggregate functions, string and date functions, and JSON extraction. The differences are the read only restriction and virtual table constraint behavior.
How do I test a query before scheduling it?
Run it in osqueryi on a representative host and check the row count and runtime feel. Then schedule it on a small pilot group and read osquery_schedule for a week before rolling it to the fleet.
How many queries should a schedule contain?
Fewer than you think. A tight schedule of 20 to 40 well constrained queries covers inventory, exposure, and persistence for most fleets. Configs with hundreds of entries usually contain borrowed packs nobody reviewed, and every unreviewed query is cost without a customer.
Why does my query return nothing in osqueryi but rows when scheduled?
Usually privileges or events. Osqueryi runs as your user unless you elevate it, while osqueryd typically runs as root or SYSTEM and sees more. Evented tables also only populate in a running daemon with the event subsystem enabled.
The takeaway
Write the question in English first. Prototype it in osqueryi until the rows match the sentence. Constrain it, schedule it differentially at the slowest interval that still answers the question, then check osquery_schedule a week later. That loop, repeated, builds a query library your team trusts. And once the rows are flowing, remember the bottleneck moves: the fleet will answer any question you ask, and the scarce resource becomes deciding which answers matter. That reading layer is the problem Artemes AI works on: an AI engine that reads deep endpoint context and decides which findings matter.
See which of your findings actually matter
Artemes AI combines deep endpoint context with AI-driven analysis to show which findings are actually exploitable on your hosts—not just everything a scanner can list. We are onboarding early access teams now.
Chris Seymour
Chris writes about vulnerability prioritization, exploitability, AI-assisted remediation, and the engineering realities of turning scanner output into remediation decisions.
