query to obtaint triggers per host

[angelhc]

Hi all,

We need a Mysql query to obtain which triggers are associated with each host but can¡t find any relation on the database model to obtain it.
My first approach is:

select i.description
from zabbix.triggers i
(select h.hostid, h.host
from zabbix.groups g, zabbix.hosts_groups hg, zabbix.hosts h
where g.groupid = hg.groupid
and hg.hostid = h.hostid
and g.name='New_hosts') H
where i.hostid = H.hostid;

But unfortunality it doesn't works cause there's no relation between hosts and triggers on my query.

Any help?
Thanks!

[angelhc]

Sorry but after a few test it's solved:

SELECT DISTINCT host, t.description, f.triggerid, e.acknowledged, t.value, i.lastvalue FROM triggers t INNER JOIN functions f ON ( f.triggerid = t.triggerid ) INNER JOIN items i ON ( i.itemid = f.itemid ) INNER JOIN hosts ON ( i.hostid = hosts.hostid ) INNER JOIN events e ON ( e.objectid = t.triggerid ) WHERE (e.eventid DIV 100000000000000) IN (0) AND (e.object-0)=0 AND t.value =0 AND hosts.status =0 AND i.status =0 AND t.status =0 GROUP BY f.triggerid ORDER BY t.lastchange DESC;

As I see it returns all the triggers associated with each host and it's last value.
Is it correct?

Any help to obtain the host group on the same query?

Thanks!

[angelhc]

Solved again :

SELECT DISTINCT host, g.name, t.description, f.triggerid
FROM triggers t INNER JOIN functions f ON ( f.triggerid = t.triggerid )
INNER JOIN items i ON ( i.itemid = f.itemid )
INNER JOIN hosts h ON ( i.hostid = h.hostid )
INNER JOIN events e ON ( e.objectid = t.triggerid )
INNER JOIN hosts_groups hg ON ( hg.hostid = h.hostid )
INNER JOIN groups g ON ( g.groupid = hg.groupid )
WHERE (e.eventid DIV 100000000000000) IN (0)
AND (e.object-0)=0 AND t.value =0
AND h.status =0 AND i.status =0
AND t.status =0
GROUP BY f.triggerid
ORDER BY t.lastchange DESC;


With this query we can obtain which triggers are associated with each host and the host_group where the hosts are.

Regards.

[danvaught1]

Mysql query to obtain triggers status per host

Hi all,

We need a Mysql query to obtain which triggers are associated with each host and what is the Current status of those triggers. i.e Enabled or Disabled ?.

I check the mysql database but i do not found any Foreign key in Triggers table through which we retrieve the required data.

I have written query for identifying items which are disabled. But same thing i want to do with triggers...

[danvaught1]

Hi All;

After lots of Try now i am able to retrieve the data for hosts on which Triggers are Disabled

SELECT DISTINCT host, g.name, t.description, f.triggerid FROM triggers t INNER JOIN functions f ON ( f.triggerid = t.triggerid ) INNER JOIN items i ON ( i.itemid = f.itemid ) INNER JOIN hosts h ON ( i.hostid = h.hostid ) INNER JOIN events e ON ( e.objectid = t.triggerid ) INNER JOIN hosts_groups hg ON ( hg.hostid = h.hostid ) INNER JOIN groups g ON ( g.groupid = hg.groupid ) WHERE t.status =1