Hi guys.
I need a support on how I can make a report to calculate the time difference between tuples (occurrence of incidents and the solution of these incidents) of a date field in MySQL, as shown in the example from image over below:
I can calculate the first date and second date in my report.
2020-05-21 15:43:03
2020-05-21 11:09:23
I'm using the SQL below on a Zabbix basis, and in this example, I'm looking for a “Circuit - LDC 60622” circuit to better elucidate the case:
use zabbix;
SELECT e.clock as event_ts_epoch,
From_unixtime(e.clock) as event_ts,
e.ns as event_age_ns,
e.eventid as event_id,
e.objectid as event_objetctid,
h.hostid as hostid,
h.host as host_name,
ip.ip as host_ip,
i.name as item_name,
IFNULL(ELT(FIELD(t.priority,0, 1, 2, 3, 4, 5),‘not classified’,‘information’,‘warning’,‘average’, ‘high’,‘disaster’),‘unknown’) AS trigger_severity,
IFNULL(ELT(FIELD(t.value,0, 1),‘ok’,‘problem’),‘unknown’) AS trigger_value,
IF(e.acknowledged=1,a.message,“N/A”) as event_msg
FROM zabbix.hosts h
INNER JOIN zabbix.hosts_groups hg ON (hg.hostid= h.hostid)
INNER JOIN zabbix.interface ip ON ( h.hostid = ip.hostid )
INNER JOIN zabbix.host_inventory inv ON ( h.hostid = inv.hostid )
INNER JOIN zabbix.items i ON ( i.hostid = h.hostid )
INNER JOIN zabbix.functions f ON ( f.itemid = i.itemid )
INNER JOIN zabbix.triggers t ON ( t.triggerid = f.triggerid ),
zabbix.events e LEFT JOIN zabbix.alerts a ON ( e.eventid = a.eventid )
WHERE e.objectid = t.triggerid
AND t.priority IN (‘3’,‘4’,‘5’)
AND hg.groupid=‘98’
AND From_unixtime(e.clock) BETWEEN ‘2020-05-01 00:00:00’ AND ‘2020-05-31 23:59:59’
AND h.host =‘Circuito - LDC 60622’
ORDER BY e.clock DESC
I need a support on how I can make a report to calculate the time difference between tuples (occurrence of incidents and the solution of these incidents) of a date field in MySQL, as shown in the example from image over below:
I can calculate the first date and second date in my report.
2020-05-21 15:43:03
2020-05-21 11:09:23
I'm using the SQL below on a Zabbix basis, and in this example, I'm looking for a “Circuit - LDC 60622” circuit to better elucidate the case:
use zabbix;
SELECT e.clock as event_ts_epoch,
From_unixtime(e.clock) as event_ts,
e.ns as event_age_ns,
e.eventid as event_id,
e.objectid as event_objetctid,
h.hostid as hostid,
h.host as host_name,
ip.ip as host_ip,
i.name as item_name,
IFNULL(ELT(FIELD(t.priority,0, 1, 2, 3, 4, 5),‘not classified’,‘information’,‘warning’,‘average’, ‘high’,‘disaster’),‘unknown’) AS trigger_severity,
IFNULL(ELT(FIELD(t.value,0, 1),‘ok’,‘problem’),‘unknown’) AS trigger_value,
IF(e.acknowledged=1,a.message,“N/A”) as event_msg
FROM zabbix.hosts h
INNER JOIN zabbix.hosts_groups hg ON (hg.hostid= h.hostid)
INNER JOIN zabbix.interface ip ON ( h.hostid = ip.hostid )
INNER JOIN zabbix.host_inventory inv ON ( h.hostid = inv.hostid )
INNER JOIN zabbix.items i ON ( i.hostid = h.hostid )
INNER JOIN zabbix.functions f ON ( f.itemid = i.itemid )
INNER JOIN zabbix.triggers t ON ( t.triggerid = f.triggerid ),
zabbix.events e LEFT JOIN zabbix.alerts a ON ( e.eventid = a.eventid )
WHERE e.objectid = t.triggerid
AND t.priority IN (‘3’,‘4’,‘5’)
AND hg.groupid=‘98’
AND From_unixtime(e.clock) BETWEEN ‘2020-05-01 00:00:00’ AND ‘2020-05-31 23:59:59’
AND h.host =‘Circuito - LDC 60622’
ORDER BY e.clock DESC