› ›

SQL 模块

SQL 模块edit

SQL 模块允许您对 SQL 数据库执行自定义查询并将结果存储在 Elasticsearch 中。它还允许使用 SQL 查询作为输入来开发各种 SQL 指标集成。

此模块支持您可以使用 Metricbeat 监控的数据库，包括

PostgreSQL
MySQL
Oracle
Microsoft SQL
CockroachDB

要启用该模块，请运行

metricbeat module enable sql

启用模块后，打开 modules.d/sql.yml 并设置所需字段

驱动程序: 驱动程序可以是任何具有 Metricbeat 模块的驱动程序，例如 mssql 或 postgres。
fetch_from_all_databases: 需要 true 或 false，默认设置为 false。标记为 true 将为服务器中的所有数据库启用执行 sql_queries 或 sql_query。目前只有 mssql 驱动程序支持此功能。对于其他驱动程序，如果启用，将记录“fetch from all databases 功能不支持驱动程序：<driver_name>”错误。
raw_data.enabled: 需要 true 或 false，默认设置为 false。标记为 true 将以新的字段格式生成事件结果。

根据用例使用 sql_queries 或 sql_query。

sql_queries

要执行的查询列表。 query 和 response_format 字段会重复以获取多个查询输入。

query

单个 SQL 查询。

response_format

可以是 variables 或 table。

variables: 需要一个类似于键值结果的双列表格。左列被视为键，右列被视为值。此模式在每次获取操作中生成一个事件。
table: 需要任意数量的列。此模式为每行生成一个事件。

sql_query (向后兼容)

要运行的单个查询。此外，提供相应的 sql_response_format（值：variables 或 table）类似于 sql_queries 的 response_format。

示例edit

下面列出了在 sql.yml 中连接到支持数据库的配置示例。

示例：捕获与 InnoDB 相关的指标edit

此 sql.yml 配置显示了如何捕获查询 SHOW GLOBAL STATUS LIKE 'Innodb_system%' 在 MySQL 数据库中产生的与 InnoDB 相关的指标

- 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

SHOW GLOBAL STATUS 查询导致以下表格

Variable_name	Value
Innodb_system_rows_deleted	0
Innodb_system_rows_inserted	0
Innodb_system_rows_read	5062
Innodb_system_rows_updated	315

结果在结果事件中按类型分组，便于在 Elasticsearch 中映射。例如，strings 值分组到 sql.strings 中，numeric 分组到 sql.numeric 中，依此类推。

上面显示的示例生成以下事件

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

示例：查询 PostgreSQL 并生成“表格”结果edit

此 sql.yml 配置显示了如何查询 PostgreSQL 并生成“表格”结果。此配置为返回的每行生成一个事件

- 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

SELECT 查询导致以下表格

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

由于表格包含三行，因此会生成三个事件，每行一个事件。例如，此事件为第一行创建

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

示例：获取 Oracle 中的缓冲区命中率edit

此 sql.yml 配置显示了如何获取缓冲区命中率

- 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

此示例生成以下事件

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

示例：获取 MSSQL 的缓冲区命中率edit

此 sql.yml 配置获取缓冲区命中率

- 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

此示例生成以下事件

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

示例：启动两个或多个查询。edit

要启动两个或多个查询，请为每个查询指定完整的配置。例如

- 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

此示例生成以下事件：通过启用标志 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"
  }
}

此示例生成以下事件：通过禁用标志 raw_data.enabled，这是旧格式。

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

示例：将多个查询合并到一个事件中。edit

多个查询将创建多个事件，每个查询一个。通过将指标组合到一个事件中来创建一个事件可能更可取。

可以使用 merge_results 配置启用此功能。

但是，这种合并只有在合并表格查询时才有可能，每个查询都生成一行。

例如

- 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

这将创建一个组合事件，如下所示，其中 blks_hit、blks_read、checkpoints_timed 和 checkpoints_req 是同一个事件的一部分。

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

示例：为服务器上存在的所有数据库执行给定的查询edit

假设用户可能在他们的服务器上有数百个数据库，然后手动将它们添加到查询中变得很麻烦。如果将 fetch_from_all_databases 设置为 true，那么 SQL 模块将自动获取数据库名称并将数据库选择器语句添加到查询中，以便查询可以在提供的数据库上运行。

目前，此功能仅适用于 mssql 驱动程序。例如

- 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

对于 mssql 实例，默认情况下只存在四个数据库，即 — master、model、msdb、tempdb。因此，如果启用了 fetch_from_all_databases，那么查询 SELECT DB_NAME() AS 'database_name' 将针对它们中的每一个运行，即每次抓取都会有 4 个文档（每个数据库一个）。

{
    "@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 代理指标集主机设置 »