Quickstartedit

You can setup the module by activating it first running

metricbeat module enable sql

Once it is activated, open modules.d/sql.yml and fill the required fields. This is an example that captures Innodb related metrics from the result of the query SHOW GLOBAL STATUS LIKE 'Innodb_system%' in a MySQL database:

sql.yml.

- 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

Table 3. SHOW GLOBAL STATUS LIKE Innodb_system%

Variable_name Value

Innodb_system_rows_deleted

0

Innodb_system_rows_inserted

0

Innodb_system_rows_read

5062

Innodb_system_rows_updated

315

Keys in the YAML are defined as follow:

  • driver: The drivers currently supported are those which already have a Metricbeat module like mssql or postgres.
  • sql_query: Is the single query you want to run
  • sql_response_format: You have 2 options here:
  • variables: Expects a table which looks like a key/value result. With 2 columns, left column will be considered a key and the right column the value. This mode generates a single event on each fetch operation.
  • table: Table mode can contain any number of columns and a single event will be generated for each row.

Results will be grouped by type in the result event for convenient mapping in Elasticsearch. So strings values will be grouped into sql.strings, numeric into sql.numeric and so on and so forth.

The event generated with the example above looks like this:

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

In this example, we are querying PostgreSQL and generate a "table" result, hence a single event for each row returned

sql.yml.

- 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

Table 4. SELECT datid, datname, blks_read, blks_hit, tup_returned, tup_fetched, stats_reset FROM pg_stat_database

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

With 3 rows on the table, three events will be generated with the contents of each row. As an example, below you can see the event 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
  }
}