SWQL CASE Function

CARION 2 months ago

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.

Top Replies

Parents

0 bobmarley 1 month ago

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

0 dodo123 1 month ago in reply to bobmarley

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
Cancel
Up 0 Down

Reply

Verify Answer

Cancel

0 bobmarley 1 month ago in reply to dodo123

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

0 bobmarley 1 month ago in reply to dodo123

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

Reply

0 bobmarley 1 month ago in reply to dodo123

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

Children

No Data