基于Icinga2数据库按主机、服务、日期统计状态时长的MySQL实现

Got it, let's tackle this Icinga2 state duration report problem step by step. The key here is to calculate how long each host-service combination stayed in a specific state, then roll that up by date, host, service, and state.

Approach Breakdown #

First, we need to figure out when each state started and ended. Since your table only logs state change timestamps, we'll use a window function to grab the next state change time for each record. Then we can compute the duration between those two times. Finally, we'll group the results by the dimensions you need (host, service, date, state) to get the aggregated totals.

MySQL Query Implementation #

Assuming your table is named icinga_state_history (replace this with your actual table name if different):

WITH state_time_windows AS (
    SELECT
        Hostname,
        Servicename,
        State,
        State_Time AS state_start,
        -- Get the next state change time; use current time if it's the latest record
        LEAD(State_Time, 1, NOW()) OVER (
            PARTITION BY Hostname, Servicename 
            ORDER BY State_Time ASC
        ) AS state_end,
        -- Calculate duration in seconds (adjust unit as needed)
        TIMESTAMPDIFF(SECOND, State_Time, LEAD(State_Time, 1, NOW()) OVER (
            PARTITION BY Hostname, Servicename 
            ORDER BY State_Time ASC
        )) AS duration_sec
    FROM icinga_state_history
    -- Optional: filter out initial state records if needed (where Last_State is NULL)
    -- WHERE Last_State IS NOT NULL
),
daily_state_summary AS (
    SELECT
        Hostname AS host,
        Servicename AS service,
        DATE(state_start) AS report_date,
        -- Convert state codes to human-readable labels (Icinga2 standard)
        CASE State
            WHEN 0 THEN 'OK'
            WHEN 1 THEN 'WARNING'
            WHEN 2 THEN 'CRITICAL'
            WHEN 3 THEN 'UNKNOWN'
            ELSE 'UNDEFINED'
        END AS state,
        -- Sum duration and convert to hours for readability (change to MINUTE/SECOND if preferred)
        SUM(duration_sec) / 3600 AS total_duration_hours
    FROM state_time_windows
    GROUP BY Hostname, Servicename, DATE(state_start), State
    ORDER BY Hostname, Servicename, report_date, State
)
SELECT * FROM daily_state_summary;

What Each Part Does #

1. state_time_windows CTE:

   • Uses LEAD() to pair each state change with the next one for the same host-service pair. This gives us the start and end time of each state period.
   • Calculates the duration of each state in seconds. For the most recent state (no next change), it uses the current time as the end point.

2. daily_state_summary CTE:

   • Groups data by host, service, date, and state.
   • Converts raw state codes to friendly names (adjust this if your state values use different codes).
   • Aggregates total duration per group and converts it to hours (easily switch to minutes or seconds by changing the division factor).

Sample Report Output #

Customization Tips #

• Adjust Time Units: Change TIMESTAMPDIFF(SECOND, ...) to MINUTE or HOUR if you want duration in those units directly.
• Hourly Aggregation: Replace DATE(state_start) with DATE_FORMAT(state_start, '%Y-%m-%d %H:00:00') to get hourly instead of daily totals.
• Filter Specific Periods: Add a WHERE State_Time BETWEEN '2024-05-01' AND '2024-05-31' clause in the first CTE to limit results to a date range.

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