See for the official Zabbix site.


Jump to: navigation, search

Low-level discovery through "Database monitor" items


Status: v1.1

Owner: Aleksandrs


It is currently possible for "Database monitor" items to return JSON for low-level discovery by constructing the JSON string manually using SQL concatenation (see ZBX-8489 for an example).

However, it would be much more convenient to transform query results into JSON automatically, where column names would become macro names and column values would become the values of these macros. For instance, consider the following query:

> select letter, description from components;
| letter | description   |
| A      | API           |
| D      | Documentation |
| F      | Frontend      |
| G      | Agent         |
| I      | Installation  |
| J      | Java gateway  |
| L      | Appliance     |
| P      | Proxy         |
| S      | Server        |
| T      | Templates     |
10 rows in set (0.00 sec)

It can be turned automatically into the following JSON object, suitable for low-level discovery:

{"data" : [
    { "{#LETTER}" : "A", "{#DESCRIPTION}" : "API"           },
    { "{#LETTER}" : "D", "{#DESCRIPTION}" : "Documentation" },
    { "{#LETTER}" : "F", "{#DESCRIPTION}" : "Frontend"      },
    { "{#LETTER}" : "G", "{#DESCRIPTION}" : "Agent"         },
    { "{#LETTER}" : "I", "{#DESCRIPTION}" : "Installation"  },
    { "{#LETTER}" : "J", "{#DESCRIPTION}" : "Java gateway"  },
    { "{#LETTER}" : "L", "{#DESCRIPTION}" : "Appliance"     },
    { "{#LETTER}" : "P", "{#DESCRIPTION}" : "Proxy"         },
    { "{#LETTER}" : "S", "{#DESCRIPTION}" : "Server"        },
    { "{#LETTER}" : "T", "{#DESCRIPTION}" : "Templates"     }

Therefore, new db.odbc.discovery[<description>,<dsn>] item should be introduced that returns this JSON based on the query. It shall accept the same arguments as[<description>,<dsn>] item.

Conversion to JSON

The general idea behind how a JSON object is formed is clear from the example above. This section discusses specifics of JSON conversion.

Column name is converted to macro name by uppercasing all ASCII letters, and adding "{#" on the left and "}" on the right. If the resulting string does not yield a valid macro name (that consisting of A-Z, 0-9, ".", "_" characters, see macro documentation), the item becomes not supported with a corresponding error message.

In order to obtain a column name from the query, function SQLColAttribute() with SQL_DESC_LABEL should be used. The rationale and behavior are described in a comment and (4) of ZBXNEXT-2321. Here, we just note the fact that the result is different, depending on the database engine that is queried. Therefore, two things are required:

  • documentation should state that a column alias is recommended, in case it is not obvious how a column name like "count(*)" would be converted to macro name;
  • Zabbix server and proxy should provide obtained column names under DebugLevel=4, so that queries are easier to debug for Zabbix administrators.

After column names are obtained, it should be checked that no two macro names are identical. If so, the item should become not supported, detailing the offending macro name.

If the SQL query is successful, but yields no rows, an empty "data" array is returned.

Database NULL should be converted to JSON null. Proper behavior of JSON null with LLD filters will be implemented in ZBX-9146.

Frontend changes

Currently, when a user tries to create a new item and selects "Database monitor" item type, key field is prefilled with "[<unique short description>,<dsn>]". This behavior should remain for regular items and item prototypes, however, for discovery rules the key field should now be prefilled with "db.odbc.discovery[<unique short description>,<dsn>]". Also, "Select" button should be enabled in both cases and descriptions of these two item keys should be added to help items.

Translation strings

  • for[] in help items: Return first column of the first row of the SQL query result.
  • for db.odbc.discovery[] in help items: Transform SQL query result into a JSON object for low-level discovery.




  • Decided on JSON nulls.