Collect installed agent versions using mysql query

[Jvlavl]

Hi,

I want to retrieve all agent versions that are installed on the hosts.
This way I can upgrade the hosts that have an older version.

I would like to do it via a Mysql query to put it directly in Excel.

Rgds,

John

[Jvlavl]

Hello,

when I use the api I get the following result

{"jsonrpc":"2.0","result":[{"itemid":"46787","type":"0","snmp_community":""," snmp_oid":"","hostid":"10501","name":"Version of zabbix_agent(d) running","key_":"agent.version","delay":"3600","hi story":"7","trends":"0","status":"0","value_type": "1","trapper_hosts":"","units":"","multiplier":"0" ,"delta":"0","snmpv3_securityname":"","snmpv3_secu ritylevel":"0","snmpv3_authpassphrase":"","snmpv3_ privpassphrase":"","formula":"1","error":"","lastl ogsize":"0","logtimefmt":"","templateid":"23161"," valuemapid":"0","delay_flex":"","params":"","ipmi_ sensor":"","data_type":"0","authtype":"0","usernam e":"","password":"","publickey":"","privatekey":"" ,"mtime":"0","flags":"0","interfaceid":"770","port ":"","description":"","inventory_link":"0","lifeti me":"0","snmpv3_authprotocol":"0","snmpv3_privprot ocol":"0","state":"0","snmpv3_contextname":"","eva ltype":"0","lastclock":"1517993987","lastns":"5432 33670","lastvalue":"3.2.0","prevvalue":"3.2.0"}],"id":1}

Can anyone tell me in what table I can find this value lastvalue":"3.2.0"?

Rgds,

John

[Jvlavl]

Hello all,

If anyone likes to get this data directly out of the database you can use this mysql query.

SELECT
h.host AS 'Server', x.value AS 'Agent version', max(x.clock) as 'Last Clock'
FROM items i
LEFT JOIN hosts h ON i.hostid = h.hostid
LEFT JOIN history_str x ON x.itemid = i.itemid WHERE i.key_ = 'agent.version' and x.value not like 'NULL'

GROUP BY h.host, i.key_

Topic closed.

Rgds,

John

[rikrom]

Hi John thank you for this query, on Zabbix Server 6.4.5 still works, well done!

[kloczek]

Hi,

I want to retrieve all agent versions that are installed on the hosts.
This way I can upgrade the hosts that have an older version.

I would like to do it via a Mysql query to put it directly in Excel.

Why do you want to do this using SQL query or Excel if you can do this using straight zabbix?
Look on my zabbix agent template:

https://github.com/kloczek/zabbix-templates/tree/master/Service%20Zabbix%20Agent

This template has "version" item which has in item settings "Populates host inventory field" to "Software Application A"
With this template, if you want to see which one hosts have exact version all that you need to do is to go Inventory -> Overview -> choose on right side "Software Application A" and you would see aggregated information about versions and names of the hosts on which exact versions of the agents are installed.
All those information are updated on each sample "version" metric (in my template "version" is sampled every 5 min)