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
  • If you want to try it without the inner joins here is what I tried, still can't get the Case part going though

    SELECT
    [AlertHistory].DisplayName AS [Alert Name],
    [AlertHistory].AlertObjects.AlertConfigurations.AlertID AS [Alert ID],
    [AlertHistory].AlertObjects.AlertConfigurations.Severity AS Sev,
    COUNT( [AlertHistory].AlertHistoryID ) AS [Last 30 Days]
    --CASE
    --When AlertHistory.AlertObjects.AlertConfigurations.Severity LIKE 'a' then 'Warning'
    --When AlertHistory.AlertObjects.AlertConfigurations.Severity LIKE 'b' then  'Critical'
    --ELSE AlertHistory.AlertObjects.AlertConfigurations.Severity
    --end AS [jjj]
    
    FROM Orion.AlertHistory
    
    --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 AlertConfigurations.AlertID

  • I think if you use the like, you need to use the % so like ‘%a’ if there are characters before but not after or like ‘a%’ if there are no characters before but after or like ‘%a%’ if there are characters before and after

Reply Children
  • Got it working 

    SELECT
    [AlertHistory].DisplayName AS [Alert Name],
    [AlertHistory].AlertObjects.AlertConfigurations.AlertID AS [Alert ID],
    [AlertHistory].AlertObjects.AlertConfigurations.Severity AS Sev,
    COUNT( [AlertHistory].AlertHistoryID ) AS [Last 30 Days],
    CASE
    
    When AlertHistory.AlertObjects.AlertConfigurations.Severity LIKE '1' then 'Warning'
    When AlertHistory.AlertObjects.AlertConfigurations.Severity LIKE '2' then  'Critical'
    ELSE ToString (AlertHistory.AlertObjects.AlertConfigurations.Severity)
    end AS [Result]
      
    FROM Orion.AlertHistory
    
    --WHERE EventType = 0 and  [AlertHistory].AlertObjects.AlertConfigurations.AlertID AS [Alert ID] IN ('714','618','735','732','941','942')  --AND  OAH.TimeStamp > GetUtcDate()-30
    
    WHERE AlertHistory.TimeStamp > GetUtcDate()-30
    GROUP BY [AlertHistory].AlertObjects.AlertID

  • Got it working 

    SELECT
    [AlertHistory].DisplayName AS [Alert Name],
    [AlertHistory].AlertObjects.AlertConfigurations.AlertID AS [Alert ID],
    [AlertHistory].AlertObjects.AlertConfigurations.Severity AS Sev,
    COUNT( [AlertHistory].AlertHistoryID ) AS [Last 30 Days],
    CASE
    
    When AlertHistory.AlertObjects.AlertConfigurations.Severity LIKE '1' then 'Warning'
    When AlertHistory.AlertObjects.AlertConfigurations.Severity LIKE '2' then  'Critical'
    ELSE ToString (AlertHistory.AlertObjects.AlertConfigurations.Severity)
    end AS [Result]
      
    FROM Orion.AlertHistory
    
    --WHERE EventType = 0 and  [AlertHistory].AlertObjects.AlertConfigurations.AlertID AS [Alert ID] IN ('714','618','735','732','941','942')  --AND  OAH.TimeStamp > GetUtcDate()-30
    
    WHERE AlertHistory.TimeStamp > GetUtcDate()-30
    GROUP BY [AlertHistory].AlertObjects.AlertID