Database Datasets

Database datasets define the queries a database device runs. Each database engine has its own dataset type, matching the device it attaches to — dataset types mssql, mysql, postgres, and oracle. The connection itself belongs to the device: see Microsoft SQL Server, MySQL, PostgreSQL, and Oracle. To create and assign datasets through the web interface, see Management. For the dataset and profile model, see Overview.

Collector Types

• Microsoft SQL Query (mssql_custom_query_collector): Scheduled read-only queries against a Microsoft SQL Server instance.
• MySQL Query (mysql_custom_query_collector): Scheduled read-only queries against a MySQL server.
• PostgreSQL Query (postgres_custom_query_collector): Scheduled read-only queries against a PostgreSQL server.
• Oracle Query (oracle_custom_query_collector): Scheduled read-only queries against an Oracle service.

All four accept the same input properties and support the same incremental and time-window collection modes. They differ only in the database they connect to.

Device Compatibility

A database dataset attaches only to devices of its own type: a MySQL Query dataset can be assigned to mysql devices and to no others. The dataset type and the device type are the same value, which is what enforces the pairing.

Configuration is split between the two sides:

Owned by	Configuration
Device	Host, port, database, credentials, authentication mode, TLS, connect timeout, connection string
Dataset	The query itself, its schedule, timeout, row and retry limits, incremental tracking, and time windows

A dataset therefore carries no credentials. The collector opens one connection per device and runs every query assigned to it through that connection.

The same collection rules can also be written by hand in the device's own definitions: block, without a dataset. The two paths produce the same configuration, and a device that has datasets assigned merges them into that block.

Assignment

A device receives either datasets or a profile, never both — assigning one replaces any existing assignment of the other. Assigning several datasets of the same type to one device is supported; their definitions are merged rather than replacing one another.

The relationship can be managed from either side:

• From the device: the Data Configuration tab on the device's detail page, or the Configure Data Collection step of the device creation wizard.
• From the dataset: the Assign to Devices step of the dataset creation wizard, or the Assigned Devices tab on the dataset's detail page.

Query Inputs

Queries are defined as dataset inputs. A dataset assigned to a database device supplies the device's definitions: block, and the same block can be written by hand in the device's YAML. Either way the definition name must match the device's collector constant, and each entry under inputs: is one query running on its own schedule with its own state.

Input properties

Inputs share the standard dataset input frame (id, name, status) plus the following properties:

Field	Required	Type	Default	Description
query	Y	string	—	The SQL statement to run. May contain the binding tokens described below.
cron	N	string	—	5-field cron expression. Empty or "0" runs the query on every stats interval tick.
timeout	N	int	120	Query timeout in seconds. A zero or negative value falls back to the default.
validate_query	N	boolean	true	Enforce that the query is a single read-only statement. See Query validation.
max_rows	N	int	0	Per-run row cap for bounded, chunked backfills; the remainder resumes on the next run. 0 means no cap. Ignored when the query uses window tokens.
max_retries	N	int	1	How many times a failed run retries on the next collection cycle before waiting for its next cron slot. 0 resolves to the default, so retrying cannot be switched off.
skippable	N	boolean	true	When true, a backlog of missed cron slots collapses into a single catch-up run. false replays every missed slot, one per cycle, until the schedule catches up.
pipeline_name	N	string	—	Names a preprocessing pipeline to ship with the device's configuration. It does not route records: a database device's records enter the pipeline assigned to the device.
tracking_column	N	string	—	Column whose maximum value becomes the next cursor. See Incremental collection.
tracking_column_type	N	string	numeric	numeric, timestamp, or string.
tracking_initial_value	N	string	—	First-run starting point. Defaults to 0, the Unix epoch, or the empty string per type.
tracking_rescan_margin	N	int	0	Re-scan this far behind the watermark on each run to catch late-committing rows. Numeric units, or seconds for timestamps. Not applied when tracking_column_type is string.
window_format	N	string	datetime	How window bounds are bound: datetime, unix, unix_ms, or rfc3339. See Time-window collection.
initial_lookback	N	int	0	First-run backfill in seconds for window queries. 0 collects forward only.

Binding tokens

Three tokens may appear in a query. Each is rewritten to the database driver's own placeholder syntax and its value is passed as a bound parameter, so a token value can never alter the shape of the statement:

Token	Bound value
{{cursor}}	The last-seen value of the tracking column.
{{earliest}}	Lower bound of this run's time window.
{{latest}}	Upper bound of this run's time window.

The {{...}} spelling is the same on every database. Do not substitute a driver-native placeholder such as :cursor or ? — those are not recognized as tokens and reach the driver unbound.

Incremental collection

Setting tracking_column turns an input into a high-watermark collector: each run collects only rows past the largest value seen so far. The watermark is persisted after every successful run and survives restarts and device-ownership moves within a cluster.

query: "SELECT id, event_time, message FROM audit_log WHERE id > {{cursor}} ORDER BY id ASC"
tracking_column: id
tracking_column_type: numeric

Always ORDER BY the tracking column so that runs capped by max_rows, or interrupted partway, stay contiguous.

caution

Configuring tracking_column without placing {{cursor}} in the query is accepted but collects nothing incrementally: the watermark advances while the full result set is re-read on every run. A warning is logged when this combination is detected.

High-watermark tracking assumes the column is monotonic in commit order. A row that commits late with an already-passed value falls behind the watermark and is skipped. tracking_rescan_margin re-collects a safety margin behind the watermark on each run to catch those rows, at the cost of re-emitting the margin — deduplicate downstream when enabling it.

Each input keeps its own watermark, keyed by the input's id and name together. An input written by hand with both an id and a name was previously keyed by id alone, so it re-collects once from tracking_initial_value after the move to the combined key, then resumes normally.

Time-window collection

A query referencing {{earliest}} or {{latest}} collects a contiguous time slice per run. Consecutive windows tile exactly, with no gap and no overlap: this run's upper bound becomes the next run's lower bound, persisted the same way as a cursor.

query: "SELECT * FROM login_events WHERE event_time >= {{earliest}} AND event_time < {{latest}}"
window_format: datetime
initial_lookback: 3600

Use >= on the lower bound and < on the upper bound so that a row landing exactly on a boundary is collected by exactly one window.

window_format must match how the column stores time. The default datetime binds a native timestamp; unix and unix_ms bind integer epoch values; rfc3339 binds a string.

max_rows is ignored for window queries, because a capped window would silently drop its remainder.

Query validation

With validate_query left at its default, a query must be a single statement beginning with SELECT, WITH, SHOW, EXPLAIN, DESCRIBE, or DESC. Multiple statements are rejected, as is a WITH or EXPLAIN statement containing INSERT, UPDATE, DELETE, or MERGE — and EXPLAIN ANALYZE, which executes its target rather than describing it. String literals, quoted identifiers, and comments are excluded from the check, so a semicolon or keyword inside them does not trigger a rejection. One trailing semicolon is stripped.

Set validate_query: false only for stored procedures or vendor read syntax the validator cannot prove read-only.

warning

Query validation guards against configuration accidents; it is not a security boundary. Pair it with a database login that has read-only permissions on the objects being queried.

Collected records

Each row becomes one JSON record whose fields are the query's column names, carrying native JSON types rather than stringified values:

Column type	Rendered as
Numeric, boolean	JSON number or boolean
Exact numeric (DECIMAL, NUMERIC, MONEY)	Unquoted JSON number with full precision preserved
Date and time	ISO-8601 string in UTC, millisecond precision
JSON, JSONB	Nested JSON object
Binary	Base64 string
NULL	null

A top-level @timestamp field is added to every record, which is what lets it flow through pipelines and into targets as a first-class document. If the query itself returns a column named @timestamp, that column is kept as-is and nothing is added.

caution

Schedule queries with cron. The interval-based option in the query editor is not applied by the database collectors: a query saved that way carries no cron expression and therefore runs on every stats interval tick rather than at the chosen interval.

caution

The per-query preprocessing pipeline field does not route records for these collectors. A database device's records enter the pipeline assigned to the device.

Examples

Two datasets assigned to one MySQL device produce a merged collector definition, each query keeping its own schedule and state...

- id: 930000002 name: mysql-prod type: mysql status: true properties: host: mysql01.example.com username: vmetric_reader password: "<password>" database: appdb definitions: - name: mysql_custom_query_collector status: true inputs: - id: 2001 name: Audit Log status: true properties: query: "SELECT id, event_time, message FROM audit_log WHERE id > {{cursor}} ORDER BY id ASC" cron: "*/5 * * * *" tracking_column: id tracking_column_type: numeric - id: 2002 name: Failed Logins status: true properties: query: "SELECT user, host, attempted_at FROM login_failures WHERE attempted_at >= {{earliest}} AND attempted_at < {{latest}}" cron: "0 * * * *" window_format: datetime initial_lookback: 3600

Each returned row becomes one record, with the query's column names as fields and an added @timestamp...

{ "@timestamp": "2026-08-27T09:20:00.000Z", "id": 84213, "event_time": "2026-08-27T09:19:58.000Z", "message": "configuration updated" }