Create an SQL-based BI dashboards in DX UIM for Server monitoring (CPU, Memory, DISK)

Products

• DX Unified Infrastructure Management (Nimsoft / UIM)

Issue/Introduction

This KB is a Basic Guide to build a CABI-Replacement type of Dashboard in DX UIM Operator Console BI Dashboards using a SQL-Based Data source. 

Environment

• DX UIM: 23.4.*
• Database: Microsoft SQL Server
• Probes: cdm
• Component: Operator Console (OC) BI Dashboards

Cause

Guidance

Resolution

To create a BI Dashboard with a SQL-Based widget in DX UIM Operator Console, follow these steps:

1. Open the Operator Console and navigate to the Business Intelligence portlet (or Dashboard Designer in versions 23.4.5 and lower).
2. Select Create a New Dashboard.
3. Drag a SQL Table widget (or another data widget like a Multi-Line Chart) onto the canvas.
4. Select the widget and click the Widget Properties (wrench icon).
5. Under the Data Source section:
   • Set Type to SQL.
   • Select the nis database.
   • Click the Plus (+) icon to add a new query.
6. Name the query and paste your SQL code into the field.
7. Click Create; the table columns will automatically populate based on the query results.

Example Query. This query will display servers by CS_ID, Name IP and most valuable Server QOS (CPU, Memory, DISK)

 

⚠️ Review this command with your DBA or administrator before running it. The query can be modified to adjust to the required set of metrics. 



WITH S_QOS_DATA_UNIQUE AS
(
    SELECT
        SQD.source,
        SQD.target,
        SQD.probe,
        SQD.qos,
        SQD.origin,
        SQD.robot,
        SQD.table_id,
        SQD.ci_metric_id,
        ROW_NUMBER() OVER
        (
            PARTITION BY
                SQD.origin,
                SQD.robot,
                SQD.source,
                SQD.target,
                SQD.probe,
                SQD.qos
            ORDER BY SQD.table_id DESC
        ) AS rn
    FROM S_QOS_DATA SQD WITH (NOLOCK)
    WHERE SQD.probe IN ('cdm', 'hub')
      AND SQD.qos IN
      (
          'QOS_CPU_USAGE',
          'QOS_MEMORY_PERC_USAGE',
          'QOS_NETWORK_INBOUND_TRAFFIC',
          'QOS_NETWORK_OUTBOUND_TRAFFIC',
          'QOS_DISK_USAGE',
          'QOS_DISK_FREE',
          'QOS_POWER_STATE'
      )
      AND SQD.target NOT IN ('Idle', 'System', 'User', 'Wait')
),
LATEST_SNAPSHOT AS
(
    SELECT
        SQS.table_id,
        SQS.sampletime,
        SQS.samplevalue,
        ROW_NUMBER() OVER
        (
            PARTITION BY SQS.table_id
            ORDER BY SQS.sampletime DESC
        ) AS rn
    FROM S_QOS_SNAPSHOT SQS WITH (NOLOCK)
    WHERE SQS.sampletime >= DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()) - 1, 0)
      AND SQS.sampletime <  DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()), 0)
)
SELECT
    CS_ID,
    NAME,
    IP,
    QOS_CPU_USAGE,
    QOS_MEMORY_PERC_USAGE,
    QOS_NETWORK_INBOUND_TRAFFIC,
    QOS_NETWORK_OUTBOUND_TRAFFIC,
    QOS_DISK_USAGE,
    QOS_DISK_FREE,

    CAST
    (
        (
            ISNULL(QOS_DISK_USAGE, 0) * 100.0
        ) /
        NULLIF
        (
            ISNULL(QOS_DISK_USAGE, 0) +
            ISNULL(QOS_DISK_FREE, 0),
            0
        )
        AS DECIMAL(10,2)
    ) AS AGGREGATE_DISK_USAGE_PERC,

    QOS_POWER_STATE

FROM
(
    SELECT
        CS.cs_id,
        CS.name,
        CS.ip,
        SQD.qos,
        LS.samplevalue
    FROM S_QOS_DATA_UNIQUE SQD

    INNER JOIN LATEST_SNAPSHOT LS
        ON SQD.table_id = LS.table_id
       AND LS.rn = 1

    INNER JOIN CM_CONFIGURATION_ITEM_METRIC CIM
        ON SQD.ci_metric_id = CIM.ci_metric_id

    INNER JOIN CM_CONFIGURATION_ITEM CI
        ON CI.ci_id = CIM.ci_id

    INNER JOIN CM_DEVICE CD
        ON CI.dev_id = CD.dev_id

    INNER JOIN CM_COMPUTER_SYSTEM CS
        ON CD.cs_id = CS.cs_id

    WHERE SQD.rn = 1
) SourceTable

PIVOT
(
    MAX(samplevalue)
    FOR qos IN
    (
        QOS_CPU_USAGE,
        QOS_MEMORY_PERC_USAGE,
        QOS_NETWORK_INBOUND_TRAFFIC,
        QOS_NETWORK_OUTBOUND_TRAFFIC,
        QOS_DISK_USAGE,
        QOS_DISK_FREE,
        QOS_POWER_STATE
    )
) AS PivotTable

ORDER BY NAME;