Tutorial: Observing Zabbix events on a Geospatial Map
Mixed Grafana datasource
Create a mixed datasource:
Datasource 1: Mysql
Let's assume you keep coordinates, child/parent relations, and other important hosts information in a separate DB. Switch to 'Code' mode from 'Builder' and make a similar query:
SELECT sw.id switchId, sw.addr switchIp, sw.vendor_id vendorId, sw.topology_level topologyLevel, c.name city, s.name street, h.name house, h.id houseId, psw.id parentSwitchId, psw.addr parentSwitchIp, ST_X(h.coordinate) longitude, ST_Y(h.coordinate) latitude, sw.name description, d.name FROM TCC.switch_a sw LEFT JOIN TCC.switch_a psw ON psw.id = sw.parent_switch_id LEFT JOIN TCC.houses_new h ON h.id = sw.house_id LEFT JOIN TCC.district_street ds ON ds.id = h.district_street LEFT JOIN TCC.streets_new s ON s.id = ds.street_id LEFT JOIN TCC.districts d ON d.id = ds.district_id LEFT JOIN TCC.cities c ON d.city_id = c.id WHERE sw.is_deleted = 0 AND psw.is_deleted = 0 AND coordinate is not null AND d.id = district_id AND c.name = "$city" OR IF("$city"="All",1,0) = 1 AND sw.is_deleted = 0 AND psw.is_deleted = 0 AND coordinate is not null AND d.id = district_id
- This information could as well be fetched directly from Zabbix API or Zabbix Mysql DB, if you have coordinates and other info filled out in hosts inventory.
$city - is a sample Grafana variable you can set in dashboard options to group your points in every query. Its value options can be also automatically queried from the same datasource:
SELECT name FROM TCC.cities UNION SELECT 'All' AS name;
Datasource 2: Zabbix Mysql DB
Get problem events with messages (acknowledges), severity metrics from Zabbix Mysql DB:
SELECT switchIp, status, clock as lastUpd, message, COALESCE(severity, 0) as severity, event FROM ( SELECT hosts.host as switchIp, CASE WHEN triggers.value = 1 THEN 0 ELSE 1 END AS status , events.clock as clock, a.message AS message, p.severity as severity, events.name as event FROM events JOIN triggers ON events.objectid = triggers.triggerid JOIN functions ON functions.triggerid = triggers.triggerid JOIN items ON items.itemid = functions.itemid JOIN hosts ON items.hostid = hosts.hostid JOIN problem p on events.eventid = p.eventid LEFT JOIN acknowledges a on events.eventid = a.eventid WHERE events.source = 0 AND p.severity > 2 AND triggers.value = 1 UNION SELECT hosts.host as switchIp, history_uint.value as status, history_uint.clock as clock, '' AS message, null AS severity, null AS event FROM hosts, items, history_uint WHERE hosts.hostid = items.hostid and items.itemid = history_uint.itemid and items.name = 'ICMP ping' and history_uint.clock between (unix_timestamp() - 90) and unix_timestamp() ) as combined_result ORDER BY clock DESC;
- 'Union' operation is intended to fill in the information about hosts, that are not covered by the 'problem' table. We're going to need this in order to transform our Grafana mixed datasource with INNER JOIN by the location name (switchIp) field
- 'Coalesce' for 'severity' sets null values to 0. It makes all severity values numeric, so that you can set color thresholds for these values.
Choose 'Join by field' mode: "INNER", and select location name field (switchIp)