Help with SQL for Grafana

[victorbrca]

Hello eveyone,

Brand new user here. I'm testing Zabbix at home to learn a bit more and try and implement it at work. My goal is to have the monitoring done by Zabbix and Grafana plugged into for the reports. I'm still playing around with options to see what I can do and what will be useful for me.

One of the main things I would like to do is have an internal status page displaying our servers and the health status of each service (similar to status.io or Grafana's WordPing plugin). I might be going the wrong way here, but what I have done so far was setup a few devices with ICMP and HTTP monitors. I have Grafana connected in via API and MySQL, but I figured the best way to get the info would be via SQL.

I wanted to have a Grafana table displaying in the following format:

+------------------+----------------+
| Server           | Ping   | HTTP  |
+------------------+----------------+
| Server 1         | Ok     |  Ok   |
| Server 2         | Ok     |  Down |
+------------------+----------------+

And I'm pretty close, but I can' t get conditional summing setup right.

select h.name,t.description,t.value from items i, hosts h, triggers t, functions f
  where i.hostid=h.hostid and
  t.triggerid=f.triggerid and
  f.itemid=i.itemid and
  h.name in ('MotionEye Alerts', 'FreeNAS', 'Front Camera') and
  t.description in ('HTTP service is down on {HOST.NAME}', 'Unavailable by ICMP ping');

Gives me

+------------------+-----------------------------------+-------+
| name             | description                       | value |
+------------------+-----------------------------------+-------+
| FreeNAS          | Unavailable by ICMP ping          | 0     |
| FreeNAS          | HTTP service is up on {HOST.NAME} | 1     |
| Front Camera     | Unavailable by ICMP ping          | 0     |
| Front Camera     | HTTP service is up on {HOST.NAME} | 1     |
| MotionEye Alerts | Unavailable by ICMP ping          | 1     |
| MotionEye Alerts | HTTP service is up on {HOST.NAME} | 0     |
+------------------+-----------------------------------+-------+

I'd appreciate any feedback or help with the SQL. Maybe there's another/easier way of doing this.

Thanks.

[DmitryL]

Hi there,
I would use Zabbix plugin for grafana https://grafana.com/plugins/alexanderzobnin-zabbix-app
Works via API, which means that all queries will be optimized for Zabbix.