3 Replies Latest reply on Apr 3, 2017 3:40 PM by tdanner

    Help with SWQL query for alert history

    wes_appelhans

      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

        • Re: Help with SWQL query for alert history
          tdanner

          The Orion.AlertHistory SWIS entity does exist. Are you connected to SWISv2? This entity is only in SWISv3.

            • Re: Help with SWQL query for alert history
              wes_appelhans

              Good catch TDanner I was running on v2.  I just connected to SWISv3 and the query runs however I am now getting the exception message "cannot resolve property details"

               

              2017-04-03 13:23:05,752 [182] ERROR SolarWinds.InformationService.Core.InformationService - (null) Exception caught in method SolarWinds.InformationService.Core.InformationService.RunQuery

              SolarWinds.Data.Query.ParserException: Cannot resolve property Details

                 at SolarWinds.Data.Query.Processor.SemanticChecker.HandleNonQualifiedPropertyName(EntityExpression entityExpression)

                 at SolarWinds.Data.Query.Processor.SemanticChecker.Visit(EntityExpression entityExpression)

                 at SolarWinds.Data.Query.Swql.ParseTreeDuplicator.Visit(BinaryExpression binary)

                 at SolarWinds.Data.Query.Swql.ParseTreeDuplicator.Visit(ColumnExpression column)

                 at SolarWinds.Data.Query.Processor.SemanticChecker.Visit(ColumnExpression column)

                 at SolarWinds.Data.Query.Swql.ParseTreeDuplicatorNoState.Visit(SelectClause select)

                 at SolarWinds.Data.Query.Processor.SemanticChecker.Visit(SelectStatement statement)

                 at SolarWinds.Data.Query.Processor.SemanticChecker.Process(SelectStatement statement, PreProcessorState state)

                 at SolarWinds.Data.Query.Processor.PreProcessor.Process(SelectStatement selectStatement, PreProcessorState& preProcessorState)

                 at SolarWinds.Data.Query.Engine.QueryProcessor.ProcessInternal(String query, IQueryExecutionContext context)

                 at SolarWinds.InformationService.Core.QueryPlanCache.GetQueryPlan(String query, String username, Boolean federationEnabled, Func`1 buildQueryPlan)

                 at SolarWinds.InformationService.Core.InformationService.RunQuery(String query, IDictionary`2 parameters, IDataSerializer serializer)

                • Re: Help with SWQL query for alert history
                  tdanner

                  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".

                  1 of 1 people found this helpful