Help IN statement not working as expected

Hey everyone, i'm trying to cound the triggered alerts in a web based report for several Alerts, but i noticed that the IN statement takes only the first value inside parenthesis.

here's the SWQL code:

SELECT
COUNT( AlertHistoryID ) AS [Alert Count]
, oah.AlertObjects.AlertConfigurations.DisplayName AS [Alert Name]
, oah.AlertObjects.AlertConfigurations.AlertID AS [Alert ID]
, oah.AlertObjects.AlertConfigurations.Frequency AS [Alert Evaluation Frequency (seconds)]

FROM Orion.AlertHistory oah
WHERE EventType = 0 and oah.AlertObjects.AlertConfigurations.AlertID IN ( 514, 537, 535)

thanks in advance

Find more posts tagged with

orion swql

orion_sdk

sqwl

orion sdk

Accepted answers

christopher.t.jones123

@CARION, try this.

SELECT  
  COUNT( AlertHistoryID ) AS [Alert Count]
, oah.AlertObjects.AlertConfigurations.DisplayName AS [Alert Name]
, oah.AlertObjects.AlertConfigurations.AlertID AS [Alert ID]
, oah.AlertObjects.AlertConfigurations.Frequency AS [Alert Evaluation Frequency (seconds)]


FROM Orion.AlertHistory oah
WHERE EventType = 0 and  oah.AlertObjects.AlertConfigurations.AlertID IN ('514','537','535')
GROUP BY oah.AlertObjects.AlertConfigurations.AlertID

All comments

KMSigma.SWI

Are you getting a different result if you convert it to "OR" syntax?

SELECT  
  COUNT( AlertHistoryID ) AS [Alert Count]
, oah.AlertObjects.AlertConfigurations.DisplayName AS [Alert Name]
, oah.AlertObjects.AlertConfigurations.AlertID AS [Alert ID]
, oah.AlertObjects.AlertConfigurations.Frequency AS [Alert Evaluation Frequency (seconds)]


FROM Orion.AlertHistory AS oah
WHERE EventType = 0 AND (
   oah.AlertObjects.AlertConfigurations.AlertID = 514
OR oah.AlertObjects.AlertConfigurations.AlertID = 537
OR oah.AlertObjects.AlertConfigurations.AlertID = 535
)

CARION

Thanks KMSigma for your answer but i'm getting one value using 'OR' syntax also.

KMSigma.SWI

Are you looking to get a count for each individual alert type? Because for that you'd need a GROUP BY.

christopher.t.jones123

@CARION, try this.

SELECT  
  COUNT( AlertHistoryID ) AS [Alert Count]
, oah.AlertObjects.AlertConfigurations.DisplayName AS [Alert Name]
, oah.AlertObjects.AlertConfigurations.AlertID AS [Alert ID]
, oah.AlertObjects.AlertConfigurations.Frequency AS [Alert Evaluation Frequency (seconds)]


FROM Orion.AlertHistory oah
WHERE EventType = 0 and  oah.AlertObjects.AlertConfigurations.AlertID IN ('514','537','535')
GROUP BY oah.AlertObjects.AlertConfigurations.AlertID

CARION

Thanks all for your help, Group By did help me solve my issue.