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

Two item keys may be used in “Database monitor” discovery rules:

  • db.odbc.discovery[<unique short description>,<dsn>,<connection string>] - this item transforms the SQL query result into a JSON array, turning the column names from the query result into low-level discovery macro names paired with the discovered field values. These macros can be used in creating item, trigger, etc prototypes. See also: Using db.odbc.discovery.

  • db.odbc.get[<unique short description>,<dsn>,<connection string>] - this item transforms the SQL query result into a JSON array, keeping the original column names from the query result as a field name in JSON paired with the discovered values. Compared to db.odbc.discovery[], this item does not create low-level discovery macros in the returned JSON, therefore there is no need to check if the column names can be valid macro names. The low-level discovery macros can be defined as an additional step as required, using the custom LLD macro functionality with JSONPath pointing to the discovered values in the returned JSON. See also: Using db.odbc.get.

Using db.odbc.discovery

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,<name>,lastaccess]“ internal items to monitor which proxies are alive.

Let us start with discovery rule configuration:

lld_rule_odbc.png

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[,{$DSN}]“ item, the result of this query gets automatically transformed into the following JSON:

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

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:

item_prototype_odbc.png

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

discovered_items_odbc1.png

Using db.odbc.get

Using db.odbc.get[,{$DSN}] and the following SQL example:

  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)

this JSON will be returned:

  1. [
  2.            {
  3.                "host": "Japan 1",
  4.                "count": "5"
  5.            },
  6.            {
  7.                "host": "Japan 2",
  8.                "count": "12"
  9.            },
  10.            {
  11.                "host": "Latvia",
  12.                "count": "3"
  13.            }
  14.        ]

As you can see, there are no low-level discovery macros there. However, custom low-level discovery macros can be created in the LLD macros tab of a discovery rule using JSONPath, for example:

  1. {#HOST} → $.host

Now this {#HOST} macro may be used in item prototypes:

item_prototype_odbc.png