zabbix 系统表

查询服务器配置信息sql

select ip,
max(case when item='Number of CPUs' or item='Number of cores'  then avg  end) cpus,
max(case when item='Total memory'  then avg  end)totalMem,
max(case when item='Used memory'  then avg  end)usedMem,
max(case when item='Memory utilization'  then avg  end)percentageMem
from (
        select a.name ip ,b.name item,
        (case  when c.value_avg>99968000 then round(c.value_avg/1024/1024/1024,0) else round(c.value_avg,2) end) avg
        from hosts a
        join items b on a.hostid=b.hostid
        join trends c on b.itemid=c.itemid
        where c.clock>1636870800 
        and b.name in ('Number of cores','Total memory','Memory utilization','Number of CPUs','Memory utilization','Used memory')
        group by a.name,b.name
        
        union 
        select a.name,b.name,
        (case  when c.value_avg>99968000 then round(c.value_avg/1024/1024/1024,0) else round(c.value_avg,2) end) avg
        from hosts a
        join items b on a.hostid=b.hostid
        join trends_uint c on b.itemid=c.itemid
        where c.clock>1636870800 
        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
where ip not like '10.6.144%'
group by ip
order by ip ;

查询结果展示

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