Live Queries & Osquery
Relevant source files
The following files were used as context for generating this wiki page:
Live Queries in Fleet allow IT and security teams to run ad hoc osquery SQL against live endpoints to gather real-time data not already tracked by the server, such as installed software versions, system configurations, or vulnerability details. This system leverages the osquery agent installed on millions of devices to provide deep visibility into the state of the fleet.
The system encompasses a standard query library, AI-assisted query generation, and a rigorous schema management process that merges native osquery table definitions with Fleet-specific overrides. Users can interact with live queries through the Fleet UI, CLI (fleetctl), or via GitOps-driven workflows.
Sources: tools/fleet-slackbot/system-prompt.js:20-22, website/scripts/build-static-content.js:38-42, openspec/README.md:88-90
Architecture and Query Lifecycle
The live query system facilitates the flow of SQL instructions from the Fleet server to distributed agents, capturing results in real-time.
The diagram shows the lifecycle of a live query from generation (AI or manual) through execution on the endpoint and back to the user interface.
Sources: website/api/controllers/query-generator/get-llm-generated-sql.js:10-25, tools/fleet-slackbot/system-prompt.js:15-25
AI-Assisted Query Generation
Fleet utilizes Large Language Models (LLMs) to translate natural language questions into osquery-compatible SQL. This process involves:
- Schema Filtration: The system provides the LLM with a subset of relevant osquery tables based on the user's question.
- Platform-Specific Logic: The generator creates unique queries for macOS, Windows, Linux, and ChromeOS, accounting for table availability across platforms.
- Constraint Enforcement: Queries are instructed to avoid table aliasing (the
ASoperator) and useLIKEfor broad software matching.
Sources: website/api/controllers/query-generator/get-llm-generated-sql.js:43-65, website/api/controllers/query-generator/get-llm-generated-sql.js:112-135
Osquery Schema & Table Management
Fleet maintains an extended version of the osquery schema, combining standard osquery tables with Fleet-specific overrides for better documentation and usability.
Merged Schema Structure
The schema is defined in YAML files located in the schema/tables/ directory. Each entry can override or extend standard osquery metadata.
| Field | Type | Description |
|---|---|---|
name | String | Unique identifier for the osquery table. |
evented | Boolean | Indicates if the table is an evented table (records changes over time). |
description | Markdown | Technical explanation of the table's purpose. |
platforms | Array | Supported OS environments (e.g., darwin, windows). |
columns | Array | List of column objects containing name, type, and description. |
Sources: schema/README.md:1-40, website/scripts/build-static-content.js:636-650
Schema Processing Logic
The build script build-static-content.js processes the merged schema to generate static documentation and SQL syntax highlighting keywords.
- Platform Normalization: Normalizes platform values (e.g.,
darwintomacOS). - Column Attributes: Identifies columns that are required in
WHEREclauses or hidden fromSELECT *results. - Static Assets: Generates EJS partials for each table to be rendered on the website.
Sources: website/scripts/build-static-content.js:655-690, website/scripts/build-static-content.js:725-745
Query Validation and Syntax
To ensure stability and security, Fleet validates SQL queries before execution.
Validation Criteria
Queries must adhere to standard SQL syntax supported by SQLite (osquery's underlying engine). Validation tests include:
- Malformed SQL Rejection: Queries like
this is not a thingare rejected with syntax errors. - CTE & Subquery Support: Advanced constructs like Common Table Expressions (CTEs) and
VALUESclauses are permitted. - Platform Checks: The AI generator ensures that queries for specific platforms only use tables supported by those platforms (e.g., avoiding
pliston Windows).
Sources: frontend/components/forms/validators/validate_query/validate_query.tests.ts:18-40, frontend/components/forms/validators/validate_query/validate_query.tests.ts:70-100, website/api/controllers/query-generator/get-llm-generated-sql.js:117-125
Standard Query Library
Fleet includes a "Standard Query Library" (informational and host vital queries) baked into the application configuration.
Library Configuration
The library is parsed from docs/queries.yml. Each query entry includes:
- Discovery: A string defining when the query should be automatically suggested (for built-in types).
- Contributors: GitHub handles of community members who provided the query.
- Tags: Metadata used for categorization and filtering in the UI.
- Kind: Distinguishes between standard
queryandbuilt-invital queries.
Sources: website/scripts/build-static-content.js:38-70, website/scripts/build-static-content.js:105-125
GitOps Integration
Live queries can be managed as code. In a Fleet GitOps repository, queries are defined in lib/<platform>/queries/<name>.yml and referenced in fleet-specific configuration files (e.g., workstations.yml).
- name: "Query Name"
automations_enabled: false
description: "What this query detects"
interval: 300
logging: snapshot
platform: "darwin"
query: "SELECT * FROM apps;"
Sources: tools/fleet-slackbot/system-prompt.js:205-215, tools/fleet-slackbot/system-prompt.js:235-245
Summary
The Live Queries and Osquery systems provide the core visibility layer of Fleet. By combining AI generation, robust SQL validation, and a managed schema that overrides standard osquery documentation, Fleet enables users to safely and efficiently interrogate their infrastructure. The integration with GitOps ensure that these capabilities remain scalable and auditable within enterprise environments.