IMPORTANT: No additional bug fixes or documentation updates will be released for this version. For the latest information, see the current release documentation.

« Redis Enterprise proxy metricset SQL query metricset »

› ›

SQL module

edit

SQL module

edit

This 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.

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

sql_query

The single query you want to run.

sql_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.

Example: capture Innodb-related metrics

edit

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

edit

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

edit

This sql.yml configuration shows how to get the buffer cache hit ratio:

- module: sql
  metricsets:
    - query
  period: 10s
  hosts: ["oracle://sys:[email protected]: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

edit

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
  },
  "metricset": {
    "name": "query",
    "period": 10000
  },
  "service": {
    "address": "172.17.0.2",
    "type": "sql"
  },
  "agent": {
    "id": "670ef211-87f0-4f38-8beb-655c377f1629",
    "name": "elastic",
    "type": "metricbeat",
    "version": "8.0.0",
    "ephemeral_id": "3da88889-036e-47cb-a88b-275037fa2bc9"
  },
  "ecs": {
    "version": "1.5.0"
  },
  "host": {
    "name": "elastic"
  }
}

Example: launch two or more queries

edit

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"
  sql_query: "SELECT datid, datname, blks_read, blks_hit, tup_returned, tup_fetched, stats_reset FROM pg_stat_database"
  sql_response_format: table

- module: sql
  metricsets:
    - query
  period: 10s
  hosts: ["postgres://postgres:postgres@localhost:5432/stuff?sslmode=disable"]
  driver: "postgres"
  sql_query: "SELECT * FROM pg_catalog.pg_tables pt WHERE schemaname ='pg_catalog'"
  sql_response_format: table

Example configuration

edit

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

Metricsets

edit

The following metricsets are available:

query

« Redis Enterprise proxy metricset SQL query metricset »