query for disk usage data in UIM - top consumers

Products

• DX Unified Infrastructure Management (Nimsoft / UIM)

Issue/Introduction

Is there a query I can run (for integration with dashboard tools like Grafana) to pull the list of robots with the highest disk usage?

 

Environment

DX UIM - any version

Resolution

The following query is an example of a query that returns the top disk consumers in Windows for the c:\ drive based on QOS_DISK_USAGE_PERC.

Construction of specific queries is generally beyond the scope of support; you should consult with a qualified DBA to construct additional queries or modify this one as needed, but it provides a logical starting point.

 

DECLARE @sql NVARCHAR(MAX);
DECLARE @r_table NVARCHAR(255);
DECLARE @report_qos NVARCHAR(MAX) = "QOS_DISK_USAGE_PERC"  --- change this for a different QoS
DECLARE @report_target NVARCHAR(MAX) = "C:\"  -- change this for a different target to match the QoS

 

 

SELECT TOP 1 @r_table = r_table
FROM S_QOS_DATA
WHERE qos = @report_qos
  AND target = @report_target

-- Do not change anything below here unless you really know what you are doing
SET @sql = N'
WITH LatestSamples AS (
    SELECT
        raw.table_id,
        MAX(raw.sampletime) AS latest_sampletime
    FROM
        S_QOS_DATA qos
    JOIN
        ' + QUOTENAME(@r_table) + N' raw
    ON qos.table_id = raw.table_id
    WHERE
        qos.qos = @report_qos
        AND qos.target = @report_target
    GROUP BY
        raw.table_id
)
SELECT
    qos.robot,
    qos.source,
    qos.target,
    qos.qos,
    latest.latest_sampletime,
    raw.samplevalue AS disk_usage_percent
FROM
    S_QOS_DATA qos
JOIN
    LatestSamples latest
    ON qos.table_id = latest.table_id
JOIN
    ' + QUOTENAME(@r_table) + N' raw
    ON latest.table_id = raw.table_id
    AND latest.latest_sampletime = raw.sampletime
WHERE
    qos.qos = @report_qos
    AND qos.target = @report_target
ORDER BY
    raw.samplevalue DESC;
';

EXEC sp_executesql @sql;