KPI Group Status Widget with Color

alanmpadgett

I'm trying to build a KPI widget, using a SWQL query, for Group (or Container ID) that changes color when the Group is in a Warning (Orange) or Critical (Red) but stays Green when the Group status is Up.  I've tried the two options below but I can't get either one to work the way I want the KPI to be displayed.  I'm not sure if a CASE/WHEN set of commands is needed.  I saw this option used for Proportional widgets but now for a KPI widget.  Any help would be MUCH appreciated.

** SWQL - Example 1 **

The Example 1 query somewhat works but the output has a '3' in the box.  I'd like to have the "Orion.Group.StatusDescription" of 'Warning' in place of the 3.

SELECT
G.Status,
G.Status AS [_IconFor_Service]

FROM Orion.Groups G
FULL JOIN Orion.GroupCustomProperties gcp ON g.ContainerID=gcp.ContainerID
WHERE g.ContainerID = '495'

** SWQL - Example 2 **

The other option I've tried is using the StatusDescription column.  This allows the WARNING to be displayed, but now I can't adjust the Warning/Critical threshold options to turn the KPI box different colors based on the status (Warning = Orange, Critical = Red)

SELECT
G.StatusDescription AS Status,
G.StatusDescription AS [_IconFor_Service],
FROM Orion.Groups G
FULL JOIN Orion.GroupCustomProperties gcp ON g.ContainerID=gcp.ContainerID
WHERE g.ContainerID = '495'

Lofstrand

Hi, looks like that is not possible. 

The best option I was able to find is below with a table widget:

SELECT
    CASE
        WHEN G.Status=1 THEN 0 -- OK
        WHEN G.Status=3 THEN 2 -- Warning
        WHEN G.status=14 THEN 3 -- Crtitical
        WHEN G.status=2 THEN 4 -- Down
        ELSE 1 -- Blue
    END AS GroupStatus
    ,G.StatusDescription
FROM Orion.Groups AS G
WHERE
    G.ContainerID=1