如何编写Zabbix SQL语句输出主机名、icmpping等监控数据

核心SQL查询语句 #

要获取你需要的主机名、IP、ping值、丢包率和状态，需关联Zabbix核心数据表（以MySQL/MariaDB为例，其他数据库语法可微调）：

SELECT
    h.name AS hostname,
    h.ip AS ip,
    CONCAT(ROUND(hp.value_avg, 0), 'ms') AS ping,
    CONCAT(ROUND(hl.value_avg, 0), '%') AS loss,
    CASE WHEN h.status = 0 THEN 'up' ELSE 'down' END AS status
FROM
    hosts h
LEFT JOIN (
    SELECT
        hostid,
        AVG(value) AS value_avg
    FROM
        history
    WHERE
        itemid IN (SELECT itemid FROM items WHERE key_ = 'icmpping')
        AND clock >= UNIX_TIMESTAMP(NOW() - INTERVAL 5 MINUTE)
    GROUP BY
        hostid
) hp ON h.hostid = hp.hostid
LEFT JOIN (
    SELECT
        hostid,
        AVG(value) AS value_avg
    FROM
        history
    WHERE
        itemid IN (SELECT itemid FROM items WHERE key_ = 'icmploss')
        AND clock >= UNIX_TIMESTAMP(NOW() - INTERVAL 5 MINUTE)
    GROUP BY
        hostid
) hl ON h.hostid = hl.hostid
WHERE
    h.status = 0  -- 仅筛选启用状态的主机，可按需移除
ORDER BY
    h.name;

语句说明 #

• hosts表：提取主机名（name）、IP（ip）和状态码（status），通过CASE转换为易读的up/down
• 子查询hp：筛选icmpping监控项，取最近5分钟的平均ping值并格式化为Xms
• 子查询hl：筛选icmploss监控项，取最近5分钟的平均丢包率并格式化为X%

示例输出（匹配需求格式） #

注意事项 #

• 需确保Zabbix已配置icmpping和icmploss监控项，且历史数据已写入history表
• 若使用PostgreSQL，时间函数需调整为NOW() - INTERVAL '5 minutes'，UNIX_TIMESTAMP替换为EXTRACT(EPOCH FROM NOW())
• 可修改时间范围（如将5分钟改为10分钟），或移除WHERE h.status = 0查看所有主机

内容的提问来源于stack exchange，提问作者Samuka Sampaio