cmsinformatica
commented
1 year ago Esta é uma consulta que escrevi para integração com o banco de dados Zabbix, pode ser o que você está procurando. Ele selecionar os hosts e grupos de hosts e, em seguida, seleciona eventos não resolvidos para esses hosts (reconhecidos ou não). Os status do host são aqueles habilitados (0) ou em um estado inacessível (2). Quando um evento obtém o status "resolvido", ele sai dessa lista.
select
h.name as hostname, h.status as hoststatus, g.name as groupname, hi.alias as hostalias, hi.location as hostlocation, hi.os as hostos, dt.idescription as itemdescription, dt.ikey as itemkey_, dt.iname as itemname, dt.hsurl as httpstepurl, dt.hsname as httpstepname, dt.tcomments as triggercomments, dt.tdescription as triggerdescription, dt.tpriority as triggerpriority, dt.eventclock as eventclock, dt.eacknowledged as eventacknowledged
from hosts h inner join hosts_groups hg on h.hostid=hg.hostid inner join groups g on hg.groupid = g.groupid left join host_inventory hi on h.hostid=hi.hostid
LEFT JOIN (SELECT i.hostid as ihostid, i.itemid as iitemid, i.description as idescription, i.key_ as ikey, i.name as iname, hs.url as hsurl, hs.name as hsname, t.description as tdescription, t.url as turl, t.comments as tcomments, t.priority as tpriority, from_unixtime(e.clock) as eventclock, e.acknowledged as eacknowledged from items i left join functions f on i.itemid = f.itemid left join triggers t on f.triggerid = t.triggerid right join events e on t.triggerid = e.objectid left join httpstepitem hsi on i.itemid = hsi.itemid left join httpstep hs on hsi.httpstepid = hs.httpstepid inner join problem p on e.eventid = p.eventid