Database Datasets

Database datasets define what a database device collects. Each database engine has its own query dataset type, matching the device it attaches to — dataset types mssql, mysql, postgres, and oracle. Microsoft SQL Server additionally has four health datasets that read the server's own state; see SQL Server Health Datasets. 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.
Microsoft SQL Blocking Queries (mssql_blocking_query_collector): The sessions waiting on a lock in each user database, who blocks them, and what each last ran.
Microsoft SQL Expensive Queries (mssql_expensive_query_collector): The queries that read the most in each user database since the last reading.
Microsoft SQL Metrics (mssql_metrics_collector): How the server and its databases are doing — CPU and buffer pool by database, file I/O and space, table and index use, and stored procedure runs.
Microsoft SQL Inventory (mssql_inventory_collector): What the server has, from its settings and Agent jobs to its databases, tables, indexes, and users, each item marked with what changed.

The four query collectors accept the same input properties and support the same incremental and time-window collection modes. They differ only in the database they connect to. The four Microsoft SQL Server health datasets are configured differently; see SQL Server Health Datasets.

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 collection tab on the device's detail page, or the 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	the dataset's	The preprocessing pipeline this query's records enter. Left empty, they enter the dataset's `pipeline_name`, and with that empty too, the device's pipeline. A name not shipped to the director falls back the same way.
`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.

note

A query's records enter the query's own pre-processing pipeline. A query that names none uses the dataset's pipeline_name, and a dataset that names none uses the device's pipeline.

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"
}

SQL Server Health Datasets

Besides custom queries, a Microsoft SQL Server device can read the server's own health, over the device's connection and with any of its authentication modes. Each health dataset is assigned like any other dataset, attaches only to mssql devices, and has its own interval.

note

Health datasets can be created through the dataset creation wizard (under the Microsoft SQL category) or declared in the device's definitions: block — see Microsoft SQL Server.

Dataset	Definition	Interval (default, least)	Rows per query (default)	Parts
Microsoft SQL Blocking Queries	`mssql_blocking_query_collector`	60 s, 10 s	100	—
Microsoft SQL Expensive Queries	`mssql_expensive_query_collector`	300 s, 60 s	25	—
Microsoft SQL Metrics	`mssql_metrics_collector`	300 s, 60 s	100	7
Microsoft SQL Inventory	`mssql_inventory_collector`	3600 s, 300 s	1000	14

Wizard fields

The Dataset configuration step asks for:

Field	Required	Behavior
Interval (Optional)	N	In seconds. Leave empty to use the dataset's default interval. At least the dataset's least interval.
Rows per query	N	Leave empty to keep the dataset's default, the largest or busiest first. From `1` to `10000`.
Query timeout (Optional)	N	Leave empty to let each query run up to 60 seconds. At least `1`.
Parts to read	Y*	At least one part must be ticked. All parts are ticked by default.
Pre-processing pipeline	N	The pipeline the dataset's records enter, as on every dataset.

* Metrics and Inventory only; the blocking and expensive queries are read whole.

Properties

Field	Required	Type	Default	Description
`interval`	N	int	per dataset	Seconds between readings. An interval under the dataset's least is ignored.
`top`	N	int	per dataset	How many rows a part keeps from each query, the largest or busiest first — per database for a part read in each database, across the server otherwise. At most `10000`.
`timeout`	N	int	`60`	Seconds each query may take.
`items`	N	string[] or string	every part	The metrics' or the inventory's parts to read, as a list or a comma-separated string.
`pipeline_name`	N	string	the device's	The preprocessing pipeline the dataset's records enter.

Metrics parts

Part	Console label	Read	One document per
`cpu`	CPU by database	across the server	database: its CPU time since the last reading, and its share of SQL Server's and of the machine's
`memory`	Buffer pool by database	across the server	database: its pages in the buffer pool, their size, and the free space in them
`file_stats`	Data file size and I/O	in each database	data file: size, space used, and reads and writes since the server started
`log_stats`	Transaction log size and I/O	in each database	transaction log file: the same
`table_stats`	Table rows and size	in each database	user table: rows, and space reserved, used, and unused
`index_stats`	Index size and use	in each database	index: rows, space, and user scans, seeks, lookups, and updates since the server started
`procedure_stats`	Stored procedure runs	across the server	stored procedure run since the last reading: executions, and what its last run cost

Sizes are in bytes and shares are fractions of one. Counters since the server started are written as they are, not as rates.

Inventory parts

Part	Console label	Items
`server`	Server	The instance: version, edition, collation, and properties, with its machine's processors, memory, and start time, and its registry settings where the login may read them
`agent_job`	SQL Server Agent jobs	Each SQL Server Agent job, with its steps and schedules
`endpoint`	Endpoints	Each endpoint
`login`	Logins	Each login
`resource_pool`	Resource pools	Each Resource Governor pool
`role`	Server roles	Each server role
`database`	Databases	Each database: its settings, and its last full, differential, and log backups
`database_file`	Database files	Each data and log file: path, type, filegroup, maximum size, and growth
`table`	Tables	Each user table
`index`	Indexes	Each index
`index_fragmentation`	Index fragmentation	Each index over 10% fragmented and 100 pages
`missing_index`	Missing indexes	Each index the optimizer asked for, with the index it proposes
`procedure`	Stored procedures	Each stored procedure
`user`	Database users	Each database user

From a part's second reading on, each item carries event.action: added, changed, or unchanged. An item gone since the last reading is written once more, marked removed. When a part could not be read in a database, that database's items keep their state: they are neither written nor marked removed.

Permissions

The device's login needs:

VIEW SERVER STATE (VIEW SERVER PERFORMANCE STATE on SQL Server 2022) and VIEW ANY DEFINITION.
A user in each database it reads, or CONNECT ANY DATABASE.
Read access to msdb, for the Agent jobs and the backups.
For the server item's registry settings, permission to run xp_instance_regread — in practice a sysadmin. Without it, the item is written without them.

Only the databases the login can open and that are online are read. SQL Server 2014 and later is supported.

Azure SQL Database

On Azure SQL Database the device reads only the one database it connects to. The Agent jobs, the backups, and the server's machine and registry settings are not read there.

A part the login may not read, in one database or across the server, is logged as a warning and left out of that reading; the rest of the reading goes on. A reading that fails waits its interval before it is tried again.

Output

The blocking and expensive queries write application log events. The metrics write metric documents and the inventory writes inventory documents, each with event.dataset set to mssql.<part> — for example mssql.file_stats or mssql.agent_job.

Example

Reading blocking sessions every 30 seconds, and five inventory parts on the default hourly interval...

- id: 930000001
  name: mssql-prod
  type: mssql
  status: true
  properties:
    host: sql01.example.com
    username: vmetric_reader
    password: "<password>"
    database: master
  definitions:
    - name: mssql_blocking_query_collector
      status: true
      properties:
        interval: 30
        top: 50
    - name: mssql_inventory_collector
      status: true
      properties:
        items: server,agent_job,login,database,database_file
        timeout: 120

Collector Types​

Device Compatibility​

Assignment​

Query Inputs​

Input properties​

Binding tokens​

Incremental collection​

Time-window collection​

Query validation​

Collected records​

Examples​

SQL Server Health Datasets​

Wizard fields​

Properties​

Metrics parts​

Inventory parts​

Permissions​

Output​

Example​