zabbix统计闲置资产

背景

近期降本增效之风涉及基础资源浪费情况，领导对于资源增量合理性认可度降低，需要infra团队增加手段是实现计算资源使用情况回顾，及时发现闲置、浪费资源，并通过管理措施降低资源净增量
公司内部存在超融合和物理服务器两种形式

解决思路

通过需求，初步需要关注CPU、内存、硬盘使用情况，拍脑袋定下指标：cpu利用率、内存利用率、硬盘IO相关，后续实际统计发现硬盘相关IO统计以及不大，于是战略性放弃

CPU指标

通常来说，物理服务器和虚拟机服务器CPU利用率无法使用同一阈值，拍脑袋定下指标：虚拟机CPU使用率低于15%；物理服务器CPU使用率2%（不包含宿主机、非通用操作系统形式交付的一体机、虚拟机）

内存指标

无论物理服务器还是虚拟机服务器，该指标可以一视同仁，拍脑袋定下指标CPU使用率不低于15%

实现路径

虚拟机可以通过超融合管理平台定制报告（但是无法关联CPU、内存指标，需要分别出具报告，然后手工处理）；
物理服务器目前除了zabbix，没有更好的选择；
还有一条路（cmdb），但是cmdb项目建设由其他团队主导，现状无法直接实现，需要针对存量进行改造；
综合以上因素，本次计划选择zabbix作为统一方案解决；

实施方案

通过zabbix自带报表功能，发现需要先定制dashboard，具体实现太费劲，并且有两套zabbix，具体实现完成后估计脑子和手都要发麻，战略性放弃；

指标确认

另一条路就是通过数据库，查找需要的数据，首先确认zabbix对应以上两个指标为：

序号	系统	项目	指标（键值）	备注（监控项名称）
1	Lin	CPU	system.cpu.util	CPU utilization
2	Win	CPU	system.cpu.util	CPU utilization
3	Lin	MEM	vm.memory.utilization	Memory utilization in %
4	Win	MEM	vm.memory.util	Memory utilization in %

综合以上因素，计划使用监控项名称进行统计。

SQL查询语句

实际关联的历史数据主要有history和trends两张表，我司实际环境history只有7天的数据（按照监控项实际采集间隔进行获取保留），trends对应数据为history进行精简提取（以小时为单位，获取对应指标的最小值，平均值和最大值）
结合多次查询结果，最终计划使用trends表数据作为依据进行处理，结合我司实际服务情况，分两种情况进行统计：
1.全天CPU、内存相关指标

-- 趋势表合并查询cpu、内存利用率，取平均值
SELECT 
    h.hostid,
    h.host AS hostname,
    -- CPU 利用率统计值
    AVG(CASE WHEN i.name = 'CPU utilization' THEN t.value_min END) AS avg_min_cpu_usage,
    AVG(CASE WHEN i.name = 'CPU utilization' THEN t.value_avg END) AS avg_avg_cpu_usage,
    AVG(CASE WHEN i.name = 'CPU utilization' THEN t.value_max END) AS avg_max_cpu_usage,
    -- 内存利用率统计值
    AVG(CASE WHEN i.name = 'Memory utilization in %' THEN t.value_min END) AS avg_min_memory_usage,
    AVG(CASE WHEN i.name = 'Memory utilization in %' THEN t.value_avg END) AS avg_avg_memory_usage,
    AVG(CASE WHEN i.name = 'Memory utilization in %' THEN t.value_max END) AS avg_max_memory_usage
FROM 
    hosts h
JOIN 
    items i ON h.hostid = i.hostid
JOIN 
    trends t ON i.itemid = t.itemid
WHERE 
    i.name IN ('CPU utilization', 'Memory utilization in %') -- CPU和内存利用率的监控项
    AND t.clock BETWEEN UNIX_TIMESTAMP(DATE_SUB(NOW(), INTERVAL 30 DAY)) AND UNIX_TIMESTAMP(NOW()) -- 过去30天的数据
GROUP BY 
    h.hostid;

2.主要服务时间指标统计

-- 趋势表合并查询cpu、内存利用率，取平均值（9:00-16:00）
SELECT 
    h.hostid,
    h.host AS hostname,
    -- CPU 利用率统计值
    AVG(CASE WHEN i.name = 'CPU utilization' THEN t.value_min END) AS avg_min_cpu_usage,
    AVG(CASE WHEN i.name = 'CPU utilization' THEN t.value_avg END) AS avg_avg_cpu_usage,
    AVG(CASE WHEN i.name = 'CPU utilization' THEN t.value_max END) AS avg_max_cpu_usage,
    -- 内存利用率统计值
    AVG(CASE WHEN i.name = 'Memory utilization in %' THEN t.value_min END) AS avg_min_memory_usage,
    AVG(CASE WHEN i.name = 'Memory utilization in %' THEN t.value_avg END) AS avg_avg_memory_usage,
    AVG(CASE WHEN i.name = 'Memory utilization in %' THEN t.value_max END) AS avg_max_memory_usage
FROM 
    hosts h
JOIN 
    items i ON h.hostid = i.hostid
JOIN 
    trends t ON i.itemid = t.itemid
WHERE 
    i.name IN ('CPU utilization', 'Memory utilization in %') -- CPU和内存利用率的监控项
    AND t.clock BETWEEN UNIX_TIMESTAMP(DATE_SUB(NOW(), INTERVAL 30 DAY)) AND UNIX_TIMESTAMP(NOW()) -- 过去30天的数据
    AND HOUR(FROM_UNIXTIME(t.clock)) BETWEEN 9 AND 16 -- 9:00-16:00的时间范围
GROUP BY 
    h.hostid;

结合查询结果，导出至excel通过透视图进行进一步加工处理，确认实际合适的阈值（此步骤不进行展开）当然也可以根据统计结果动态确认阈值；

后续优化

后续可以结合实际确认好的CPU和内存指标，优化查询语句，也可以通过crontab实现定期查询，并将结果通过邮件、企微等方式通知关联人员。