How to use Zabbix items in ODBC database monitor queries

[Jason-TELUS]

I am trying to figure out if this is possible in Zabbix. I have an external MySQL DB I would like to query, I have ODBC setup and I can query the DB, using a general query just to make sure the connection is working for example "SELECT * FROM db1.table1" this query works I get a value back. I was using db.odbc.select for this query, so I only expected one value back. when I use the same query with db.odbc.get, I get a good amount of JSON back, again this is good and expected.

I have a SNMP item where the key is named site.id, I would like to use the value of this key in the db query for example "SELECT * FROM db1.table1 WHERE col1=site.id" However I don't know how to properly format site.id in this query so that it is replaced by the value at the time the query is run. Is there a way to do this?

I think my other option is to run an external script where I pass the value site.id and return the JSON after doing the MySQL query in python.

[PeterZielony]

Ok, so you have 4000+ devices already in zbx and your goal is to have additional items for each of them. Each host has unique site.id in name you need to query DB for additional things
just to clarify if I understand correctly:
1) you have snmp monitoring already for voltage and stuff for each battery host
2)you want to add more items for each host that comes from this query as individual items per host (like a manufacturer, sn) that is being kept in DBamd you can get them using this query:

SELECT * FROM db1.table1 WHERE col1=SUBSTRING_INDEX({HOST.HOST},"-",1)

which I guess does work and it gets all information from DB for that one host if you add this item to single host as a test?

So in that case you can make a template with one item and do mass update on all hosts.

And you can take it step further have dependent items on that query that will divide all values from query to each values from DB for that host (on template level)

[cyber]

You cannot use one items value in other items parameters...

[PeterZielony]

What is the reason for query DB - it is some form of static list of snmp devices/attributes?

you could use low level discovery for DB - and based on them item prototype that will query snmp.

but that depends what you trying to accomplish and what exactly is in DB that need to be used for snmp query

[Jason-TELUS]

I have a Zabbix setup where I have ~4000 devices already monitored. The DB I am looking to query is an inventory DB. The devices monitored are battery monitors, that will tell me the Voltage, Conductance, a Sensor address, Temperature, and a timestamp of when the conductance reading was taken. The device itself has no information on the the actual battery model, serial number, installation date or manufacturing date stored which I can reference. The number of batteries being monitored varies per device and so we use a SNMP discovery rule to get this information.

The company I work for keeps the data I'd like to reference in the DB I'm looking to query. The reason for bringing this information into Zabbix is so that I can build a one stop dashboard where site maintainers can view the data from the battery monitor and the inventory DB in one place.

the battery monitors do have a location field that is monitored by Zabbix (using SNMP) and is data filled on the device at installation. We named this key site.id because this is the common name that will be used to discuss sites in everyday interactions. The device {HOST.HOST} value follow a strict naming convention when adding a host to Zabbix. For this example it would look like this: SH001234-1-BM01, with SH001234 from the host name being the same as the site.id value that were queried.

what I would like to do, is in my DB query, use site.id to get the DB information I need. I have not been idle as I have been waiting for this topic to post. So here is where I am at:

SELECT * FROM db1.table1 WHERE col1=SUBSTRING_INDEX({HOST.HOST},"-",1)

The above SQL query gets the macro {HOST.HOST} which in the example name above is SH001234-1-BM01 and slices it on the dash "-", returning the first value SH001234, which allows my query to complete. This only works because of the strict naming structure we use. The other solution was to set a macro at the Host level called {$SITEID} and reference this in the DB query, however setting that across ~4000 hosts was looking like a daunting challenge.

If I understand correctly I can't reference other item values, so this may have to do.

[PeterZielony]

Ok, so you have 4000+ devices already in zbx and your goal is to have additional items for each of them. Each host has unique site.id in name you need to query DB for additional things
just to clarify if I understand correctly:
1) you have snmp monitoring already for voltage and stuff for each battery host
2)you want to add more items for each host that comes from this query as individual items per host (like a manufacturer, sn) that is being kept in DBamd you can get them using this query:

SELECT * FROM db1.table1 WHERE col1=SUBSTRING_INDEX({HOST.HOST},"-",1)

which I guess does work and it gets all information from DB for that one host if you add this item to single host as a test?

So in that case you can make a template with one item and do mass update on all hosts.

And you can take it step further have dependent items on that query that will divide all values from query to each values from DB for that host (on template level)

[Jason-TELUS]

Yes, you understand the layout correctly. All hosts are added manually using the same template. I have the sql query setup as a odbc.get, and I have all the dependant items all sorted out (which was the easy part)

I'm still testing on our non-production platform to make sure this is going to work, but I think I have something that will work.

[PeterZielony]

Oh ok then

let us know if you need more help

edit: just note about queries - don't do them too often - 4000 queries in one go might hammer DB if they are executed separately at the same time

[Jason-TELUS]

Yes, the query should be done once per day. And we are looking at possible scheduling options