MODERN DASHBOARD | Return COUNT null as zero in KPI widget

I'm fairly new to SWQL and SQL. Im having a hard time to make this swql query work. 

SELECT COUNT(1) as Count_Items, a.Status
FROM Orion.APM.Application a

JOIN Orion.Nodes n
ON n.NodeID = a.NodeID

WHERE Status = 14 AND n.CustomProperties.Device_Prio LIKE 'Prio1%'
GROUP BY a.Status

This counts the critical applications with a custom property. To make it more presentable I would like to show it as zero when null is returned instead of blank in the widget. Thank in advance!

Parents
  • Try this query instead. It will count all the applications with critical status that are assigned to nodes with the custom property starting with "Prio1". If there are none, the count will be 0. This will auto-join all your tables for you.

    SELECT COUNT(1) as Count_Items
    FROM Orion.APM.Application a
    WHERE a.Status = 14 AND a.Node.CustomProperties.Device_Prio LIKE 'Prio1%'

    In your query, if there are no results there won't be any status returned therefore no count for that status. If you wanted to modify your query; you could remove the Status in the SELECT, and in the WHERE clause reference the application table Status with a.Status instead of just Status (otherwise the system doesn't know whether you mean Nodes Status or Application Status), and get rid of the GROUP BY.

    SELECT COUNT(1) as Count_Items
    FROM Orion.APM.Application a
    JOIN Orion.Nodes n
    ON n.NodeID = a.NodeID
    WHERE a.Status = 14 AND n.CustomProperties.Device_Prio LIKE 'Prio1%'

    I think your query is more designed for Proportional widgets as they typically need multiple values (e.g. item and item_count). The KPI widget just needs a single value to be returned by the query.

Reply
  • Try this query instead. It will count all the applications with critical status that are assigned to nodes with the custom property starting with "Prio1". If there are none, the count will be 0. This will auto-join all your tables for you.

    SELECT COUNT(1) as Count_Items
    FROM Orion.APM.Application a
    WHERE a.Status = 14 AND a.Node.CustomProperties.Device_Prio LIKE 'Prio1%'

    In your query, if there are no results there won't be any status returned therefore no count for that status. If you wanted to modify your query; you could remove the Status in the SELECT, and in the WHERE clause reference the application table Status with a.Status instead of just Status (otherwise the system doesn't know whether you mean Nodes Status or Application Status), and get rid of the GROUP BY.

    SELECT COUNT(1) as Count_Items
    FROM Orion.APM.Application a
    JOIN Orion.Nodes n
    ON n.NodeID = a.NodeID
    WHERE a.Status = 14 AND n.CustomProperties.Device_Prio LIKE 'Prio1%'

    I think your query is more designed for Proportional widgets as they typically need multiple values (e.g. item and item_count). The KPI widget just needs a single value to be returned by the query.

Children