Help with SWQL query for alert history

Question

Hello Everyone, I have an SQL query that I am trying to convert to SWQL and I am getting the error "Source entity [Orion.AlertHistory] not found in catalog. When I compare SQL studio tables to SWQL studio tables I do not see a Orion.AlertHistory table however in SQL studio the table dbo.AlertHistory does exist. Did I miss something or shouldnt the same tables be present between the two systems? Below is my query:

SELECT ADDDATE('HOUR',-7,AlertHistory.[TimeStamp]) as "Time"

,AlertObjects.EntityCaption as "Alert"

,AlertObjects.RelatedNodeCaption as "Node"

,"Details" =

Case

When AlertObjects.EntityType = 'Orion.APM.Application' Then 'Application is Down'

When AlertObjects.EntityType = 'Orion.Nodes' Then 'Node did not respond to ICMP call'

When AlertObjects.EntityType = 'Orion.NPM.Interfaces' Then 'Interface is down.'

When AlertObjects.EntityType = 'Orion.Volumes' Then 'Volume has reached a critical threshold'

Else 'Check Event Log for Details'

END

FROM [Orion].[AlertHistory]inner join [Orion].[AlertObjects] on AlertHistory.[AlertObjectID]=AlertObjects.[AlertObjectID]

where ActionID in

(SELECT [ActionID]

FROM [Orion].[Actions]

where Title = 'Execute program : C:\Program Files (x86)\Tools\bin\RATriggerAlarm.CMD')

and TimeStamp > ADDDATE('HOUR',7,DATEADD(DAY,-1,GETDATE()))

Order by TimeStamp desc

tdanner · Accepted Answer

As the fourth column in the SELECT clause, you have this:

"Details" = Case ... END

This is not the normal SWQL syntax for column aliases (which follows standard SQL). It should look like this instead:

Case ... END AS Details

I found another issue. This function call works in SQL, but not SWQL: DATEADD(DAY,-1,GETDATE()). To fix it for SWQL, make it this:

ADDDATE('day', -1, GETDATE())

With those two changes, this query works on my system. (It doesn't return any data, but that's because my alerts don't match what you are expecting):

SELECT ADDDATE('HOUR',-7,AlertHistory.[TimeStamp]) as "Time"
      ,AlertObjects.EntityCaption as "Alert"
      ,AlertObjects.RelatedNodeCaption as "Node"
      ,
      Case
      When AlertObjects.EntityType = 'Orion.APM.Application' Then 'Application is Down'
      When AlertObjects.EntityType = 'Orion.Nodes' Then 'Node did not respond to ICMP call'
      When AlertObjects.EntityType = 'Orion.NPM.Interfaces' Then 'Interface is down.'
      When AlertObjects.EntityType = 'Orion.Volumes' Then 'Volume has reached a critical threshold'
      Else  'Check Event Log for Details'
      END AS Details
  FROM [Orion].[AlertHistory]inner join [Orion].[AlertObjects] on AlertHistory.[AlertObjectID]=AlertObjects.[AlertObjectID]
   where ActionID in
  (SELECT [ActionID]
FROM [Orion].[Actions]
where Title = 'Execute program : C:\Program Files (x86)\Tools\bin\RATriggerAlarm.CMD')
  and TimeStamp > ADDDATE('HOUR',7,ADDDATE('day',-1,GETDATE())) 
Order by  TimeStamp desc

One more note: preferred style in SWQL is to quote identifiers using [square brackets] and not "double quotes".

Help with SWQL query for alert history

Top Replies