zabbix system table

 events

select date_format(from_unixtime(clock),'%Y-%m-%d')time,name,count(*) from events where name like '%frequent%' and value=0 group by date_format(from_unixtime(clock),'%Y-%m-%d'),name  order by date_format(from_unixtime(clock),'%Y-%m-%d') desc limit 30;

 

 time diff

select a.name,b.name,abs(from_unixtime(clock)-from_unixtime(value)) df
        from hosts a
        join items b on a.hostid=b.hostid
        join history_uint c on b.itemid=c.itemid
        where not exists (select * from hosts_groups e where e.groupid=17 and e.hostid=a.hostid )
and  c.clock>UNIX_TIMESTAMP(date_sub(now(),interval 3 minute))
        and b.name in ('System local time')
        group by a.name,b.name
order by abs(from_unixtime(clock)-from_unixtime(value)) ;

 

host cpu,mem  utilization

select ip,
max(case when item='Number of CPUs' or item='Number of cores'  then value_max  end) cpus,
max(case when item='Total memory'  then  round(value_max/1024/1024/1024,0)  end)totalMem,
max(case when item='Used memory'  then round(value_max/1024/1024/1024,0)  end)usedMem,
max(case when item='CPU utilization'  then round(value_max,2)  end)pusedCpuMax,
max(case when item='CPU utilization'  then round(value_avg,2)  end)pusedCpuAvg,
max(case when item='Memory utilization'  then round(value_max,2)  end)pusedMemMax,
max(case when item='Memory utilization'  then round(value_avg,2)  end)pusedMemAvg
from (
        select a.name ip ,b.name item,max(c.value_max)  value_max,avg(c.value_avg)value_avg
        from hosts a
        join items b on a.hostid=b.hostid
        join trends c on b.itemid=c.itemid
        where c.clock>UNIX_TIMESTAMP(date_sub(curdate(),interval 7 day))
        and b.name in ('Number of cores','Total memory','Memory utilization','Number of CPUs','Memory utilization','Used memory','CPU utilization')
        group by a.name,b.name
        
        union 
        select a.name,b.name,max(c.value_max) value_max,avg(c.value_avg)value_avg
        from hosts a
        join items b on a.hostid=b.hostid
        join trends_uint c on b.itemid=c.itemid
        where c.clock>UNIX_TIMESTAMP(date_sub(curdate(),interval 90 day))
        and b.name in ('Number of cores','Total memory','Memory utilization','Number of CPUs','Memory utilization','Used memory')
        group by a.name,b.name
)t join hosts d on t.ip=d.name
where d.error not like '%cannot connect%' and d.status=0 and not exists (select * from hosts_groups e where e.groupid=17 and e.hostid=d.hostid )
group by ip
order by  pusedMemAvg;

 

 disk utilization

select ta.ip,substr(ta.item,1,locate(':',ta.item)) path,ta.utilization_max,tb.total_max
from
(select a.name ip ,b.name item,round(max(c.value_max),2) utilization_max
from hosts a
join items b on a.hostid=b.hostid
join trends c on b.itemid=c.itemid
where c.clock>UNIX_TIMESTAMP(date_sub(now(),interval 2 hour))
and length(b.name)<50 and b.name like '%Space utilization'
group by a.name,b.name
) as ta
 join

(select a.name ip ,b.name item,round(max(c.value_max)/1024/1024/1024,2) total_max
from hosts a
join items b on a.hostid=b.hostid
join trends_uint c on b.itemid=c.itemid
where c.clock>UNIX_TIMESTAMP(date_sub(now(),interval 2 hour))
and length(b.name)<50 and b.name like '%Total space'
group by a.name,b.name
)as tb
on ta.ip=tb.ip and substr(ta.item,1,locate(':',ta.item))=substr(tb.item,1,locate(':',tb.item))
join hosts d on ta.ip=d.name
where ta.utilization_max<50  and tb.total_max>200
and not exists (select * from hosts_groups e where e.groupid=17 and e.hostid=d.hostid )
order by tb.total_max asc;

 

 

result

 

refer：https://blog.csdn.net/weixin_34238642/article/details/93687290