I have the same exact issue. I want to create a simple dashboard that shows in easy to read stats the availability of hosts and/or services running on the hosts.
So let’s say we have 300 servers of which at any given time most are up’n running, but some may be unavailable. I’d like to be able to visualise a single or two separate panels showing a how many hosts are available and how many are not.
I’ve been going through transformations, but I can’t seem to be able to make it happen. Instead of a single panel with a single number of hosts, I get a panel with a gazillion hosts all showing either a 1 or a 0 for availability.
I’m also using Zabbix as a source for Grafana.
If anyone has any ideas on how to accomplish this, please let me know.
I performed the visualization of the triggers by group, however, for this it was necessary to use database queries, it is possible to do what you are requesting, however, it will have to be through queries in the database. Hope I helped!
I’ll see if I can get the code to pull this data, but I don’t promise, I came here just to shed some light…
below I will leave an image that deals with the triggers I pulled through the database
I will send it here, pay attention to what I write and let me know if you have any questions.
Remembering that:
1° The Datasource needs to be your Database
2° There must be a variable with the name “group” that takes all the zabbix groups or the groups you want
3° The priorities in the SQL code will be defined as:
0 - No
1 - Information
2 - Warning
3 - Average
4 - High
5 - Disaster
4° It is also necessary to use the “Stats” dashboard
…
Below I will send the SQL code and the place where there will be a replacement
SELECT
COUNT(DISTINCT triggers.triggerid) AS metric
FROM
hstgrp
LEFT JOIN hosts_groups
ON hstgrp.groupid = hosts_groups.groupid
LEFT JOIN hosts
ON hosts_groups.hostid = hosts.hostid
LEFT JOIN items
ON hosts.hostid = items.hostid
LEFT JOIN functions
ON items.itemid = functions.itemid
LEFT JOIN triggers
ON functions.triggerid = triggers.triggerid
WHERE
hstgrp.name = ‘$group’ AND
hosts.status = 0 AND
hosts.maintenance_status = 0 AND
items.status = 0 AND
triggers.status = 0 AND
triggers.value = 1 AND
triggers.priority = 1; // Change the trigger priority as desired, from 0 to 5 as explained above.
Thanks for the SQL-code! That helped me move on quite a bit.
Just a quick question:
The panel “Hosts Window Servers” with the pie chart… How did you pull those numbers from the database? I’ve used the code you posted here and tweaked it a bit to get host count instead of trigger count, but I cannot seem to be able to figure out how to get the number of OK-hosts.
So I get the number of the 20 hosts in a group correctly. There are 4 averages and 2 warnings which are correct. But my OK comes out as 19 out of 20 instead of 16. So that’s obviously wrong.
Your observation is interesting, however, there is a difference in this dashboard.
This Donut dashboard doesn’t show the alerts but the hosts that are alerting, so it contains “Ok” first, for example.
In this dashboard I made, I made a point of showing the alarms and number of hosts that are alarming with the severities.
Just below I made a dashboard that shows the name of the servers and what alarms they have, and clicking on any one is redirected to a dashboard for that specific host.
Referring to the code of this pizza (or donut) dashboard, I will send you here.
Yours sincerely!
…
SELECT
UNIX_TIMESTAMP(NOW()) as time_sec,
name as metric,
IFNULL(count,0) as value
FROM
(SELECT 0 as priority, ‘1 - Not classified’ as name
UNION SELECT 1, ‘2 - Information’
UNION SELECT 2, ‘3 - Warning’
UNION SELECT 3, ‘4 - Average’
UNION SELECT 4, ‘5 - High’
UNION SELECT 5, ‘6 - Disaster’
UNION SELECT 6, ‘0 - OK’) as tmp1
LEFT JOIN (SELECT IFNULL(tmp.priority,6) as priority, count(h.hostid) as count
FROM hstgrp g, hosts_groups hg, hosts h
LEFT JOIN (SELECT h.hostid, max(priority) as priority FROM hstgrp g, hosts_groups hg, hosts h, items i, functions f, triggers t
LEFT JOIN trigger_depends td ON t.triggerid=td.triggerid_down
LEFT JOIN (SELECT triggerid, value FROM triggers WHERE status=0) as t2 ON td.triggerid_up=t2.triggerid
WHERE g.groupid=hg.groupid and
hg.hostid=h.hostid and
h.hostid=i.hostid and
i.itemid=f.itemid and
f.triggerid=t.triggerid and
h.status=0 and
h.maintenance_status=0 and
i.status=0 and
t.status=0 and
t.value=1 and
(t2.value=0 or t2.value is null)
GROUP BY h.hostid) as tmp ON h.hostid=tmp.hostid
WHERE
g.groupid=hg.groupid and
hg.hostid=h.hostid and
g.name = ‘$grupo’ and
h.status=0
GROUP BY
tmp.priority) as tmp2 ON tmp1.priority=tmp2.priority
ORDER BY
metric;
