Query: Trigger age

[bashman]

I would like to query Zabbix DB to get the age of all corrected triggers in order to know how long did the problem take to get solved, during last month.

[zabbix_zen]

Hola Bashman.

This post show how to query the *currently* active Triggers showing how long they're already active.

It should get you in the right direction with some slight modifications.

[bashman]

Obrigado zabbix_zen!,

I'm getting close to what I want, I already have this:

SELECT DISTINCT t.triggerid, h.host, t.description, FROM_UNIXTIME(UNIX_TIMESTAMP(NOW()) - i.lastclock, '%h:%i:%s'), t.value
FROM   triggers t
       INNER JOIN functions f ON ( f.triggerid = t.triggerid )
       INNER JOIN events e ON ( e.objectid = t.triggerid )
       INNER JOIN items i ON ( i.itemid = f.itemid )
       INNER JOIN hosts h ON ( i.hostid = h.hostid )
       INNER JOIN hosts_groups hg ON ( hg.hostid = h.hostid )
       INNER JOIN groups g ON ( hg.groupid = g.groupid )
WHERE  t.value IN (0, 1)
       AND h.status = 0
       AND i.status = 0
       AND t.status = 0
       AND t.priority > 2
       AND e.acknowledged = 0
       AND i.lastclock >= UNIX_TIMESTAMP(NOW() - INTERVAL 9 MINUTE)
       AND e.eventid = (SELECT max(eventid)
                        FROM   events e
                        WHERE  t.value IN (0, 1)
                               AND h.status = 0
                               AND i.status = 0
                               AND t.status = 0
                               AND e.objectid = t.triggerid)
       AND g.name = "Linux"
       ORDER BY h.host, i.lastclock DESC;

But I would like to get the trigger duration, the time a trigger has been "on".

[gessel]

Calculated event age, formatted as days hours:minutes

SELECT DISTINCT host, p.ip, t.description,
CONCAT(
FLOOR(HOUR(TIMEDIFF(NOW(), FROM_UNIXTIME(e.clock))) / 24), ' days ',
MOD(HOUR(TIMEDIFF(NOW(), FROM_UNIXTIME(e.clock))), 24), ':',
LPAD(MINUTE(TIMEDIFF(NOW(), FROM_UNIXTIME(e.clock))), 2, '0')) as time 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 interface p ON ( h.hostid = p.hostid )
INNER JOIN events e ON ( e.objectid = t.triggerid )
LEFT JOIN acknowledges a ON ( a.eventid = e.eventid )
WHERE (e.eventid DIV 100000000000000)
IN (0)
AND e.object = 0
AND (t.value=1 OR (t.value =0 AND unix_timestamp(now()) - t.lastchange <60))
AND h.status = 0
AND i.status = 0
AND t.status = 0
AND t.priority > 3
AND e.acknowledged = 0
AND e.eventid = (SELECT max(eventid)
FROM events e
WHERE (e.eventid DIV 100000000000000)
IN (0)
AND e.object = 0
AND (t.value=1 OR (t.value =0 AND unix_timestamp(now()) - t.lastchange <60))
AND h.status = 0
AND i.status = 0
AND t.status = 0
AND e.acknowledged = 0
AND e.objectid = t.triggerid
)
GROUP BY host, f.triggerid
ORDER BY e.clock DESC;

[fgo.oliver]

hello Gessel, can you help me?
I need the same, but, for now, i'm searching for a query that bring the event duration for a x trigger...

for example, trigger activate today 10am and back up at 11am, the duration in this case was 60 minutes...

please, help me...

[fgo.oliver]

Look, I have this post https://www.zabbix.com/forum/zabbix-...r-get-duration

[gessel]

You'd just use time math compared to (NOW) https://dev.mysql.com/doc/refman/8.0...functions.html to find the duration of unresolved events. I'm not sure how to recreate the "history" table with the resolved problems and duration it was a problem. I'd think this data would be in https://zabbix.org/wiki/Docs/DB_sche...event_recovery but there's no clock value in that table. But as you will find, there's zero documentation about what these values mean. Each of the table descriptions is "This table contains [please add a brief table description]." The best I can find at the moment is the acknowledge time. https://zabbix.org/wiki/Docs/DB_schema/4.0/acknowledges (a nicely commented table page - thanks to whoever provided the detail)