osquery: ask endpoints precise, repeatable questions
Use SQL-like queries for inventory and state while respecting table cost, platform differences, scheduling, and result interpretation.
Scope
For administrators and defenders evaluating osquery for endpoint visibility.
Start with the situation, not the slogan
A security tool should begin with a question. osquery: ask endpoints precise, repeatable questions is presented here as a method for collecting or testing evidence, not as a substitute for an analyst. Decide what fact would change the next decision, then choose the smallest data set and safest operation that can establish that fact.
Tools are confident even when their inputs are incomplete. They will print a result in a reassuringly technical font and leave uncertainty as an exercise for the operator. Record scope, version, configuration, time and source data so the output can be interpreted, repeated and challenged.
osquery exposes operating-system state as tables. It is excellent for precise questions, but results are snapshots, table semantics vary, and expensive queries can affect endpoints.
Composite scenario
How this usually reaches the desk
Suppose the immediate question is raised by this clue: The schema, platform support, constraints, and cost of each table are understood. Before opening the tool, write the possible explanations and the field or observation that would distinguish them. Collect a known-good comparison where possible. This prevents a colourful result from becoming a conclusion simply because it arrived first.
This scenario combines common operational patterns; it is not presented as a report of one named incident.
What to look for
Begin with preserved, comparable evidence. One signal is rarely proof; use independent observations and a reliable timeline before declaring scope or intent.
The schema, platform support, constraints, and cost of each table are understood.
Identify which input creates this observation and whether collection can alter the system. Prefer read-only or passive acquisition when the question permits it.
Scheduled queries have stable names, intervals, expected result type, and an owner.
Check time zone, host, identity, interface and data provenance. A precise result attached to the wrong source remains wrong, only with better punctuation.
Differential results are interpreted correctly and forwarded to protected storage.
Compare the output with baseline and independent evidence. A match, alert or open port is a lead whose meaning depends on asset purpose, exposure and surrounding activity.
Working examples
Run it, read it, decide what changes
These examples use documentation addresses, test identities and bounded targets. Replace placeholders only inside systems you own or are explicitly authorised to operate. Read the expected result and next action before running the command; a successful command is evidence, not yet a conclusion.
Inventory listening ports with owning processes
- Prerequisites
- Local osquery shell or an approved distributed-query platform.
SELECT lp.address, lp.port, lp.protocol, p.pid, p.name, p.path
FROM listening_ports lp
LEFT JOIN processes p USING (pid)
ORDER BY lp.port;Rows associate listeners with processes where the operating system exposes a PID.
Blank process fields are possible for kernel services, permissions or short-lived processes.
Compare with the approved service inventory and investigate new internet-bound listeners through firewall and process telemetry.
List startup items
- Prerequisites
- osquery with access to platform startup tables.
SELECT name, path, args, source, status
FROM startup_items
ORDER BY name;Configured startup entries and their paths are displayed.
Presence does not prove execution and familiar names can point to unfamiliar paths.
Hash unexpected targets, check signatures and package ownership, and compare with a known-good baseline.
Check local users and groups
- Prerequisites
- Permission to query local account data.
SELECT u.username, u.uid, u.directory, g.groupname
FROM users u
LEFT JOIN user_groups ug ON u.uid=ug.uid
LEFT JOIN groups g ON ug.gid=g.gid
ORDER BY u.username, g.groupname;Local users and group memberships are listed.
Directory identities and cloud roles may not appear. Built-in service accounts are normal on many systems.
Diff against an approved baseline and verify unexpected privileged membership through native identity logs.
A practical workflow
Read the whole sequence before starting. Several workstreams may run in parallel, but their evidence, authority and expected outcomes still need to be explicit. Every step below points back to a concrete example; use the example as implementation evidence, not as permission to operate outside the stated scope.
Operational judgement
Operational cost belongs in the design. For osquery, Endpoint and Inventory, estimate collection volume, storage, query time, analyst attention and the impact of credentials used by the tool. A detection that nobody can review is not free; it has simply moved its bill to the incident queue.
Preserve provenance through every transformation. Keep original evidence immutable, note tool and rule versions, and store the exact query or configuration with the result. This is equally useful for incident review, false-positive tuning and the humbling moment when yesterday’s clever filter turns out to have excluded the answer.
Handover
Make the result useful to the next person
Package the useful workflow, not merely the output. Store the question, authority, input source, collection method, tool and rule version, query, time zone, raw result, interpretation and known blind spots. Separate immutable source material from filtered or enriched copies. If the result contains credentials, personal data or confidential content, apply access and retention controls before pasting it into a ticket where it may enjoy a longer life than the system itself.
The operational handover should show how “Write the question in plain language and identify the minimum tables and columns needed.” leads to a repeatable decision and how the team verifies “Monitor endpoint performance, query health, result volume, and downstream parsing.” Add a known test event and an alert for collection failure. State which changes in product version, schema, environment or threat behaviour require review. The result is ready when another authorised analyst can reproduce it, understand its limitations and obtain the same conclusion without borrowing the original operator’s intuition.
Validate before you close
A query is operational only when it answers the intended question on supported platforms, stays within a measured cost budget, and produces understandable results.
Capture the test, the expected result and the observed result. Where a person or business owner must accept restored service, name them in the record. A green dashboard can confirm that a component is answering; it cannot confirm that invoices, identities or restored data are trustworthy.
Finish with a compact closure note: the original trigger, confirmed scope, evidence retained, controls changed, tests passed, known gaps, residual risk, and the people responsible for the remaining work. Schedule a review while the timeline is still fresh enough to challenge. The purpose is not to find a person to blame; computers already perform blame with admirable efficiency. The purpose is to make the next response faster, safer and less dependent on one person remembering where the useful log was hidden.
Common mistakes
- Starting with a tool before writing down the question you need to answer.
- Collecting more data than the team can protect, retain, and review.
- Treating an alert or match as a conclusion instead of a lead that needs context.
These errors usually come from haste, unclear ownership or misplaced confidence. Build the safeguard into the runbook: a required evidence field, a second-person review, a rollback test or a specific exit criterion.
Questions people ask when the clock is running
Does a match prove malicious activity?
No. It proves that the input satisfied the rule or query. Confirm provenance, context and related behaviour before assigning intent or impact.
How much data should we collect?
Enough to answer the written question and support likely follow-up, subject to privacy, retention and operational limits. More data is not automatically more truth; it is often more storage with a search box.
What makes the workflow production-ready?
A query is operational only when it answers the intended question on supported platforms, stays within a measured cost budget, and produces understandable results. Add ownership, monitoring for collection failure, access control and a documented review cadence.
Safety boundary
Use these steps only on systems you own or are explicitly authorised to assess. Preserve evidence, follow your organisation’s legal and regulatory obligations, and prefer reversible actions when the situation is not yet understood.
Primary references
- osquery Documentationosquery Foundation
Editorial status: first edition. Review the linked vendor documentation for product- and version-specific changes before acting.