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!

  • 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.

  • Hi there, 

    SWQL supports the ISNULL function for this exact use case, could you try the following for me:

    SELECT ISNULL(count(1), '0') as app_count
    from orion.apm.Application a
    where a.Node.CustomProperties.Device_Prio LIKE 'Prio1%' and a.Status = '14'

    This query (with my custom property names replaced) works fine to show me both 0 or more than 0 in the query results based on what is returned. I've also taken the liberty of removing the slow JOIN command and replaced it with the faster SWQL table connections. As said by in their response, the KPI widget should really only be returning a single numerical value so I also removed the other column from the results. 

    Let me know how you get on! 

    Kind regards,

    Marlie Fancourt | SolarWinds Pre-Sales Manager

    Prosperon Networks | SolarWinds Partner since 2006

    If this helps answer your question please mark my answer as confirmed to help other users, thank you!

  • Just got back from thwack. Already resolved this using ISNULL, same as your answer. Thanks btw!

  • Yup, got rid of the GROUP BY. It was messing up the query when I initially used ISNULL. Thanks for the answer!