The SQL module allows you to execute custom queries against an SQL database and store the results in {es}. 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
This 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
{es}. 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
This 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
This 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
This 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.
To 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.
Multiple queries will create multiple events, one for each query. It is desired to create single event by combining the metrics together in a single event. However, such a merge is possible only if the table queries produce single row.
This can be enabled using the request_merge
config.
For example:
- module: sql
metricsets:
- query
period: 10s
hosts: ["postgres://postgres:post@localhost?sslmode=disable"]
driver: "postgres"
raw_data.enabled: true
request_merge: true
sql_queries:
- query: "SELECT blks_hit FROM pg_stat_database limit 1;"
response_format: table
- query: "SELECT blks_read FROM pg_stat_database limit 1;"
response_format: table
This creates a combined event as below, where `blks_hit` and `blks_read` are part of same event. [source,json]
{ "@timestamp": "2022-07-18T14:03:30.049Z", "service": { "address": "localhost", "type": "sql" }, "sql": { "query": "MULTIPLE", "metrics": { "blks_hit": 9883, "blks_read": 21 }, "driver": "postgres" }, "event": { "dataset": "sql.query", "module": "sql", "duration": 19461958 }, "metricset": { "name": "query", "period": 10000 } }