Modern Dashboard - Query (Missing zeros on the query)

jamshad2712

The below-mentioned query is working fine. But I am facing an issue it is not showing 0. 

Can anyone change the query if no alert its shows it as 0. 

SELECT Count(1) AS CountItems
     , OAC.DisplayName
     , CASE 
          WHEN OAC.DisplayName LIKE 'Uplink Interface is down'
               THEN 'Orange'
          WHEN OAC.DisplayName LIKE 'Link Receive Utilization above 80%'
               THEN 'Blue'
          WHEN OAC.DisplayName LIKE 'Device is down'
               THEN 'Red'
          WHEN OAC.DisplayName LIKE 'Server is down'
               THEN 'Red'
          WHEN OAC.DisplayName LIKE 'Turnstile is down'
               THEN 'Red'
          WHEN OAC.DisplayName LIKE 'Memory Utilization more than 80 %'
               THEN 'Brown'
          END AS [Color]
FROM Orion.AlertActive OAA
JOIN Orion.AlertObjects OAO
     ON OAO.AlertObjectID = OAA.AlertObjectID
JOIN Orion.AlertConfigurations OAC
     ON OAC.AlertID = OAO.AlertID
JOIN Orion.NodesCustomProperties NCP
     ON NCP.NodeID = OAO.RelatedNodeID
WHERE NCP.Stadium_Name LIKE 'Lusail'
GROUP BY OAC.DisplayName

wluther

@jamshad2712 I'm not super certain, but you might be able to wrap something in a ISNULL(,0), which should dump out a 0 if not having another value.

Maybe (again, just as a guess, as I'm not somewhere I can test) you can try

ISNULL(COUNT(1),0) AS CountItems

I do something like that in a few spots for some of our dashboards/widgets, which shows a 0 until something alerts, then shows whatever the value is.

Hopefully that is something that will work for you, but let us know how it goes.

Thank you,

-Will