Grafana-zabbix: [feature request] Worldmap panel integration

[floppy84]

Is it possible to add support of worldmap plugin for zabbix datasource ?

[alexanderzobnin]

Can you describe in details, please, what exactly do you want to get?

[floppy84]

hi, i would like to retrieve from zabbix, some information of geolocalisation from inventory. And the. Display localisation of each devices on worldmap panel

Florian cavaillou

Le 27 sept. 2016 à 21:24, Alexander Zobnin [email protected] a écrit :

Can you describe in details, please, what exactly do you want to get?

—
You are receiving this because you authored the thread.
Reply to this email directly, view it on GitHub, or mute the thread.

[alexanderzobnin]

Ok, I understand. Not sure when I can deal with it, but idea looks interesting.

[frcrodrig]

Hi floppy84,
Since Grafana have mysql datasource if your zabbix backend use mysql database you can use this worrkaround. Create a geohash object and store it in host_inventory table (I have used location field). Configure Worldmap in table mode and then you could use a similar query to get data:

select it.name as item_name, it.itemid, h.name as hostname, i.location as geohash, i.location_lat, i.location_lon, ht.value as metric
from hosts h, host_inventory i, items it, history ht
where it.hostid=h.hostid and it.name like '%icmp%' and (h.name='host1' or h.name='host2')
and h.hostid=i.hostid and ht.itemid=it.itemid and it.itemid=655174
and clock between unix_timestamp('2018-04-14 20:30:00') and unix_timestamp('2018-04-14 20:45:00');

Worldmap in table mode need 3 objects metric, geohash and the last but not necesarry a label i have used hostname.

[alexanderzobnin]

@frcrodrig good workaround :+1: I really like the power of SQL data sources in Grafana.

[cah-adean]

Integration would be fantastic, but a SQL workaround works as well. If I simply want to get the name of the host and it's geohash mapped on the map, what would a query look like for that? I am simply mapping the locations of the hardware, no metrics required. The geohash is stored in the inventory as site_country

[alexanderzobnin]

Seems, it's possible, you should mark location field as _geohash_.

[alexanderzobnin]

I've made an example:
http://play.grafana-zabbix.org/d/WiU0SI7mz/worldmap-panel-integration-via-sql?orgId=2

[pawelkoston]

Thank You @alexanderzobnin for the example.
The configuration shown in the example works with the v0.0.21 version of grafana/worldmap-panel, the newer versions of this plugin (1.X) does not work.

[christianhals]

Has there been any progress around this feature request? What I would like to do is to populate the Worldmap panel with 3 different statuses.

Green = Host UP and no active alarms/event
Yellow = Host UP and active alarms/events
Red = Host DOWN

I can get the geohash values from MySQL and populate the map, but I´m not sure how to get the metrics correct or if this is even possible with the MySQL integration? I have an ICMP-PING item in all my host and I can see the numeric metrics for this via the Zabbix plugin.

[jothoma1]

I'm also interested 👍 any news ?

[juniorjones18]

Hi floppy84,
Since Grafana have mysql datasource if your zabbix backend use mysql database you can use this worrkaround. Create a geohash object and store it in host_inventory table (I have used location field). Configure Worldmap in table mode and then you could use a similar query to get data:

select it.name as item_name, it.itemid, h.name as hostname, i.location as geohash, i.location_lat, i.location_lon, ht.value as metric
from hosts h, host_inventory i, items it, history ht
where it.hostid=h.hostid and it.name like '%icmp%' and (h.name='host1' or h.name='host2')
and h.hostid=i.hostid and ht.itemid=it.itemid and it.itemid=655174
and clock between unix_timestamp('2018-04-14 20:30:00') and unix_timestamp('2018-04-14 20:45:00');

Worldmap in table mode need 3 objects metric, geohash and the last but not necesarry a label i have used hostname.

I don't have much experience with DB or MySQL then I Would like to understand How can I create a geohash object?

[lsa1es]

SELECT h.name, h.available as metric, i.location_lat as latitude, i.location_lon as longitude, i.site_country FROM host_inventory as i, hosts as h WHERE i.hostid=h.hostid AND i.location_lon!=''

using lat e Lon from zabbix, setup to coordinates

[lsa1es]

SELECT h.name, i.notes as triggers, i.location_lat as latitude, i.location_lon as longitude, i.site_country
FROM host_inventory as i, hosts as h
WHERE i.hostid=h.hostid AND i.location_lon!=''

[aalmarshad]

this Query worked successfully with me.

( SELECT h.name, h.available as triggers, i.location_lat as latitude, i.location_lon as longitude, i.site_country
FROM host_inventory as i, hosts as h
WHERE i.hostid=h.hostid AND i.location_lon!='')

and I configured the Threshold Options with 1.2

[glardz98]

hello,

i try to do a sql request in zabbix database for have (host, trigger information), available and severity and (lat long), with this three information i could use worldmap in grafana,

host | trigger information | trigger information | lat | long
-- | -- | -- | -- | --
titi | host is unreachable for 5 min |   | 50.33 | 3.2535
toto |   |   | 67 | 9.7890

my current sql query:

SELECT host, available
FROM hosts
LEFT JOIN hosts_groups ON (hosts.hostid = hosts_groups.hostid AND hosts.host NOT LIKE '%Template%')
LEFT JOIN hstgrp ON (hstgrp.groupid = hosts_groups.groupid AND hstgrp.name = 'SEKURIT')
LEFT JOIN host_inventory ON (host_inventory.hostid = hosts.hostid)
LEFT JOIN items i ON (i.hostid = hosts.hostid)
INNER JOIN functions f ON (f.itemid = i.itemid)

INNER JOIN triggers t ON (t.triggerid = f.triggerid AND t.value=1)

INNER JOIN events e ON (e.objectid = t.triggerid)

GROUP BY hosts.hostid;

thanks for you any information

[mmszxc]

I am also confused about this issue. How to integrate zabbix data source into grafana。
Not just asking for MySQL data.