Availability and maintenance percentage calculation in BI reports, UIM

Products

• DX Unified Infrastructure Management (Nimsoft / UIM)

Issue/Introduction

In DX Unified Infrastructure Management (UIM) Business Intelligence (BI) reports, the combined value of Operational Availability (%) and Maintenance (%) might exceed 100%.

This occurs because the two metrics are calculated independently using different denominators. This article explains the calculation logic and provides a query to validate the results.

Environment

• DX Unified Infrastructure Management
• BI Reporting /Sample BI Dashboards

Cause

Operational Availability and Maintenance percentages use different time-basis denominators:

1. Operational Availability (%): Calculated only using samples collected outside of maintenance windows.
2. Maintenance (%): Calculated based on the total duration of the reporting window.

Because maintenance time is excluded from the availability denominator but included in the maintenance denominator, the sum of these two percentages is not intended to equal 100%.

Resolution

Understand the independent formulas used in BI reporting:

Operational Availability (%)

This metric uses QoS sample data. Samples falling within a defined maintenance window are excluded from the calculation. Formula: Operational Availability (%) = [Samples with value 1] / ([Samples with value 1] + [Samples with value 0]) × 100

Maintenance (%)

This represents the proportion of the reporting period the device was in a maintenance window, relative to the total duration. Formula: Maintenance (%) = [Minutes in maintenance] / [Total reporting window minutes] × 100

Validation Query

Execute the following SQL query against the UIM database to validate these calculations for a 12-hour window. Replace CS_ID values with the target device IDs:

sql

WITH maintenance_windows AS (
    SELECT cs.name, cs.cs_id, mw.START_TIME, mw.END_TIME,
    CASE WHEN mw.START_TIME < DATEADD(hour, -12, CURRENT_TIMESTAMP) THEN DATEADD(hour, -12, CURRENT_TIMESTAMP) ELSE mw.START_TIME END AS adjusted_start,
    CASE WHEN mw.END_TIME > CURRENT_TIMESTAMP THEN CURRENT_TIMESTAMP ELSE mw.END_TIME END AS adjusted_end
    FROM MAINTENANCE_WINDOW_HISTORY mw
    JOIN cm_device dev ON mw.DEV_ID = dev.dev_id
    JOIN cm_computer_system cs ON dev.cs_id = cs.cs_id
    WHERE mw.IS_LATEST = 1 
    AND mw.END_TIME >= DATEADD(hour, -12, CURRENT_TIMESTAMP)
    AND mw.START_TIME <= CURRENT_TIMESTAMP
    AND cs.origin IN (SELECT DISTINCT ORIGIN FROM CM_COMPUTER_SYSTEM_ORIGIN)
),
merged_windows AS (
    SELECT name, cs_id, MIN(adjusted_start) AS merged_start, MAX(adjusted_end) AS merged_end
    FROM maintenance_windows
    GROUP BY name, cs_id
)
SELECT 
    CASE WHEN t1.availablecount + t1.unavailablecount = 0 THEN 0 
    ELSE CAST(t1.availablecount AS FLOAT) / (t1.availablecount + t1.unavailablecount) * 100 END AS availability,
    (CAST(ISNULL(t4.maintainence, 0) AS FLOAT) * 100 / 720) AS maintainence_percentage,
    t1.cs_id
FROM (
    SELECT SUM(availablecount) AS availablecount, SUM(unavailablecount) AS unavailablecount, cs_id
    FROM (
        SELECT 
            SUM(CASE WHEN vn.samplevalue = 1 THEN 1 ELSE 0 END) AS availablecount,
            SUM(CASE WHEN vn.samplevalue = 0 THEN 1 ELSE 0 END) AS unavailablecount,
            ccs.cs_id
        FROM RN_QOS_DATA_0001 vn
        JOIN S_QOS_DATA sqd ON vn.table_id = sqd.table_id
        JOIN CM_COMPUTER_SYSTEM ccs ON sqd.ci_metric_id = ccs.cs_id
        WHERE CAST(vn.sampletime AS DATETIME) >= DATEADD(hour, -12, CURRENT_TIMESTAMP)
        AND NOT EXISTS (
            SELECT 1 FROM MAINTENANCE_WINDOW_HISTORY mw 
            WHERE mw.IS_LATEST = 1 
            AND vn.sampletime BETWEEN mw.start_time AND mw.end_time
        )
        GROUP BY ccs.cs_id
    ) X1 GROUP BY cs_id
) t1
LEFT JOIN (
    SELECT SUM(DATEDIFF(minute, merged_start, merged_end)) AS maintainence, cs_id 
    FROM merged_windows GROUP BY cs_id
) t4 ON t4.cs_id = t1.cs_id
WHERE t1.cs_id IN ('####'); -- Replace with CS_ID