« Redis Enterprise proxy metricset Host Setup »

› ›

SQL module

The SQL module allows you to execute custom queries against an SQL database and store the results in Elasticsearch. It also enables the development of 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 or postgres.
fetch_from_all_databases: Expects either true or false and it is by default set to false. Marking as true will enable execution sql_queries or sql_query for all databases in a server. Currently only mssql driver supports this feature. For other drivers, if enabled, "fetch from all databases feature is not supported for driver: <driver_name>" error would be logged.
raw_data.enabled: Expects either true or false and it is by default set to false. Marking as true will generate event results in a new field format.

Use sql_queries or sql_query depending on the use-case.

sql_queries

List of queries to execute. query and response_format fields are repeated to get multiple query inputs.

query

Single 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 is 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 (Backward Compatibility)

Single query you want to run. Also, provide corresponding sql_response_format (value: variables or table) similar to sql_queries's response_format.

Example

edit

Examples of configurations in sql.yml to connect with supported databases are mentioned below.

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:password@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

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

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"
  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": "mps"
  },
  "agent": {
    "type": "metricbeat",
    "version": "8.3.0",
    "ephemeral_id": "8decc9eb-5ea5-47d8-8a22-fac507a5521b",
    "id": "6bbf5058-afed-44c6-aa05-775ee14a2da4",
    "name": "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": "mps"
    },
  "agent": {
    "version": "8.3.0",
    "ephemeral_id": "bc09584b-62db-4b45-bfe9-6b7e8e982361",
    "id": "6bbf5058-afed-44c6-aa05-775ee14a2da4",
    "name": "mps",
    "type": "metricbeat"
  }
}

Example: Merge multiple queries into a single event.

edit

Multiple queries will create multiple events, one for each query. It may be preferable 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 are merged, each produces 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": "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"
  }
}

Example: Execute given queries for all database(s) present in a server

edit

Assuming a user could have 100s of databases on their server and then it becomes cumbersome to add them manually to the query. If fetch_from_all_databases is set to true then SQL module would fetch the databases names automatically and prefix the database selector statement to the queries so that the queries can run against the database provided.

Currently, this feature only works with mssql driver. For example:

- module: sql
  metricsets:
    - query
  period: 50s
  hosts: ["sqlserver://<user>:<password>@<host>"]
  raw_data.enabled: true

  fetch_from_all_databases: true

  driver: "mssql"
  sql_queries:
    - query: SELECT DB_NAME() AS 'database_name';
      response_format: table

For an mssql instance, by default only four databases are present namely — master, model, msdb, tempdb. So, if fetch_from_all_databases is enabled then query SELECT DB_NAME() AS 'database_name' runs for each one of them i.e., there would be in total 4 documents (one each for 4 databases) for every scrape.

{
    "@timestamp": "2023-07-16T22:05:26.976Z",
    "@metadata": {
        "beat": "metricbeat",
        "type": "_doc",
        "version": "8.10.0"
    },
    "service": {
        "type": "sql",
        "address": "localhost"
    },
    "event": {
        "dataset": "sql.query",
        "module": "sql",
        "duration": 40346375
    },
    "metricset": {
        "name": "query",
        "period": 50000
    },
    "sql": {
        "metrics": {
            "database_name": "master"
        },
        "driver": "mssql",
        "query": "USE [master]; SELECT DB_NAME() AS 'database_name';"
    },
    "host": {
        "os": {
            "type": "macos",
            "platform": "darwin",
            "version": "13.3.1",
            "family": "darwin",
            "name": "macOS",
            "kernel": "<redacted>",
            "build": "<redacted>"
        },
        "name": "<redacted>",
        "id": "<redacted>",
        "ip": [
            "<redacted>"
        ],
        "mac": [
            "<redacted>"
        ],
        "hostname": "<redacted>",
        "architecture": "arm64"
    },
    "agent": {
        "name": "<redacted>",
        "type": "metricbeat",
        "version": "8.10.0",
        "ephemeral_id": "<redacted>",
        "id": "<redacted>"
    },
    "ecs": {
        "version": "8.0.0"
    }
}
{
    "@timestamp": "2023-07-16T22:05:26.976Z",
    "@metadata": {
        "beat": "metricbeat",
        "type": "_doc",
        "version": "8.10.0"
    },
    "agent": {
        "ephemeral_id": "<redacted>",
        "id": "<redacted>",
        "name": "<redacted>",
        "type": "metricbeat",
        "version": "8.10.0"
    },
    "event": {
        "module": "sql",
        "duration": 43147875,
        "dataset": "sql.query"
    },
    "metricset": {
        "period": 50000,
        "name": "query"
    },
    "service": {
        "address": "localhost",
        "type": "sql"
    },
    "sql": {
        "metrics": {
            "database_name": "tempdb"
        },
        "driver": "mssql",
        "query": "USE [tempdb]; SELECT DB_NAME() AS 'database_name';"
    },
    "ecs": {
        "version": "8.0.0"
    },
    "host": {
        "name": "<redacted>",
        "architecture": "arm64",
        "os": {
            "platform": "darwin",
            "version": "13.3.1",
            "family": "darwin",
            "name": "macOS",
            "kernel": "<redacted>",
            "build": "<redacted>",
            "type": "macos"
        },
        "id": "<redacted>",
        "ip": [
            "<redacted>"
        ],
        "mac": [
            "<redacted>"
        ],
        "hostname": "<redacted>"
    }
}
{
    "@timestamp": "2023-07-16T22:05:26.976Z",
    "@metadata": {
        "beat": "metricbeat",
        "type": "_doc",
        "version": "8.10.0"
    },
    "host": {
        "os": {
            "build": "<redacted>",
            "type": "macos",
            "platform": "darwin",
            "version": "13.3.1",
            "family": "darwin",
            "name": "macOS",
            "kernel": "<redacted>"
        },
        "id": "<redacted>",
        "ip": [
            "<redacted>"
        ],
        "mac": [
            "<redacted>"
        ],
        "hostname": "<redacted>",
        "name": "<redacted>",
        "architecture": "arm64"
    },
    "agent": {
        "ephemeral_id": "<redacted>",
        "id": "<redacted>",
        "name": "<redacted>",
        "type": "metricbeat",
        "version": "8.10.0"
    },
    "service": {
        "address": "localhost",
        "type": "sql"
    },
    "sql": {
        "metrics": {
            "database_name": "model"
        },
        "driver": "mssql",
        "query": "USE [model]; SELECT DB_NAME() AS 'database_name';"
    },
    "event": {
        "dataset": "sql.query",
        "module": "sql",
        "duration": 46623125
    },
    "metricset": {
        "name": "query",
        "period": 50000
    },
    "ecs": {
        "version": "8.0.0"
    }
}
{
    "@timestamp": "2023-07-16T22:05:26.976Z",
    "@metadata": {
        "beat": "metricbeat",
        "type": "_doc",
        "version": "8.10.0"
    },
    "host": {
        "architecture": "arm64",
        "os": {
            "kernel": "<redacted>",
            "build": "<redacted>",
            "type": "macos",
            "platform": "darwin",
            "version": "13.3.1",
            "family": "darwin",
            "name": "macOS"
        },
        "name": "<redacted>",
        "id": "<redacted>",
        "ip": [
            "<redacted>"
        ],
        "mac": [
            "<redacted>"
        ],
        "hostname": "<redacted>"
    },
    "agent": {
        "type": "metricbeat",
        "version": "8.10.0",
        "ephemeral_id": "<redacted>",
        "id": "<redacted>",
        "name": "<redacted>"
    },
    "event": {
        "dataset": "sql.query",
        "module": "sql",
        "duration": 49649250
    },
    "metricset": {
        "name": "query",
        "period": 50000
    },
    "service": {
        "address": "localhost",
        "type": "sql"
    },
    "sql": {
        "metrics": {
            "database_name": "msdb"
        },
        "driver": "mssql",
        "query": "USE [msdb]; SELECT DB_NAME() AS 'database_name';"
    },
    "ecs": {
        "version": "8.0.0"
    }
}

« Redis Enterprise proxy metricset Host Setup »