SLA report using SQL query

[Vermizz]

Hi,
I need to extract historical data from events along with their closing time (durration time) for a given host_group and selected tag. I wrote a SQL query, that pulls all the data, for me but I can't calculate the durration time against the e.clock column.

I know, I should do diffdate on e.clock if e.value changes from 1 to 0 then subtract e.clock for r_eventid - eclock for source eventid.
I tried to use API but also don't know how to calculate durration time for resolved event.
How zabbix calculates the duration time for resolved event?
Does anyone know how to modify my query to get 3 additional column: start problem time, resolve problem time and duration time?

For workaround problem, I use GUI: Problems-> History-> Export to csv (button) but I want to automate this task. Can I somehow schedule this export?

select h.hostid, i.itemid, i.NAME AS iname, f.triggerid, FROM_UNIXTIME(e.clock)AS eclock, e.eventid, e.objectid, e.VALUE, er1.r_eventid
#h.NAME, a.subject, FROM_UNIXTIME(a.clock) AS aclock
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
join triggers t on f.triggerid = t.triggerid
join hosts_groups hg On h.hostid = hg.hostid
JOIN item_tag it ON i.itemid = it.itemid
left JOIN alerts a ON a.eventid = e.eventid
left JOIN event_recovery er1 ON e.eventid=er1.eventid
WHERE
e.clock > UNIX_TIMESTAMP(DATE_SUB(CURDATE(),INTERVAL 6 MONTH)) AND
hg.groupid = 252 AND
it.value IN ('ICMP', 'Zabbix agent') AND
i.NAME IN ('Agent ping', 'ICMP ping') AND
e.value in ('0','1')
#AND a.eventid = e.eventid
order BY
  e.clock DESC
LIMIT 200;

​

[ridonekorkmaz]

Hello,
We wanted to do same thing. We were only achieved to write a query that event_id, start_event_time, recovery_event_time on the same row. But we could not able to add hosts, objects etc.
Your query helped us too much, thank you posting your it here.
Finally we managed to write SLA report combining both queries.
We post here, may be helpful for someone.

Note: WHERE condition must be changed according your needs.

-- Final report (SLA Report)
select q.host_name,
q.ip,
q.location,
q.item_name,
q.triggerid,
q.trigger_name,
sum(recovery_duration_seconds) sum_recovery_duration_seconds
from
(select distinct h.name as host_name,
ifc.ip,
hi.location,
i.name as item_name,
f.triggerid,
e.objectid,
e.eventid,
er1.r_eventid,
e."name" as trigger_name,
case e.value
when 1 then 'RESOLVED' when 0 then 'PROBLEM' end as "status",
to_timestamp(e.clock) as start_time,
to_timestamp(e2.clock) as recovery_time,
case when e2.clock is null then (extract(epoch from now()) - e.clock) when e2.clock is not null then ((select clock from events e2 where e2.eventid = er1.r_eventid) - e.clock) end as recovery_duration_seconds

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)

join triggers t on (f.triggerid = t.triggerid)

join hosts_groups hg on (h.hostid = hg.hostid)

join item_tag it on (i.itemid = it.itemid)

left join alerts a on (a.eventid = e.eventid)

left join event_recovery er1 on (e.eventid = er1.eventid)

left join events e2 on (e2.eventid = er1.r_eventid)

left join interface ifc on (ifc.hostid = h.hostid)

left join host_inventory hi on (hi.hostid = h.hostid)

where hg.groupid = 252
and it.value IN ('ICMP', 'Zabbix agent')
and i.name IN ('Agent ping', 'ICMP ping')
and e.value in ('0', '1')
) q

group by q.host_name, q.ip, q.location, q.item_name, q.triggerid, q.trigger_name
order by 7 desc, 2, 3, 4, 5, 6


-- Raw data, for check, to be sure
select distinct h.name as host_name,
       ifc.ip,
       hi.location,
       i.name as item_name,
       f.triggerid,
       e.objectid,
       e.eventid,
       er1.r_eventid,
       e."name" as trigger_name,
       case e.value

            when 1  then 'RESOLVED'

            when 0  then 'PROBLEM'

             end as "status",

       to_timestamp(e.clock) as start_time,

       to_timestamp(e2.clock) as recovery_time,

       case
                    when e2.clock is null     then (extract(epoch
                                                        from now()) - e.clock)

            when e2.clock is not null then (
                                                        (select clock
                                                         from events e2
                                                         where e2.eventid = er1.r_eventid) - e.clock)

             end as recovery_duration_seconds

  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

  join triggers t
    on f.triggerid = t.triggerid

  join hosts_groups hg
    on h.hostid = hg.hostid

  join item_tag it
    on i.itemid = it.itemid

  left join alerts a
    on a.eventid = e.eventid

  left join event_recovery er1
    on e.eventid = er1.eventid

  left join events e2
    on e2.eventid = er1.r_eventid

  left join interface ifc
    on ifc.hostid = h.hostid

  left join host_inventory hi
    on hi.hostid = h.hostid

 where hg.groupid = 252

   and it.value IN ('ICMP', 'Zabbix agent')

   and i.name IN ('Agent ping', 'ICMP ping')

   and e.value in ('0', '1')

 order by 7 desc, 2, 3, 4, 5, 6​

[Vermizz]

Hi,
Thanks for submitting the SQL query. Greate job

Personally, I finally did it using the API and a Python script.
Script available on my github: https://github.com/Vermiz/Zabbix/tre...eports/KPI_SLA

[ridonekorkmaz]

Thank you for sharing, great. We need a modification, so instead of modifiying SQL, we will use your Python script, if you kindly have consent.