5 使用ODBC SQL查询进行发现

概述

这种类型的低等级 发现 是使用SQL查询完成的，其结果自动转换到一个适合低等级发现的JSON对象。

键值

使用“数据库监视器”项类型执行SQL查询。 因此， ODBC监控 页面上的大多数指令都适用于获得工作的“数据库监控”发现规则，唯一的区别在于

  1. db.odbc.discovery[<description>,<dsn>]

键值应该替换为 “db.odbc.select[,]”.

从Zabbix server/proxy 3.0开始支持使用SQL查询发现。

作为说明如何将SQL查询转换为JSON的实例，让我们通过在Zabbix数据库上执行ODBC查询来考虑Zabbix proxies的低等级发现。 这对于自动创建“zabbix [proxy，，lastaccess]”内部监控项以监视哪些代理是活动的非常有用。

让我们从发现规则配置开始：

5 使用ODBC SQL查询进行发现 - 图1

所有必填输入字段都标有红色星号。

以下对Zabbix数据库的直接查询用于选择所有Zabbix proxies，包括它们监视的主机数。例如，可用主机数来过滤掉空代理：

  1. mysql> SELECT h1.host, COUNT(h2.host) AS count FROM hosts h1 LEFT JOIN hosts h2 ON h1.hostid = h2.proxy_hostid WHERE h1.status IN (5, 6) GROUP BY h1.host;
  2. +---------+-------+
  3. | host    | count |
  4. +---------+-------+
  5. | Japan 1 |     5 |
  6. | Japan 2 |    12 |
  7. | Latvia  |     3 |
  8. +---------+-------+
  9. 3 rows in set (0.01 sec)

通过“db.odbc.discovery[]“监控项的内部工作，这个查询结果自动转换到一下JSON：

  1. {
  2.     "data": [
  3.         {
  4.             "{#HOST}": "Japan 1",
  5.             "{#COUNT}": "5"
  6.         },
  7.         {
  8.             "{#HOST}": "Japan 2",
  9.             "{#COUNT}": "12"
  10.         },
  11.         {
  12.             "{#HOST}": "Latvia",
  13.             "{#COUNT}": "3"
  14.         }
  15.     ]
  16. }

可以看到，列名称表示宏名称，而所选行表示这些宏的值。

如果列名称如何转换成宏名称并不明显，建议在上面的示例中使用列别名，如 “COUNT(h2.host) AS count” 。

如果无法将列名称转换成有效的宏名称，则不支持发现规则，并详细说明违规列号的错误信息。如果需要其他帮助，在Zabbix server的日志文件中的 DebugLevel=4 下提供获取的列名称：

  1. $ grep db.odbc.discovery /tmp/zabbix_server.log
  2.  ...
  3.  23876:20150114:153410.856 In db_odbc_discovery() query:'SELECT h1.host, COUNT(h2.host) FROM hosts h1 LEFT JOIN hosts h2 ON h1.hostid = h2.proxy_hostid WHERE h1.status IN (5, 6) GROUP BY h1.host;'
  4.  23876:20150114:153410.860 db_odbc_discovery() column[1]:'host'
  5.  23876:20150114:153410.860 db_odbc_discovery() column[2]:'COUNT(h2.host)'
  6.  23876:20150114:153410.860 End of db_odbc_discovery():NOTSUPPORTED
  7.  23876:20150114:153410.860 Item [Zabbix server:db.odbc.discovery[proxies,{$DSN}]] error: Cannot convert column #2 name to macro.

现在我们了解到SQL查询如何转换为一个JSON对象，我们在监控项原型中使用 {#HOST} 宏：

5 使用ODBC SQL查询进行发现 - 图2

一旦执行发现后，将会为每个proxy创建一个监控项：

5 使用ODBC SQL查询进行发现 - 图3

5 Discovery using ODBC SQL queries

Overview

This type of low-level discovery is done using SQL queries, whose results get automatically transformed into a JSON object suitable for low-level discovery.

Item key

SQL queries are performed using a “Database monitor” item type. Therefore, most of the instructions on ODBC monitoring page apply in order to get a working “Database monitor” discovery rule, the only difference being that a

  1. db.odbc.discovery[<description>,<dsn>]

key should be used instead of “db.odbc.select[,]”.

Discovery using SQL queries is supported since Zabbix server/proxy 3.0.

As a practical example to illustrate how the SQL query is transformed into JSON, let us consider low-level discovery of Zabbix proxies by performing an ODBC query on Zabbix database. This is useful for automatic creation of “zabbix[proxy,,lastaccess]” internal items to monitor which proxies are alive.

Let us start with discovery rule configuration:

5 使用ODBC SQL查询进行发现 - 图4

All mandatory input fields are marked with a red asterisk.

Here, the following direct query on Zabbix database is used to select all Zabbix proxies, together with the number of hosts they are monitoring. The number of hosts can be used, for instance, to filter out empty proxies:

  1. mysql> SELECT h1.host, COUNT(h2.host) AS count FROM hosts h1 LEFT JOIN hosts h2 ON h1.hostid = h2.proxy_hostid WHERE h1.status IN (5, 6) GROUP BY h1.host;
  2. +---------+-------+
  3. | host    | count |
  4. +---------+-------+
  5. | Japan 1 |     5 |
  6. | Japan 2 |    12 |
  7. | Latvia  |     3 |
  8. +---------+-------+
  9. 3 rows in set (0.01 sec)

By the internal workings of “db.odbc.discovery[]” item, the result of this query gets automatically transformed into the following JSON:

  1. {
  2.     "data": [
  3.         {
  4.             "{#HOST}": "Japan 1",
  5.             "{#COUNT}": "5"
  6.         },
  7.         {
  8.             "{#HOST}": "Japan 2",
  9.             "{#COUNT}": "12"
  10.         },
  11.         {
  12.             "{#HOST}": "Latvia",
  13.             "{#COUNT}": "3"
  14.         }
  15.     ]
  16. }

It can be seen that column names become macro names and selected rows become the values of these macros.

If it is not obvious how a column name would be transformed into a macro name, it is suggested to use column aliases like “COUNT(h2.host) AS count” in the example above.

In case a column name cannot be converted into a valid macro name, the discovery rule becomes not supported, with the error message detailing the offending column number. If additional help is desired, the obtained column names are provided under DebugLevel=4 in Zabbix server log file:

  1. $ grep db.odbc.discovery /tmp/zabbix_server.log
  2.  ...
  3.  23876:20150114:153410.856 In db_odbc_discovery() query:'SELECT h1.host, COUNT(h2.host) FROM hosts h1 LEFT JOIN hosts h2 ON h1.hostid = h2.proxy_hostid WHERE h1.status IN (5, 6) GROUP BY h1.host;'
  4.  23876:20150114:153410.860 db_odbc_discovery() column[1]:'host'
  5.  23876:20150114:153410.860 db_odbc_discovery() column[2]:'COUNT(h2.host)'
  6.  23876:20150114:153410.860 End of db_odbc_discovery():NOTSUPPORTED
  7.  23876:20150114:153410.860 Item [Zabbix server:db.odbc.discovery[proxies,{$DSN}]] error: Cannot convert column #2 name to macro.

Now that we understand how a SQL query is transformed into a JSON object, we can use {#HOST} macro in item prototypes:

5 使用ODBC SQL查询进行发现 - 图5

Once discovery is performed, an item will be created for each proxy:

5 使用ODBC SQL查询进行发现 - 图6