zabbix的监控主机信息



zabbix的监控主机的CPU,磁盘空间，内存，io等信息

insert into zabbix.month_report_t
select
   c.hostname as hostname,
   c.key_ as key_,
   value_month_avg as month_avg,
   ROUND(d.value_month_min,2) as value_month_min,
   ROUND(d.value_month_avg_max,2) as value_month_avg_max,
   ROUND(d.value_month_max,2) as value_month_max,
   FROM_UNIXTIME(d.clock) as time,
   DATE_FORMAT(now(),'%Y-%m') as report_datetime
from (
 select
  a.hostid as hostid,
  b.itemid as itemid,
  a.hostname as hostname,
  b.key_ as key_
 from (
 select hostid,host as hostname
 from zabbix.hosts
 where host in ('sz-dzfp-data-db03','sz-dzfp-data-db02',
  'sz-dzfp-data-dbk02','sz-dzfp-data-db01',
  'sz-dzfp-data-skmysql01','sz-dzfp-data-skmysql02',
  'sz-dzfp-data-skmysql03','sz-dzfp-data-skmysql04',
  'sz-dzfp-data-skmysql05'
  )
 ) a
 left join
 (
  select hostid,key_,itemid
  from zabbix.items
  where  key_ =  'system.cpu.util[,idle]'  or key_='system.cpu.util[,iowait]'
   or key_ like 'io.util[s%]'  
 ) b
 on a.hostid=b.hostid
) c
left join (
  select itemid,clock,
    value_avg  as value_month_avg,
    min(value_min) as value_month_min,
    avg(value_max) as value_month_max,
    avg(value_avg) as value_month_avg_max
  from zabbix.trends
  WHERE clock >UNIX_TIMESTAMP(date_sub(now(),INTERVAL 30 DAY))
  and itemid in(
 
select
  b3.itemid as itemid
 from (
 select hostid,host as hostname
 from zabbix.hosts
 where host in ('sz-dzfp-data-db03','sz-dzfp-data-db02',
  'sz-dzfp-data-dbk02','sz-dzfp-data-db01',
  'sz-dzfp-data-skmysql01','sz-dzfp-data-skmysql02',
  'sz-dzfp-data-skmysql03','sz-dzfp-data-skmysql04',
  'sz-dzfp-data-skmysql05'
  )
 ) a3
 left join
 (
  select hostid,key_,itemid
  from zabbix.items
  where  key_ =  'system.cpu.util[,idle]' or key_ like 'io.util[s%]'
   or key_='system.cpu.util[,iowait]'
 ) b3
 on a3.hostid=b3.hostid

)
  group by itemid,clock
 
 ) d
on c.itemid = d.itemid

UNION all
select
   c1.hostname as hostname,
   c1.key_ as key_,
   value_month_avg ,
   d1.value_month_min as value_month_min,
   d1.value_month_max as value_month_max,
   d1.value_month_avg_max as value_month_avg_max,
   FROM_UNIXTIME(d1.clock) as time,
   DATE_FORMAT(now(),'%Y-%m') as report_datetime
from (
 select
  a1.hostid as hostid,
  b1.itemid as itemid,
  a1.hostname as hostname,
  b1.key_ as key_
 from (
 select hostid,host as hostname
 from zabbix.hosts
 where host in ('sz-dzfp-data-db03','sz-dzfp-data-db02',
  'sz-dzfp-data-dbk02','sz-dzfp-data-db01',
  'sz-dzfp-data-skmysql01','sz-dzfp-data-skmysql02',
  'sz-dzfp-data-skmysql03','sz-dzfp-data-skmysql04',
  'sz-dzfp-data-skmysql05'
  )
 ) a1
 left join
 (
  select hostid,key_,itemid
  from zabbix.items
  where   key_ = 'vm.memory.size[available]'
       or  key_ = 'vm.memory.size[total]'
       or key_ like 'vfs.fs.size[/%,total]'
       or key_ like 'vfs.fs.size[/%,used]'
 ) b1
 on a1.hostid=b1.hostid
) c1
left join (
   select itemid,clock,
    value_avg/(1024*1024*1024)  as value_month_avg,
    min(value_min)/(1024*1024*1024) as value_month_min,
    avg(value_max)/(1024*1024*1024) as value_month_max,
    avg(value_avg)/(1024*1024*1024) as value_month_avg_max
  from zabbix.trends_uint
  WHERE clock >UNIX_TIMESTAMP(date_sub(now(),INTERVAL 30 DAY))
  and itemid in (
 
   
   select
  b2.itemid as itemid
 from (
 select hostid,host as hostname
 from zabbix.hosts
 where host in ('sz-dzfp-data-db03','sz-dzfp-data-db02',
  'sz-dzfp-data-dbk02','sz-dzfp-data-db01',
  'sz-dzfp-data-skmysql01','sz-dzfp-data-skmysql02',
  'sz-dzfp-data-skmysql03','sz-dzfp-data-skmysql04',
  'sz-dzfp-data-skmysql05'
  )
 ) a2
 left join
 (
  select hostid,key_,itemid
  from zabbix.items
  where   key_ = 'vm.memory.size[available]'
       or  key_ = 'vm.memory.size[total]'
       or key_ like 'vfs.fs.size[/%,total]'
       or key_ like 'vfs.fs.size[/%,used]'
 ) b2
 on a2.hostid=b2.hostid 

  )
  group by itemid,clock
 
 ) d1
on c1.itemid = d1.itemid
;

insert into zabbix.month_report_total_t
select
y2.hostname,y2.time as time,
'cpu_idle' as check_object,
CONCAT(value_min,'%') as vaules
from  zabbix.month_report_t y2
where key_  ='system.cpu.util[,idle]'

UNION all
select
y2.hostname,y2.time as time,
'cpu_io_wait' as check_object,
CONCAT(value_max,'%') as vaules
from  zabbix.month_report_t y2
where key_='system.cpu.util[,iowait]'

UNION all
select
x.hostname,x.time,
'disk_total_size' as check_object,
 ROUND(x.disk_total_size,2) as vaules
from
(select
  hostname,time,
  sum(value_max) as disk_total_size
from  zabbix.month_report_t
where     key_ like 'vfs.fs.size[/%,total]'
group by hostname,time
) x
union ALL
select
y.hostname,y.time,
'disk_used_size' as check_object,
 ROUND(y.disk_total_size,2) as vaules
from
(select
  hostname,time,
  sum(value_max) as disk_total_size
from  zabbix.month_report_t
where     key_ like 'vfs.fs.size[/%,used]'
group by hostname,time
) y

union  ALL
select
a.hostname,a.time,
'disk_used_rate' as check_object,
CONCAT(format((b.disk_use_size/a.disk_total_size)*100,2),'%') as vaules
from
(select
  hostname,time,
  sum(value_max) as disk_total_size
from  zabbix.month_report_t
where     key_ like 'vfs.fs.size[/%,total]'
group by hostname,time
) a
LEFT JOIN
(select
  hostname,time,
  sum(value_max) as disk_use_size
from  zabbix.month_report_t
where     key_ like 'vfs.fs.size[/%,used]'
group by hostname,time
 )  b  
 on a.hostname=b.hostname

UNION ALL
select
a.hostname as hostname,a.time as time,
'memory_used_rate' as check_object,
CONCAT(ROUND(b.memory_used_size/a.memory_total_size,2)*100,'%') as vaules
from
(select
  hostname,time,
  sum(value_max) as memory_total_size
from  zabbix.month_report_t
where    key_ = 'vm.memory.size[total]'
group by hostname,time
) a
LEFT JOIN
(select
  hostname,time,
  sum(value_max) as memory_used_size
from  zabbix.month_report_t
where     key_ = 'vm.memory.size[available]'
group by hostname,time
 )  b  
 on a.hostname=b.hostname

UNION ALL
select t9.hostname,t9.time as time,
check_object,vaules
from (
select
z.hostname as hostname,z.time as time,
'io_used_rate_avg' as check_object,
CONCAT(ROUND(z.io_used_rate_avg,2),'%') as vaules
from
(
select
hostname,time,
avg(value_max) as io_used_rate_avg
from  zabbix.month_report_t
where    key_ like 'io.util[s%]'
group by hostname,time
order by sum(value_max) DESC
) z
) t9
;

select 
hostname  as 主机名,
min(case check_object when 'cpu_idle' then object_values  end) as CPU空闲率,
max(case check_object when 'cpu_io_wait' then object_values  end) as CPU的io等待,
max(case check_object when 'disk_total_size' then object_values end) as  磁盘总大小_GB,
max(case check_object when 'disk_used_size' then object_values  end) as 磁盘使用大小_GB,
max(case check_object when 'disk_used_rate' then object_values  end) as 磁盘使用率,
max(case check_object when 'memory_used_rate' then object_values  end) as 内存平均使用率,
max(case check_object when 'io_used_rate_avg' then object_values  end) as io平均使用率
from zabbix.month_report_total_t
GROUP BY hostname;

select * from  zabbix.month_report_total_t LIMIT 2;