--- title: Collecting Custom Metrics with Database Monitoring description: >- Use the custom_queries option to collect metrics from your own database tables. breadcrumbs: >- Docs > Database Monitoring > Collecting Custom Metrics with Database Monitoring --- # Collecting Custom Metrics with Database Monitoring Use `custom_queries` to collect metrics from any table the Agent's database user can read. This extends the data available in Datadog beyond the query performance metrics that Database Monitoring collects natively, such as application state tables, business counters, or queue depths. ## Before you begin{% #before-you-begin %} The Datadog Agent must be installed and the database integration configured. The Agent's database user needs `SELECT` on any tables you query. ## Configuration{% #configuration %} Add `custom_queries` to your integration's `conf.yaml` file. Each entry in the list runs one SQL query and maps its output columns to metrics or tags. | Option | Required | Description | | --------------- | -------- | -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | | `metric_prefix` | Yes | All metrics emitted by this query begin with this prefix. | | `query` | Yes | The SQL to execute. All returned rows are evaluated. Use the pipe character (`|`) for multi-line queries. | | `columns` | Yes | A list of columns in the same order as your `SELECT`. Each column requires a `name` and a `type`. Set `type` to `gauge`, `count`, `rate`, or another [metric type](https://docs.datadoghq.com/metrics/types.md) to emit a metric, or `tag` to apply the column value as a tag on every metric from this query. | | `tags` | No | A list of static tags applied to every metric from this query. | **Notes:** - The number of `columns` entries must equal the number of columns returned by the query. - The order of `columns` entries must match the order of columns returned by the query. - At least one entry in `columns` must be a metric type (not `tag`). ## Examples{% #examples %} {% tab title="PostgreSQL" %} Add `custom_queries` to your `postgres.d/conf.yaml` file. If the query reads from a table the `datadog` user cannot already access, grant the permission first: ```sql GRANT SELECT ON TO datadog; ``` **Example:** The following `company` table contains employee records: ```text id | name | age | address | salary --------------------------------------- 1 | Paul | 32 | California | 20000 2 | Allen | 25 | Texas | 30000 3 | Teddy | 23 | Norway | 45000 ``` To collect `age` and `salary` as metrics with `name` and `address` as tags: ```yaml custom_queries: - metric_prefix: postgresql.employee query: SELECT age, salary, name, address FROM company columns: - name: employee_age type: gauge - name: employee_salary type: gauge - name: name type: tag - name: address type: tag tags: - source:hr_db ``` After you update the file, [restart the Agent](https://docs.datadoghq.com/agent/configuration/agent-commands.md#restart-the-agent). For the full configuration reference, see [Postgres Custom Metric Collection](https://docs.datadoghq.com/integrations/faq/postgres-custom-metric-collection-explained.md). {% /tab %} {% tab title="MySQL" %} Add `custom_queries` to your `mysql.d/conf.yaml` file. **Important:** All table references must include the database name (`database_name.table_name`). If you omit the database name, the Agent fails with the error: `No database selected`. **Example:** The following `company` table in the `testdb` database contains employee records: ```text id | name | age | address | salary --------------------------------------- 1 | Paul | 32 | California | 20000 2 | Allen | 25 | Texas | 30000 3 | Teddy | 23 | Norway | 45000 ``` To collect `age` and `salary` as metrics with `name` and `address` as tags: ```yaml custom_queries: - metric_prefix: mysql.employee query: SELECT age, salary, name, address FROM testdb.company columns: - name: employee_age type: gauge - name: employee_salary type: gauge - name: name type: tag - name: address type: tag tags: - source:hr_db ``` After you update the file, [restart the Agent](https://docs.datadoghq.com/agent/configuration/agent-commands.md#restart-the-agent). For the full configuration reference, see [MySQL Custom Queries](https://docs.datadoghq.com/integrations/guide/mysql-custom-queries.md). {% /tab %} {% tab title="SQL Server" %} SQL Server supports two approaches for collecting custom metrics: custom queries or performance counters. ### Custom queries{% #custom-queries %} Add `custom_queries` to your `sqlserver.d/conf.yaml` file to collect metrics from any table. **Example:** The following `company` table in `testdb` contains employee records: ```text id | name | age | address | salary --------------------------------------- 1 | Paul | 32 | California | 20000 2 | Allen | 25 | Texas | 30000 3 | Teddy | 23 | Norway | 45000 ``` To collect `age` and `salary` as metrics with `name` and `address` as tags: ```yaml custom_queries: - metric_prefix: sqlserver.employee query: SELECT age, salary, name, address FROM testdb.dbo.company columns: - name: employee_age type: gauge - name: employee_salary type: gauge - name: name type: tag - name: address type: tag tags: - source:hr_db ``` ### Performance counters{% #performance-counters %} Use `custom_metrics` to collect metrics from `sys.dm_os_performance_counters` and other system DMVs. ```yaml custom_metrics: - name: sqlserver.clr.execution counter_name: CLR Execution ``` | Option | Required | Description | | --------------- | -------- | ------------------------------------------------------------------------------------ | | `name` | Yes | The metric name in Datadog. | | `counter_name` | Yes | The counter name from `sys.dm_os_performance_counters`. | | `instance_name` | No | A specific counter instance. Use `ALL` to collect all instances (requires `tag_by`). | | `tag_by` | No | Tag name used to differentiate instances when `instance_name: ALL`. | After you update the file, [restart the Agent](https://docs.datadoghq.com/agent/configuration/agent-commands.md#restart-the-agent). For the full configuration reference, including performance counter details and the legacy stored procedure method, see [Collect SQL Server Custom Metrics](https://docs.datadoghq.com/integrations/guide/collect-sql-server-custom-metrics.md). {% /tab %} ## Validation{% #validation %} After the Agent runs, search for your metrics in the [Metrics Explorer](https://docs.datadoghq.com/metrics/explorer.md). To check for configuration errors, [run the Agent's status subcommand](https://docs.datadoghq.com/agent/configuration/agent-commands.md#agent-status-and-information) and look for your integration under the Checks section: ```text postgres -------- - instance #0 [ERROR]: 'Missing metric_prefix parameter in custom_queries' - Collected 0 metrics, 0 events & 0 service checks ``` ## Further Reading{% #further-reading %} - [Database Monitoring](https://docs.datadoghq.com/database_monitoring.md) - [Metric Types](https://docs.datadoghq.com/metrics/types.md)