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:

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 or postgres.

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 and response_format is repeated to get multiple query inputs.

query

Expects sql query.

response_format

Either variables or table:

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 corresponding sql_response_format: either variables or table

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