Show unsupported items, transfer hostid into human readable name

select itemid,key_,hostid from items where state='1';
SELECT i.hostid FROM items i WHERE i.state='1'
+--------+-------------------------------------------+--------+
| itemid | key_                                      | hostid |
+--------+-------------------------------------------+--------+
|  23261 | zabbix[process,ipmi poller,avg,busy]      |  10084 |
|  23262 | zabbix[process,java poller,avg,busy]      |  10084 |
|  23267 | zabbix[process,snmp trapper,avg,busy]     |  10084 |
|  23328 | zabbix[process,vmware collector,avg,busy] |  10084 |
|  23635 | zabbix[vmware,buffer,pused]               |  10084 |
|  25367 | zabbix[process,ipmi manager,avg,busy]     |  10084 |
+--------+-------------------------------------------+--------+


select host from hosts where hostid='10084';
SELECT h.host FROM hosts h WHERE h.hostid='10084'
+---------------+
| host          |
+---------------+
| Zabbix server |
+---------------+


items & hosts

SELECT
h.host AS 'Host name',
i.name AS 'ITEM name',
i.key_ AS 'KEY'
FROM hosts h
INNER JOIN
items i ON h.hostid = i.hostid
WHERE i.state='1';
Byat

1 comment:

  1. spikeextremeSeptember 06, 2022

    Its possible to use the select to return hostname, item name, key and unsupported error to debug the motive of unssupported item.

    SELECT DISTINCT h.name as host,i.name,i.key_,COALESCE(ir.error,'') AS error, h.status
    FROM hosts h, hosts_groups hg,items_applications ia,items i
    LEFT JOIN item_rtdata ir ON i.itemid=ir.itemid
    WHERE i.type<>9 AND i.flags IN (0,4) AND hg.hostid=i.hostid AND hg.hostid=h.hostid AND ia.itemid=i.itemid AND i.status=0 AND ir.state=1

    ReplyDelete