zabbix+grafana查询指定指标值数据展示

通过zabbix对每台主机下的“主机通讯检测”指标监控的值进行数据展示

首先确定在zabbix能看到指标是否有实际值

 

通grafan展示上面指标的数据，包含主机组、主机名称、主机状态、指标监控状态

　　根据需求需要获取这么多字段信息从而通过SQL方式获取

　　通过sql查询对应主机名称、主机组名称、主机状态、指标vaule值，由于存在主机指标无数据需要根据nvl将空值替换，同时只获取最近5分钟指标value值

with

xx as (select hosts.name name,hosts.status status,hn.name group_name,hosts.hostid hostid from hosts

LEFT JOIN host_rtdata hr ON hosts.hostid=hr.hostid

LEFT JOIN hosts_groups hg ON hosts.hostid=hg.hostid

RIGHT  JOIN hstgrp hn ON hg.groupid=hn.groupid

where hosts.flags IN (0,4)  and hosts.status in ('0','1')),

aa as (select xx.group_name,xx.name host_name,xx.status,items.itemid itemid from xx,items where xx.hostid=items.hostid and items.flags=0 and items.name='主机通讯检测'),

bb as (select b.itemid,b.value,FROM_UNIXTIME(b.clock, '%Y-%m-%d %H:%i:%s') dates from history_uint b where FROM_UNIXTIME(b.clock, '%Y-%m-%d %H:%i:%s') >= NOW() - INTERVAL 5 MINUTE),

cc as (select aa.group_name,aa.host_name,aa.status,aa.itemid,bb.value,bb.dates from aa LEFT JOIN bb ON aa.itemid=bb.itemid),

dd as (select cc.group_name,cc.host_name,cc.status,cc.itemid,cc.value,ROW_NUMBER() OVER (PARTITION BY cc.host_name ORDER BY cc.dates DESC) rn from cc)

select dd.group_name 群组,dd.host_name 主机,dd.status 状态,nvl(replace(dd.value,'1','0'),'1') 监控状态 from dd where dd.rn='1' order by 监控状态 desc

grafana数据展示