SQL module
editSQL module
editThis functionality is in beta and is subject to change. The design and code is less mature than official GA features and is being provided as-is with no warranties. Beta features are not subject to the support SLA of official GA features.
The SQL module allows you to execute custom queries against an SQL database and store the results in Elasticsearch. It also enables developing various SQL metrics integrations, using sql query as input.
This module supports the databases that you can monitor with Metricbeat, including:
- PostgreSQL
- MySQL
- Oracle
- Microsoft SQL
- CockroachDB
To enable the module, run:
metricbeat module enable sql
After enabling the module, open modules.d/sql.yml
and set the required
fields:
-
driver
-
The driver can be any driver that has a Metricbeat module, such as
mssql
orpostgres
. -
raw_data.enabled
- Expects either true or false. By default false. Marking as true will generate event results in new field format.
Expects either sql_queries
or sql_query
.
-
sql_queries
-
Receives the list of queries to execute.
query
andresponse_format
is repeated to get multiple query inputs.-
query
- Expects sql query.
-
response_format
-
Either
variables
ortable
:-
variables
- Expects a two-column table that looks like a key/value result. The left column is considered a key and the right column the value. This mode generates a single event on each fetch operation.
-
table
- Expects any number of columns. This mode generates a single event for each row.
-
-
-
sql_query
-
The single query you want to run. (
Backward Compatibility
). Also provide correspondingsql_response_format
: eithervariables
ortable
Example: capture Innodb-related metrics
editThis sql.yml
configuration shows how to capture Innodb-related metrics that
result from the query SHOW GLOBAL STATUS LIKE 'Innodb_system%'
in a MySQL
database:
- module: sql metricsets: - query period: 10s hosts: ["root:root@tcp(localhost:3306)/ps"] driver: "mysql" sql_query: "SHOW GLOBAL STATUS LIKE 'Innodb_system%'" sql_response_format: variables
The SHOW GLOBAL STATUS
query results in this table:
Variable_name | Value |
---|---|
Innodb_system_rows_deleted |
0 |
Innodb_system_rows_inserted |
0 |
Innodb_system_rows_read |
5062 |
Innodb_system_rows_updated |
315 |
Results are grouped by type in the result event for convenient mapping in
Elasticsearch. For example, strings
values are grouped into sql.strings
, numeric
into sql.numeric
, and so on.
The example shown earlier generates this event:
{ "@timestamp": "2020-06-09T15:09:14.407Z", "@metadata": { "beat": "metricbeat", "type": "_doc", "version": "8.0.0" }, "service": { "address": "172.18.0.2:3306", "type": "sql" }, "event": { "dataset": "sql.query", "module": "sql", "duration": 1272810 }, "sql": { "driver": "mysql", "query": "SHOW GLOBAL STATUS LIKE 'Innodb_system%'", "metrics": { "numeric": { "innodb_system_rows_updated": 315, "innodb_system_rows_deleted": 0, "innodb_system_rows_inserted": 0, "innodb_system_rows_read": 5062 } } }, "metricset": { "name": "query", "period": 10000 }, "ecs": { "version": "1.5.0" }, "host": { "name": "elastic" }, "agent": { "name": "elastic", "type": "metricbeat", "version": "8.0.0", "ephemeral_id": "488431bd-bd3c-4442-ad51-0c50eb555787", "id": "670ef211-87f0-4f38-8beb-655c377f1629" } }
Example: query PostgreSQL and generate a "table" result
editThis sql.yml
configuration shows how to query PostgreSQL and generate
a "table" result. This configuration generates a single event for each row
returned:
- module: sql metricsets: - query period: 10s hosts: ["postgres://postgres:postgres@localhost:5432/stuff?sslmode=disable"] driver: "postgres" sql_query: "SELECT datid, datname, blks_read, blks_hit, tup_returned, tup_fetched, stats_reset FROM pg_stat_database" sql_response_format: table
The SELECT query results in this table:
datid | datname | blks_read | blks_hit | tup_returned | tup_fetched | stats_reset |
---|---|---|---|---|---|---|
69448 |
stuff |
8652 |
205976 |
1484625 |
53218 |
2020-06-07 22:50:12 |
13408 |
postgres |
0 |
0 |
0 |
0 |
|
13407 |
template0 |
0 |
0 |
0 |
0 |
Because the table contains three rows, three events are generated, one event for each row. For example, this event is created for the first row:
{ "@timestamp": "2020-06-09T14:47:35.481Z", "@metadata": { "beat": "metricbeat", "type": "_doc", "version": "8.0.0" }, "service": { "address": "localhost:5432", "type": "sql" }, "ecs": { "version": "1.5.0" }, "host": { "name": "elastic" }, "agent": { "type": "metricbeat", "version": "8.0.0", "ephemeral_id": "1bffe66d-a1ae-4ed6-985a-fd48548a1971", "id": "670ef211-87f0-4f38-8beb-655c377f1629", "name": "elastic" }, "sql": { "metrics": { "numeric": { "tup_fetched": 53350, "datid": 69448, "blks_read": 8652, "blks_hit": 206501, "tup_returned": 1.491873e+06 }, "string": { "stats_reset": "2020-06-07T20:50:12.632975Z", "datname": "stuff" } }, "driver": "postgres", "query": "SELECT datid, datname, blks_read, blks_hit, tup_returned, tup_fetched, stats_reset FROM pg_stat_database" }, "event": { "dataset": "sql.query", "module": "sql", "duration": 14076705 }, "metricset": { "name": "query", "period": 10000 } }
Example: get the buffer catch hit ratio in Oracle
editThis sql.yml
configuration shows how to get the buffer cache hit ratio:
- module: sql metricsets: - query period: 10s hosts: ["oracle://sys:Oradoc_db1@172.17.0.3:1521/ORCLPDB1.localdomain?sysdba=1"] driver: "oracle" sql_query: 'SELECT name, physical_reads, db_block_gets, consistent_gets, 1 - (physical_reads / (db_block_gets + consistent_gets)) "Hit Ratio" FROM V$BUFFER_POOL_STATISTICS' sql_response_format: table
The example generates this event:
{ "@timestamp": "2020-06-09T15:41:02.200Z", "@metadata": { "beat": "metricbeat", "type": "_doc", "version": "8.0.0" }, "sql": { "metrics": { "numeric": { "hit ratio": 0.9742963357937117, "physical_reads": 17161, "db_block_gets": 122221, "consistent_gets": 545427 }, "string": { "name": "DEFAULT" } }, "driver": "oracle", "query": "SELECT name, physical_reads, db_block_gets, consistent_gets, 1 - (physical_reads / (db_block_gets + consistent_gets)) \"Hit Ratio\" FROM V$BUFFER_POOL_STATISTICS" }, "metricset": { "period": 10000, "name": "query" }, "service": { "address": "172.17.0.3:1521", "type": "sql" }, "event": { "dataset": "sql.query", "module": "sql", "duration": 39233704 }, "ecs": { "version": "1.5.0" }, "host": { "name": "elastic" }, "agent": { "id": "670ef211-87f0-4f38-8beb-655c377f1629", "name": "elastic", "type": "metricbeat", "version": "8.0.0", "ephemeral_id": "49e00060-0fa4-4b34-80f1-446881f7a788" } }
Example: get the buffer cache hit ratio for MSSQL
editThis sql.yml
configuration gets the buffer cache hit ratio:
- module: sql metricsets: - query period: 10s hosts: ["sqlserver://SA:password@localhost"] driver: "mssql" sql_query: 'SELECT * FROM sys.dm_db_log_space_usage' sql_response_format: table
The example generates this event:
{ "@timestamp": "2020-06-09T15:39:14.421Z", "@metadata": { "beat": "metricbeat", "type": "_doc", "version": "8.0.0" }, "sql": { "driver": "mssql", "query": "SELECT * FROM sys.dm_db_log_space_usage", "metrics": { "numeric": { "log_space_in_bytes_since_last_backup": 524288, "database_id": 1, "total_log_size_in_bytes": 2.08896e+06, "used_log_space_in_bytes": 954368, "used_log_space_in_percent": 45.686275482177734 } } }, "event": { "dataset": "sql.query", "module": "sql", "duration": 40750570 } }
Example: launch two or more queries.
editTo launch two or more queries, specify the full configuration for each query. For example:
- module: sql metricsets: - query period: 10s hosts: ["postgres://postgres:postgres@localhost:5432/stuff?sslmode=disable"] driver: "postgres" raw_data.enabled: true sql_queries: - query: "SELECT datid, datname, blks_read, blks_hit, tup_returned, tup_fetched, stats_reset FROM pg_stat_database" response_format: table - query: "SELECT datname, datid FROM pg_stat_database;" response_format: variables
The example generates this event: The response event is generated in new format by enabling the flag raw_data.enabled
.
{ "@timestamp": "2022-05-13T12:47:32.071Z", "@metadata": { "beat": "metricbeat", "type": "_doc", "version": "8.3.0" }, "event": { "dataset": "sql.query", "module": "sql", "duration": 114468667 }, "metricset": { "name": "query", "period": 10000 }, "service": { "address": "localhost:55656", "type": "sql" }, "sql": { "driver": "postgres", "query": "SELECT datid, datname, blks_read, blks_hit, tup_returned, tup_fetched, stats_reset FROM pg_stat_database", "metrics": { "blks_hit": 6360, "tup_returned": 2225, "tup_fetched": 1458, "datid": 13394, "datname": "template0", "blks_read": 33 } }, "ecs": { "version": "8.0.0" }, "host": { "name": "Muthu-mps" }, "agent": { "type": "metricbeat", "version": "8.3.0", "ephemeral_id": "8decc9eb-5ea5-47d8-8a22-fac507a5521b", "id": "6bbf5058-afed-44c6-aa05-775ee14a2da4", "name": "Muthu-mps" } }
The example generates this event: By disabling the flag raw_data.enabled
, which is the old format.
{ "@timestamp": "2022-05-13T13:09:19.599Z", "@metadata": { "beat": "metricbeat", "type": "_doc", "version": "8.3.0" }, "event": { "dataset": "sql.query", "module": "sql", "duration": 77509917 }, "service": { "address": "localhost:55656", "type": "sql" }, "metricset": { "name": "query", "period": 10000 }, "sql": { "driver": "postgres", "query": "SELECT datid, datname, blks_read, blks_hit, tup_returned, tup_fetched, stats_reset FROM pg_stat_database", "metrics": { "string": { "stats_reset": "2022-05-13T12:02:33.825483Z" }, "numeric": { "blks_hit": 6360, "tup_returned": 2225, "tup_fetched": 1458, "datid": 0, "blks_read": 33 } } }, "ecs": { "version": "8.0.0" }, "host": { "name": "Muthu-mps" }, "agent": { "version": "8.3.0", "ephemeral_id": "bc09584b-62db-4b45-bfe9-6b7e8e982361", "id": "6bbf5058-afed-44c6-aa05-775ee14a2da4", "name": "Muthu-mps", "type": "metricbeat" } }
Example: Merge multiple queries to single event.
editMultiple queries will create multiple events, one for each query. It may be preferrable to create a single event by combining the metrics together in a single event.
This feature can be enabled using the merge_results
config.
However, such a merge is possible only if the table queries being merged, each produce a single row.
For example:
- module: sql metricsets: - query period: 10s hosts: ["postgres://postgres:postgres@localhost?sslmode=disable"] driver: "postgres" raw_data.enabled: true merge_results: true sql_queries: - query: "SELECT blks_hit,blks_read FROM pg_stat_database limit 1;" response_format: table - query: "select checkpoints_timed,checkpoints_req from pg_stat_bgwriter;" response_format: table
This creates a combined event as below, where blks_hit
, blks_read
, checkpoints_timed
and checkpoints_req
are part of same event.
{ "@timestamp": "2022-07-21T07:07:06.747Z", "agent": { "name": "Lalits-MBP-2", "type": "metricbeat", "version": "8.4.0", "ephemeral_id": "b0867287-e56a-492f-b421-0ac870c426f9", "id": "3fe7b378-6f9e-4ca3-9aa1-067c4a6866e5" }, "metricset": { "period": 10000, "name": "query" }, "service": { "type": "sql", "address": "localhost" }, "sql": { "metrics": { "blks_read": 21, "checkpoints_req": 1, "checkpoints_timed": 66, "blks_hit": 7592 }, "driver": "postgres" }, "event": { "module": "sql", "duration": 18883084, "dataset": "sql.query" } }
The SQL module supports the standard configuration options that are described in Modules. Here is an example configuration:
metricbeat.modules: - module: sql metricsets: - query period: 10s hosts: ["user=myuser password=mypassword dbname=mydb sslmode=disable"] driver: "postgres" sql_query: "select now()" sql_response_format: table
The following metricsets are available: