Osquery ATC: Querying Any SQLite Database on the Endpoint

The problem with osquery ATC is not writing the JSON. It is trusting an application database you do not own.

Automatic Table Construction, or ATC, exposes a local SQLite database as an osquery table. That turns browser history, macOS privacy grants, Munki usage records, or another application store into fleet SQL without a custom extension. Useful. Also easy to get wrong. The database can move, lock, encrypt itself, change columns, or restrict access while your query keeps returning nothing.

Most osquery ATC guides stop after one successful query. Production work starts there. A good definition needs a schema contract, a permission model, a test fixture, a measured schedule, an owner, and an alert for empty results. Otherwise, a missing row can mean either “no issue” or “collection broke.” Security cannot build a decision on that ambiguity.

How does osquery ATC work?

Osquery opens a SQLite file, runs a read query inside that database, and presents the selected columns as a virtual table. The official ATC configuration guide uses the macOS TCC database as its example. Its definition names a table, a database path, a SQL statement, the returned columns, and a platform. No C++ table plugin. No extension process.

That syntax is deliberately plain. The hard boundary is outside it. Osquery must be able to traverse every directory in the path and read the database. The application must use a SQLite format that the embedded library understands. The selected column list must match the result set. If one of those conditions changes, the virtual table may disappear or return an error.

Start with sqlite3, then test the ATC through osqueryi. Those are different proofs. The first proves the source database and SQL. The second proves osquery permissions, configuration parsing, table registration, and returned types.

When should you use ATC instead of an extension?

Use ATC when the fact already lives in a local SQLite database and a read query can expose it safely. Write an extension when collection needs an API, custom parsing, authentication, business logic, or a write action. ATC is an adapter. It is not a general plugin system.

The distinction saves time. A small extension may take days to design, package, sign, deploy, and monitor. An ATC definition may take an hour. But low build cost does not remove operating cost. If the application ships five different schemas across your fleet, someone still has to support all five.

Good sources have stable paths, documented schemas, modest size, clear ownership, and facts that improve a decision. A package manager database can confirm installed state. A privacy database can confirm grants. An application cache full of transient presentation data is usually a bad source because its format changes and its rows do not prove security state.

Ask three questions before building. What decision will this table change? Which application team can explain the schema? What happens when the source cannot be read? If the first answer is “more visibility,” stop. Name the ticket, exception, hunt, or audit result that will consume it. If the other answers have no owner, the integration will become abandoned configuration.

Exhaust the built in surface first. The guide to the most useful osquery tables shows what already ships, while the extensions guide covers data sources that require code. ATC belongs between them: more flexible than a built in table, much less flexible than an extension.

What breaks when SQLite databases are live?

A SQLite database is a live application component, not a flat text file. Journal mode and locks matter. In rollback mode, a writer may temporarily block readers. In WAL mode, recent changes can live in a separate WAL file until a checkpoint moves them into the main file.

Do not solve that by copying the database file with a normal file copy while it is changing. The SQLite online backup documentation explains why an ordinary copy can contain old and new pages, and why its backup API takes a consistent snapshot. If an application database is too contentious for direct reads, have the application owner create a safe replica or export. Guessing around locks can damage the source you meant to observe.

Encryption is a harder stop. Some products use SQLCipher, custom page codecs, or protected key material. A readable file is not necessarily a readable database. Do not collect keys or weaken application protection just to make ATC work. Pick another evidence source.

Permissions create another silent split. A local administrator may prove the query in a shell while the osquery service account cannot enter a parent directory. On macOS, privacy controls can also block database access even when POSIX permissions look correct. Test as the deployed service identity on every operating system version you support.

How do you survive schema drift?

Treat the source schema like an external API. Record the application version, database path, source table, expected columns, sample types, and an owner. Keep a sanitized fixture for each supported schema. Run the ATC query against those fixtures in CI before a configuration reaches endpoints.

Add two health queries. One checks that the virtual table exists in osquery_registry. The other returns a bounded row count and newest source timestamp. Alert when registration fails, the row count changes sharply, or freshness exceeds the expected application behavior. Empty can be valid, so compare by cohort and source, not with one global threshold.

Version the definition beside the fixture and expected output. A change request should show the old schema, new schema, affected application releases, agent requirement, and rollback. Do not overwrite the only definition and hope every endpoint updates at once. Keep old and new tables during the migration, compare results on the same canary hosts, then remove the old contract after coverage reaches the agreed threshold.

A recent change shows why version gates matter. Osquery 5.19.0, released August 13, 2025, fixed ATC reads against open Firefox databases. A query could be correct while older agents still failed against the live source. Set a minimum agent version for that table and measure the unsupported cohort before rollout.

How often should an ATC query run?

Match cadence to the decision. A privacy grant table might justify a 15 minute read. Software usage may be useful once a day. A database that changes only during application upgrades should not be queried every minute. The official configuration specification caps a scheduled interval at 604,800 seconds, or one week, but the useful number comes from evidence freshness and source cost.

Simple math catches bad schedules. Three ATC queries across 10,000 endpoints every hour create 720,000 database reads a day. Change that to every 15 minutes and the count becomes 2.88 million. If the result drives a daily compliance report, the extra reads bought nothing.

Canary on one percent of each endpoint cohort. Capture execution duration, CPU time, returned rows, database errors, and application complaints for a full business cycle. Then move to ten percent. Osquery supports deterministic schedule sharding from 1 to 100 percent, which gives you a controlled promotion path instead of an all fleet bet.

What belongs in an ATC production checklist?

• Named application owner and security owner.
• Supported application versions and source schemas.
• Exact database path, service identity, and required permissions.
• Read query with explicit columns and a bounded result set.
• Fixtures for current and prior schemas.
• Minimum osquery version and platform scope.
• Canary results for cost, errors, and application impact.
• Freshness alert, registration alert, and removal plan.

The removal plan matters. If an application update breaks the table, disable the schedule first. Do not keep retrying an expensive failing query across the fleet while engineers investigate. Preserve the last valid result with its collection time so downstream systems know it is stale.

Run a failure drill before approval. Remove read permission on a test path, rename one source column, hold a write lock, and return a zero row database. Confirm that each condition creates a distinct visible outcome. The owner should be able to tell source absence from permission failure and schema failure without opening an endpoint shell.

Also test the application, not only osquery. Measure its write latency and error logs during the canary. Ten engineers losing five minutes to a blocked local application is 50 minutes of business cost from one bad read pattern. Collection is safe only when the source owner agrees that normal work remains unaffected.

Frequently asked questions

Can osquery ATC query any SQLite database?

It can query many local SQLite databases, but “any” is too broad. Permissions, encryption, incompatible extensions, locks, and schema differences can block or distort access.

Does ATC modify the source database?

The configured statement should be a read query. Osquery SQL is designed around selection, but you should still validate the source, permissions, and locking behavior with the application owner.

Can ATC paths include wildcards?

Yes. The official example uses a percent wildcard for user home directories. Test how many paths resolve and keep the result bounded, especially on shared systems.

Why does an ATC table return no rows?

The source may be empty, the path may not resolve, the service may lack access, the database may be locked or encrypted, or the schema may have changed. Check registration and verbose status logs before declaring the result clean.

The takeaway

Pick one database with a real owner and a clear security decision. Document its schema, prove the query as the service account, keep a fixture, canary the schedule, and alert on freshness. Then break the path and change a column on purpose. If the failure is visible and contained, promote it. Osquery ATC is ready when you can trust both the rows and the absence of rows.

Continue with the definitive osquery handbook, the practical query guide, and the fleet management operating model. The older performance tuning guide shows how to measure query cost before a wide rollout.