如何从Icinga监控数据库提取各主机服务的最新状态记录？

嘿，我明白你的问题了——你想从Icinga的数据库里捞每个主机和指定服务组合的最新状态记录，但原来的SQL把所有时间点的记录都返回了对吧？这是因为你把state_time也放进GROUP BY里了，相当于按时间分组，自然会拿到所有条目。

给你两种靠谱的解决方案，根据你的数据库版本选就行：

方案1：用窗口函数ROW_NUMBER()（推荐，适合现代数据库） #

这是最简洁清晰的方法，适合MySQL 8.0+、PostgreSQL、SQL Server这些支持窗口函数的数据库：

SELECT name1, name2, output, state_time
FROM (
    SELECT 
        cs.name1, 
        cs.name2, 
        s.output, 
        s.state_time,
        -- 给每个主机-服务组合按时间倒序编号，最新的那条是1
        ROW_NUMBER() OVER (PARTITION BY cs.name1, cs.name2 ORDER BY s.state_time DESC) AS rn
    FROM icinga_objects cs
    INNER JOIN icinga_statehistory s ON cs.object_id = s.object_id
    WHERE cs.name2 = 'xxx' 
      AND cs.is_active = '1' 
      AND s.state = '0'
) AS sub_query
-- 只取每个组合里编号为1的最新记录
WHERE rn = 1;

方案2：子查询获取最新时间再关联（兼容老版本数据库） #

如果你的数据库是MySQL 5.x这类不支持窗口函数的老版本，可以用这个方法：

SELECT cs.name1, cs.name2, s.output, s.state_time
FROM icinga_objects cs
INNER JOIN icinga_statehistory s ON cs.object_id = s.object_id
-- 先子查询找出每个主机-服务组合的最新状态时间
INNER JOIN (
    SELECT cs.object_id, MAX(s.state_time) AS latest_state_time
    FROM icinga_objects cs
    INNER JOIN icinga_statehistory s ON cs.object_id = s.object_id
    WHERE cs.name2 = 'xxx' 
      AND cs.is_active = '1' 
      AND s.state = '0'
    GROUP BY cs.object_id
) AS latest_records 
    ON s.object_id = latest_records.object_id 
    AND s.state_time = latest_records.latest_state_time
WHERE cs.name2 = 'xxx' 
  AND cs.is_active = '1' 
  AND s.state = '0';

小提示： #

• 方案1里的ROW_NUMBER()会确保每个组合只返回一条最新记录，哪怕同一时间有多条状态记录；
• 方案2如果遇到同一时间多条相同状态的记录，会返回所有这些记录，如果你确定不会出现这种情况，用它也没问题。

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