Calculating total Trigger 'problem' state time

[bturnbough]

Hi Guys,

Can someone please provide me with a query to calculate the total time a trigger has spend in a 'problem' state for, say, a week?

I have this so far:

select h.hostid, h.name, i.itemid, i.name, f.triggerid, FROM_UNIXTIME(e.clock), e.eventid, e.value, value_changed from hosts h join items i on i.hostid = h.hostid join functions f on f.itemid = i.itemid join events e on e.objectid = f.triggerid where h.name = 'pefw01' and i.name like '%ping state%' and e.value in ('0','1');


It returns this:
+--------+--------+--------+------------+-----------+------------------------+---------+-------+---------------+
| hostid | name | itemid | name | triggerid | FROM_UNIXTIME(e.clock) | eventid | value | value_changed |
+--------+--------+--------+------------+-----------+------------------------+---------+-------+---------------+
| 10146 | PEFW01 | 23405 | Ping State | 13597 | 2015-04-06 05:16:05 | 1184442 | 1 | 1 |
| 10146 | PEFW01 | 23405 | Ping State | 13597 | 2015-04-06 09:21:05 | 1185581 | 0 | 1 |
| 10146 | PEFW01 | 23405 | Ping State | 13597 | 2015-04-06 09:28:05 | 1192701 | 0 | 0 |
| 10146 | PEFW01 | 23405 | Ping State | 13597 | 2015-04-06 17:14:05 | 1201841 | 0 | 0 |
| 10146 | PEFW01 | 23405 | Ping State | 13597 | 2015-04-23 13:50:05 | 1244284 | 0 | 0 |
| 10146 | PEFW01 | 23405 | Ping State | 13597 | 2015-07-24 12:28:05 | 1474883 | 0 | 0 |
| 10146 | PEFW01 | 23405 | Ping State | 13597 | 2015-09-17 15:23:05 | 1638433 | 0 | 0 |
| 10146 | PEFW01 | 23405 | Ping State | 13597 | 2015-09-19 00:58:05 | 1644037 | 1 | 1 |
| 10146 | PEFW01 | 23405 | Ping State | 13597 | 2015-09-19 18:04:05 | 1647148 | 0 | 1 |
| 10146 | PEFW01 | 23405 | Ping State | 13597 | 2015-09-19 18:20:05 | 1650414 | 1 | 1 |
| 10146 | PEFW01 | 23405 | Ping State | 13597 | 2015-09-19 18:29:05 | 1652073 | 0 | 1 |
| 10146 | PEFW01 | 23405 | Ping State | 13597 | 2015-09-20 08:22:05 | 1663467 | 0 | 0 |
+--------+--------+--------+------------+-----------+------------------------+---------+-------+---------------+


I'm not sure how to calculate the amount of time a trigger spends in the 'problem' state.

Thanks.

[Atsushi]

You can calculate those time by IT services function of Zabbix.

Please check manual.

https://www.zabbix.com/documentation/2.2/manual/it_services

[bturnbough]

But I want it in a SQL query.

I build reports that run daily, weekly, and monthly. This would be part of that.

[bturnbough]

Additionally, I already tried that approach.

Why is it reporting 2d 7h 0m for PROBLEM, when my firewall *clearly* hasn't been inaccessible for 2d, 7h and 0m?

21 Sep 2015 00:00 25 Sep 2015 12:08 2d 5h 8m 0d 0h 0m 2d 7h 0m

[bturnbough]

And why isn't the "OK" state counter starting from the hours that I configured? M-F 7am-7pm.