SWQL CASE Function

i would like to display the severity of each alarm , i noticed that the value returned is a number and i'm working on a way to change the value into something else .

i tried the CASE function as shown belwon but i'm getting error in the syntax:

SELECT

OAH.AlertObjects.AlertConfigurations.DisplayName AS [Alert Name]

, OAH.AlertObjects.AlertConfigurations.AlertID AS [Alert ID]

, COUNT( AlertHistoryID ) AS [Last 30 Days]

, CASE

When AC.Severity=1 then 'Warning'

When AC.Severity=2 then  'Critical'

ELSE AC.Severity

end AS [ Criticité ]

FROM Orion.AlertHistory OAH

INNER JOIN Orion.AlertConfigurations as AC on AC.AlertID = OAH.AlertObjects.AlertConfigurations.AlertID

WHERE EventType = 0 and  oah.AlertObjects.AlertConfigurations.AlertID IN ('714','618','735','732','941','942')  AND  OAH.TimeStamp > GetUtcDate()-30

GROUP BY oah.AlertObjects.AlertConfigurations.AlertID

this CODE is working Fine without the CASE function.

Thanks for any help you can offer.

Parents Reply Children
  • Hey Adam thanks for answering, im facing an error while processing this request.

    i need to retreive a Custom Property when the alert is based on agroup. The whole SWQL works fine until i add this one:


    , CASE
        When AO.RealEntityType= 'Orion.Groups'  then  ( SELECT  N.CustomProperties.Type_Node   FROM Orion.Nodes AS N LEFT JOIN Orion.ContainerMemberSnapshots AS CMS ON N.NodeID=CMS.EntityID LEFT JOIN Orion.Container AS C ON C.ContainerID=CMS.ContainerID WHERE C.Name = 'GRP_APLM' )
        ELSE AO.RelatedNodeCaption
    end AS [ TEST Value]

    when i test the SELECT part alone in a custom query widget i retreive the value i'm looking for , but the moment i nest this code between () i get an error.

    thanks in advance for your time and help.

  • This is a bit of a brainmelter. Does the subquery return exactly 1 "cell". 1 row 1 column?

    If no, that's your problem. If so.. not sure offhand what the issue is. The error might help.

    This structure is very unfamiliar to me. I think i'd do something to try and join all the data together (maybe a jazzy right join to groups), then wrap that in a subquery to filter it down to the thing I needed. Perhaps that doesnt apply to your case, hard to tell and also you can usually do this stuff in a bunch of different ways, and mine isnt better just familiar.

  • YEs it does return one value.

    To me it seems like the nested SELECT is not supproted inside CASE function.

    i have tested this with a simple SELECT query with not luck:

    , CASE

        When AO.RealEntityType= 'Orion.Groups'  then  ( SELECT  N.CustomProperties.Type_Node

    FROM Orion.Nodes AS N Where N.IPAddress= '192.168.0.23' )

        ELSE AO.RelatedNodeCaption

    end AS [ TEST Value]

  • I think none of this is "supported" as such, but this query from earlier should prove it's possible


    select top 1 n.NodeID, CASE when n.nodeid = 3 then (select top 1 '1' as one from orion.nodes n) else 99 end as test from orion.nodes n

    Could you supply either a bit more of the code (with some blanked values) and/or the error?