› ›

SQL 模块

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 驱动程序支持此功能。对于其他驱动程序，如果启用，则会记录“不支持驱动程序：<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。

示例

编辑

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

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

编辑

此 sql.yml 配置显示了如何捕获 MySQL 数据库中查询 SHOW GLOBAL STATUS LIKE 'Innodb_system%' 产生的与 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 并生成“table”结果

编辑

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

- 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 中的缓冲区命中率

编辑

此 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 的缓冲区命中率

编辑

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

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

编辑

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

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

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

编辑

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

可以使用 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"
  }
}

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

编辑

假设用户在服务器上可能有数百个数据库，然后手动将它们添加到查询中变得很麻烦。如果将 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 代理指标集主机设置 »