获取 Zabbix 的仪表盘数据

一、获取主机数

select now() as time,COUNT(*) from hosts WHERE `flags` = '0' and  `status` = '0';

已启用/已禁用/模板

select `status`,
(CASE status
WHEN  '0' THEN
'已启用'
WHEN '1' THEN
'已禁用'
WHEN '2' THEN
'未知'
ELSE
'模板'
END
) 主机状态,
count(status) 主机数量 
from hosts
WHERE flags = '0'
GROUP BY status;

二、获取监控项数

select now() as time, COUNT(*) from items WHERE flags='0' and hostid in (
 select hostid from `hosts` WHERE status='0' and flags = '0'
);

已启用/已禁用/不支持

select state,
(CASE state
WHEN  '0' THEN
'已启用'
WHEN '1' THEN
'不支持'
ELSE
'未知'
END
) 监控项状态,
COUNT(state) as '监控项数量'
from items 
WHERE flags='0' and hostid in (
select hostid from `hosts` WHERE status='0' and flags = '0'
)
GROUP BY state;

三、获取触发器数

select COUNT(*) from triggers WHERE triggerid in (select objectid from events);

问题/正常

select `value`,
(CASE `value`
WHEN  '1' THEN
'问题'
ELSE
'正常'
END
) 状态,
COUNT(`value`) as '触发器数量'
from `triggers` WHERE triggerid in (select objectid from `events`) 
GROUP BY `value`;

四、自定义综合评分

select 
    now() as time_sec,
    case when 100-sum(aa.new_num) < 20 then 20 else Round(100-sum(aa.new_num),0) end as value,
    "综合评分" as metric
from 
(
    select a.severity,count(a.severity) as count,
        case a.severity
            when 2 then count(a.severity)*0.5 
            when 3 then count(a.severity)*1
            when 4 then count(a.severity)*1.5
            when 5 then count(a.severity)*2
        else 0
        end as new_num
    from problem a,triggers b
    where a.objectid = b.triggerid
        and source = 0
        and r_eventid is NULL
        and name LIKE '%弋矶山%'
        and name not like '%ZBPX%'
        and name not like '%AGE99%'
        and b.status = 0
    group by a.severity
) aa
group by "综合评分"

五、告警等级

#灾难
select 
now() as time,
aa.count as value
from 
(
    select  
        case when count(a.severity) is null then 0 else count(a.severity) end as count
    from problem a,triggers b
    where a.objectid = b.triggerid
        and source = 0
        and r_eventid is NULL
        and name LIKE '%弋矶山%'
        and b.status = 0
        and a.severity = 5
) aa 

#严重
select 
now() as time,
aa.count as value
from 
(
    select  
        case when count(a.severity) is null then 0 else count(a.severity) end as count
    from problem a,triggers b
    where a.objectid = b.triggerid
        and source = 0
        and r_eventid is NULL
        and name LIKE '%弋矶山%'
        and b.status = 0
        and a.severity = 4
) aa 

---依此类推---