整集群:
SELECTSUM(1.0/i.delay)AS qps FROM items i,hosts h WHERE i.status='0' AND i.hostid=h.hostid AND h.status='0' AND i.delay0;
breakdown 到proxy的:
SELECT h.proxy_hostid,SUM(1.0/i.delay) AS qps FROM items i,hosts h WHERE i.status='0' AND i.hostid=h.hostid AND h.status='0' AND i.delay0 AND h.proxy_hostid is NOT NULL GROUP BY h.proxy_hostid;
1)整个集群的
select a.aa/b.bb from
(select count(*) as aa from items
where lastclock > UNIX_TIMESTAMP()-1800 and delay < 900
and hostid in (select hostid from hosts where status=0)
and status = 0
) a,
(select count(*) as bb from items
where delay < 900 and status = 0
and hostid in (select hostid from hosts where status=0)
) b
2)到proxy的:
select a.aa/b.bb from
(select count(*) as aa from items
where lastclock > UNIX_TIMESTAMP()-1800 and delay < 900
and hostid in (select hostid from hosts where status=0 and proxy_hostid = 10100)
and status = 0
) a,
(select count(*) as bb from items
where delay < 900 and status = 0
and hostid in (select hostid from hosts where status=0 and proxy_hostid = 10100)
) b
其中proxy_hostid是对应的proxy的id.
3)到主机,可以定位哪些主机的值更新存在异常(比unreachable的报警更加准确):
select b.hostname ,c.ip,a.update_percent as uppercent from
(select a.hostid,round(a.aa*100/b.bb,2) as update_percent from
(select hostid,count(*) as aa from items
where lastclock > UNIX_TIMESTAMP()-1800 and delay < 900
and hostid in (select hostid from hosts where status=0)
and status = 0 group by hostid
) a,
(select hostid,count(*) as bb from items
where delay < 900 and status = 0
and hostid in (select hostid from hosts where status=0) group by hostid
) b where a.hostid=b.hostid)a,(select hostid,lower(host) as hostname from hosts where status=0)b,
(select hostid,ip from interface where type='1')c
where a.hostid=b.hostid and b.hostid=c.hostid having(a.update_percent) < 80 order by uppercent;