Capture multiple columns/rows from database monitor query output

[johnjohn]

I'm successfully using Zabbix database monitor to get single value as a result of a sql query. Is there a way to capture greater output from a sql query?

For example, currently I'm doing something like this:
select uptime from uptime_report;
Zabbix will capture the single value - what-ever uptime contains.

I want to do something like this:
select * from uptime_report;
And have Zabbix capture the entire output which will be multiple columns and rows - perhaps save to csv. Ultimately I want it to send the results via email.

Do I have to write external script for this?

Thanks.

[coza2eire]

Hi JohnJohn,

Its a big right old pain in the "Your know what"! I'm trying to do the very same.... I run a isql query against an RMM server's Mysql database to return results on all devices that are experiencing HDD drive failure.

The result returns many rows but Zabbix Database Monitor only records the first field / column from the first row and that's it. This is of no use. It would at least have been more useful if it could grab the entire result from the query and display it as a Log / in text format.

I am currently looking at running an external script for this to try get this data but in the meantime what I've done is have the monitor run a basic "Count" query so that if it detects say 5 devices with failing drives it returns a value of "5". This allows me to configure a trigger at least so that I am made aware of the issue and then I run a manual SQL query against the DB so that I can get a list of the 5 devices that are experiencing imminent failures... Like I said - some pain in the hole! It would have been manageable if I was monitoring a small set of servers / endpoints but we currently monitor 2700+ devices in our RMM and it is struggling (and ultimately disables the builting SQL queries from running) and so I use Zabbix to monitor the RMM servers monitoring our managed devices.

[ovas]

Hello!

This is possible, but not on database monitor level, but via low level discoveries: https://www.zabbix.com/documentation...evel_discovery. Your point of interest is "Discovery using ODBC SQL queries" option.
Make sure you have unixODBC supported and it works correctly from terminal before proceeding https://www.zabbix.com/documentation...es/odbc_checks.

To conclude, the procedure would be as follows:
1. Install and configure ODBC connector: https://www.zabbix.com/documentation...es/odbc_checks
2. Add Discovery Rule to the necessary host/template and launch it. Have a glimpse at the zabbix_server.log, where you will find query results in JSON output, you will need the field name later: https://www.zabbix.com/documentation...bc_sql_queries
3. In your newly created Discovery Rule, navigate to "Item prototypes" and start creating one. The item key you want to create is db.odbc.select from Database Monitor type. Make sure that key ID is unique for every discovered item! For example, in my case it's db.odbc.select[data_length {#TABLE_NAME},test], where "data_length " is static prefix for the item key, "{#TABLE_NAME}" is passed on from JSON output and "test" - ODBC connector name.
From here, it's optional:
4. If you need only specific SQL information to be stored in Zabbix, you can add filters on Zabbix Server level. Make a Regular Expression meeting your needs: https://www.zabbix.com/documentation...ar_expressions
5. In your Discovery rule's filter section, you can adjust discovery to only process item prototypes for specific regular expression results.

Hope I understood your problem correctly.

[numerouno]

What would the SQL statement look like for the item prototype? (different to SQL in discovery rule?)

I have a {#NAME} and {#VALUE} stored from discover rule. I want to call these values in Zabbix to be able to create a graph.