There is a known trigger. According to it, we can get a group of equipment and host:

mysql> select groups.groupid,hosts.hostid from triggers inner join functions on triggers.triggerid=functions.triggerid inner join items on items.itemid=functions.itemid inner join hosts on hosts.hostid=items.hostid inner join hosts_groups on hosts_groups.hostid=hosts.hostid inner join groups on groups.groupid=hosts_groups.groupid where triggers.triggerid=15266; +---------+--------+ | groupid | hostid | +---------+--------+ | 10 | 10250 | | 24 | 10250 | +---------+--------+

We also have a table of actions, from where we can take which users and which groups to notify in case of problems:

 mysql> select actions.actionid,actions.name,opmessage_usr.userid,opmessage_grp.usrgrpid from opmessage inner join operations on operations.operationid=opmessage.operationid inner join actions on actions.actionid=operations.actionid left join opmessage_usr on opmessage_usr.operationid=operations.operationid left join opmessage_grp on opmessage_grp.operationid=operations.operationid left join opcommand_hst on opcommand_hst.operationid=operations.operationid left join opgroup on opgroup.operationid=operations.operationid left join opcommand_grp on opcommand_grp.operationid=operations.operationid left join optemplate on optemplate.operationid=operations.operationid left join conditions on conditions.actionid=operations.actionid limit 10; +----------+------------------------------------------------+--------+----------+ | actionid | name | userid | usrgrpid | +----------+------------------------------------------------+--------+----------+ | 3 | Report problems to Zabbix administrators | NULL | 7 | | 3 | Report problems to Zabbix administrators | NULL | 7 | | 4 | Report not supported items | NULL | 7 | | 5 | Report not supported low level discovery rules | NULL | 7 | | 6 | Report unknown triggers | NULL | 7 | | 8 | Проблемы с КТВ | 3 | NULL | | 8 | Проблемы с КТВ | 3 | NULL | | 10 | Проблемы в уцвц | NULL | 13 | | 10 | Проблемы в цвцув | NULL | 13 | | 11 | Для сисадминов | NULL | 18 | +----------+------------------------------------------------+--------+----------+

I can not find from which table to take data in order to link this data. Those. to whom notifications are sent in case of triggering a trigger.

    1 answer 1

    Something in this spirit:

     select userid from (select users_groups.userid from operations inner join opmessage_grp on opmessage_grp.operationid=operations.operationid inner join users_groups on opmessage_grp.usrgrpid=users_groups.usrgrpid where operations.actionid in (select actionid from conditions where conditiontype=2 and value=15266 union all select actionid from conditions where conditiontype=0 and value in (SELECT groups.groupid FROM triggers INNER JOIN functions ON triggers.triggerid=functions.triggerid INNER JOIN items ON items.itemid=functions.itemid INNER JOIN hosts ON hosts.hostid=items.hostid INNER JOIN hosts_groups ON hosts_groups.hostid=hosts.hostid INNER JOIN groups ON groups.groupid=hosts_groups.groupid WHERE triggers.triggerid=15266) union all select actionid from conditions where conditiontype=1 and value in (SELECT hosts.hostid FROM triggers INNER JOIN functions ON triggers.triggerid=functions.triggerid INNER JOIN items ON items.itemid=functions.itemid INNER JOIN hosts ON hosts.hostid=items.hostid INNER JOIN hosts_groups ON hosts_groups.hostid=hosts.hostid INNER JOIN groups ON groups.groupid=hosts_groups.groupid WHERE triggers.triggerid=15266)) union all select opmessage_usr.userid from operations inner join opmessage_usr on opmessage_usr.operationid=operations.operationid where operations.actionid in (select actionid from conditions where conditiontype=2 and value=15266 union all select actionid from conditions where conditiontype=0 and value in (SELECT groups.groupid FROM triggers INNER JOIN functions ON triggers.triggerid=functions.triggerid INNER JOIN items ON items.itemid=functions.itemid INNER JOIN hosts ON hosts.hostid=items.hostid INNER JOIN hosts_groups ON hosts_groups.hostid=hosts.hostid INNER JOIN groups ON groups.groupid=hosts_groups.groupid WHERE triggers.triggerid=15266) union all select actionid from conditions where conditiontype=1 and value in (SELECT hosts.hostid FROM triggers INNER JOIN functions ON triggers.triggerid=functions.triggerid INNER JOIN items ON items.itemid=functions.itemid INNER JOIN hosts ON hosts.hostid=items.hostid INNER JOIN hosts_groups ON hosts_groups.hostid=hosts.hostid INNER JOIN groups ON groups.groupid=hosts_groups.groupid WHERE triggers.triggerid=15266))) as us group by userid
    • by known triggerid get a list of users to alert