This discussion has been locked. The information referenced herein may be inaccurate due to age, software updates, or external references.
You can no longer post new replies to this discussion. If you have a similar question you can start a new discussion in this forum.

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

Reply
  • 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!

Children