如何在Icinga告警查询结果中添加指定自定义变量列？

我来帮你搞定这个需求！你可以通过LEFT JOIN关联icinga_customvariables表，再配合COALESCE()函数轻松实现目标——这个组合既能保留原查询的所有结果，又能在没有匹配自定义变量时返回默认值UNKNOWN。

核心思路 #

1. LEFT JOIN：关联原查询的object_id和icinga_customvariables表的object_id，同时把要匹配的varname条件放在ON子句里（避免变成INNER JOIN过滤掉无匹配的行）
2. COALESCE()：如果找到匹配的varvalue就返回它，否则返回UNKNOWN

修改后的完整查询语句 #

记得把下面的YOUR_TARGET_VARNAME替换成你实际要查找的varname（比如business_unit或者notification_group）：

SELECT 
    icinga_objects.object_id,
    icinga_objects.name1 AS host_name,
    icinga_objects.name2 AS ServiceName,
    "service" AS Type,
    icinga_servicestatus.last_check AS LastCheckTime,
    icinga_servicestatus.last_hard_state_change AS LastStateChange,
    TIMEDIFF(NOW(), icinga_servicestatus.last_hard_state_change) AS SinceTime,
    CASE 
        WHEN icinga_servicestatus.current_state = 0 THEN '0' 
        WHEN icinga_servicestatus.current_state = 1 THEN '2' 
        WHEN icinga_servicestatus.current_state = 2 THEN '3' 
        ELSE '3' 
    END AS state,
    -- 新增的自定义变量列
    COALESCE(cv.varvalue, 'UNKNOWN') AS CustomVarValue
FROM 
    icinga_objects,
    icinga_servicestatus,
    icinga_services
    -- 关联自定义变量表，指定匹配条件
    LEFT JOIN icinga_customvariables cv 
        ON icinga_objects.object_id = cv.object_id 
        AND cv.varname = 'YOUR_TARGET_VARNAME'
WHERE 
    icinga_servicestatus.service_object_id IN (
        SELECT service_object_id FROM icinga_services 
        WHERE icinga_services.host_object_id IN (
            SELECT host_object_id FROM icinga_hostgroup_members 
            WHERE hostgroup_id IN (
                SELECT hostgroup_id FROM icinga_hostgroups WHERE alias = 'MY-HOSTGROUP-TO-FILTER'
            )
        )
    ) 
    AND icinga_servicestatus.service_object_id NOT IN (
        SELECT service_object_id FROM icinga_services 
        WHERE icinga_services.service_object_id IN (
            SELECT object_id FROM icinga_objects 
            WHERE icinga_objects.is_active = 1 
            AND icinga_objects.object_id IN (
                SELECT object_id FROM icinga_customvariables WHERE varvalue = '8x5'
            )
        )
    ) 
    AND icinga_servicestatus.last_check > NOW() - INTERVAL 3 HOUR 
    AND icinga_servicestatus.state_type = 1 
    AND icinga_servicestatus.scheduled_downtime_depth = 0 
    AND icinga_objects.object_id = icinga_services.service_object_id 
    AND icinga_servicestatus.service_object_id = icinga_services.service_object_id 
    AND icinga_servicestatus.current_state = 2 
    AND icinga_servicestatus.problem_has_been_acknowledged = 0

示例返回结果 #

修改后你的查询结果会多一列CustomVarValue：

关键注意点 #

• 务必把varname的条件放在LEFT JOIN的ON子句里，而不是WHERE子句——如果放在WHERE里，会过滤掉没有匹配自定义变量的行，就达不到显示UNKNOWN的效果了。
• 你可以把CustomVarValue改成更贴合业务的列名，比如BusinessUnit或者NotificationGroup。

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