7 Replies Latest reply on Aug 10, 2017 12:33 AM by tqcenter2002c

    How can I alert on a SWQL Query

    tqcenter2002c

      Hi All,

      I have already created a working SWQL query and tested to be OK. I get a table back and it looks good.

      But, When I try to create an alert on it I am getting an error.

       

      Please let me know what I am missing here? I have checked the SWQL and I got a table back with no issues at all.

      I need to create an alert on that and I can't.

       

      Please advise,

      Thank You

        • Re: How can I alert on a SWQL Query
          uniswtc

          Hi, it would be great, if you can provide the query and a screen shot where you want to create the alert.

            • Re: How can I alert on a SWQL Query
              tqcenter2002c

              Hi,

              I am trying to use this in Trigger condition of a new alert. Using the I want to alert on: (drop down menu) Custom SWQL Alert (Advanced)

              The I need to setup my SWQL condition - there is a drop down menu from there as well. The Following SWQL(enclosed down below) Works perfectly with NO ERRORS on the swiss.aspx on the server. When I paste the SWQL there and Click validate I am getting ERROR as I have posted the screen the last post.

               

              This is the SWQL:

               

              SELECT

              ObjStat.[DisplayName] as DisplayName,

              adminState.[Status] as adminState,

              ObjStat.[Status] as ObjectStat

               

              FROM

              Orion.NPM.CustomPollerStatusOnNodeTabular AS ObjStat,

              Orion.NPM.CustomPollerStatusOnNodeTabular AS adminState

              where   ObjStat.[DisplayName] = adminState.[DisplayName]

                                                              AND       ObjStat.[CustomPollerAssignment].[CustomPoller].[UniqueName] = 'DataPower_ObjectStatusTbl'

                                                              AND       adminState.[CustomPollerAssignment].[CustomPoller].[UniqueName]  = 'DataPower_ObjectAdminStateStatusTbl'

              AND       adminState.[Status] = 1 AND ObjStat.[Status]=2

               

              I am doing a polling using a UnDP Pollers: DataPower_ObjectAdminStateStatusTbl and DataPower_ObjectStatusTbl - both are two tables I get using a poller on 2 MiBs/

               

              Please advise,

              Thanks.

                • Re: How can I alert on a SWQL Query
                  I LIKE EGGS

                  Hey,

                   

                  when working with the SWQL or SQL you need to join your table on to the the predefined select and from statement. your have to remove your select statement because you will be using two select statements and it will error out. the challenge would be that the current query  doesn't have a unique ID of some type so it will need to be reworked to fit the select statement below.

                    • Re: How can I alert on a SWQL Query
                      I LIKE EGGS

                      here is an example:

                       

                       WITH (NOLOCK)
                       INNER JOIN CustomPollerAssignmentView cv WITH (NOLOCK) ON cv.CustomPollerAssignmentID = CustomPollerStatusTable.CustomPollerAssignmentID
                       WHERE CustomPollerName IN ('DataPower_ObjectAdminStateStatusTbl','DataPower_ObjectStatusTbl')
                       AND (Status = 2) 
                      
                        • Re: How can I alert on a SWQL Query
                          tqcenter2002c

                          Thanks,

                          you have used SQL and not SWQL, Why? And also trying the same as you did (copy and paste) and Sill getting conditions errors.

                          I have read your example and I am not sure that I understand the logic behind it.

                          surely I am not even sure that it resembles the conditions in my query

                           

                          My Query:

                          1. SELECT
                          2. ObjStat.[DisplayName] as DisplayName,
                          3. adminState.[Status] as adminState,
                          4. ObjStat.[Status] as ObjectStat
                          5. FROM
                          6. Orion.NPM.CustomPollerStatusOnNodeTabular AS ObjStat,
                          7. Orion.NPM.CustomPollerStatusOnNodeTabular AS adminState
                          8. where   ObjStat.[DisplayName] = adminState.[DisplayName]
                          9.                                                 AND       ObjStat.[CustomPollerAssignment].[CustomPoller].[UniqueName] = 'DataPower_ObjectStatusTbl'
                          10.                                                 AND       adminState.[CustomPollerAssignment].[CustomPoller].[UniqueName]  = 'DataPower_ObjectAdminStateStatusTbl'
                          11. AND       adminState.[Status] = 1 AND ObjStat.[Status]=2

                           

                          Why did you use the

                          1. WITH (NOLOCK) 

                          And how do I inner join the two together using the following?

                          1. INNERJOIN CustomPollerAssignmentView cv WITH (NOLOCK) ON cv.CustomPollerAssignmentID = CustomPollerStatusTable.CustomPollerAssignmentID 
                          2. WHERE CustomPollerName IN ('DataPower_ObjectAdminStateStatusTbl','DataPower_ObjectStatusTbl'

                          And I have two conditioned in line 12 when the adminstate is 1 and object status is 1

                          1. AND (Status = 2)  

                           

                          I would be happy if you could assist with the query to meet the example I have sent as much as possible if that is doable.

                          Thank you

                            • Re: How can I alert on a SWQL Query
                              I LIKE EGGS

                              yo, could you run this is SWQL Studio ?

                               

                              SELECT CustomPollerStatusOnNodeTabular.Uri, CustomPollerStatusOnNodeTabular.DisplayName,Name.CustomPollerName,adminState.[Status] AS [Admin Status],ObjStat.[Status] AS [Object Status]  FROM Orion.NPM.CustomPollerStatusOnNodeTabular AS CustomPollerStatusOnNodeTabular
                              INNER JOIN Orion.NPM.CustomPollerStatusOnNodeTabular AS ObjStat ON ObjStat.CustomPollerAssignmentID = CustomPollerStatusOnNodeTabular.CustomPollerAssignmentID
                              INNER JOIN Orion.NPM.CustomPollerStatusOnNodeTabular AS adminState ON adminState.CustomPollerAssignmentID = CustomPollerStatusOnNodeTabular.CustomPollerAssignmentID
                              INNER JOIN Orion.NPM.CustomPollerAssignment AS Name ON Name.CustomPollerAssignmentID = CustomPollerStatusOnNodeTabular.CustomPollerAssignmentID
                              WHERE 
                              
                              (Name.CustomPollerName = 'DataPower_ObjectAdminStateStatusTbl' OR Name.CustomPollerName =  'DataPower_ObjectStatusTbl')
                              
                              AND 
                              (adminState.[Status] = '1'  AND ObjStat.[Status] = '2' )
                              

                               

                               

                              and run this in the alert?

                               

                              INNER JOIN Orion.NPM.CustomPollerStatusOnNodeTabular AS ObjStat ON ObjStat.CustomPollerAssignmentID = CustomPollerStatusOnNodeTabular.CustomPollerAssignmentID
                              INNER JOIN Orion.NPM.CustomPollerStatusOnNodeTabular AS adminState ON adminState.CustomPollerAssignmentID = CustomPollerStatusOnNodeTabular.CustomPollerAssignmentID
                              INNER JOIN Orion.NPM.CustomPollerAssignment AS Name ON Name.CustomPollerAssignmentID = CustomPollerStatusOnNodeTabular.CustomPollerAssignmentID
                              WHERE 
                              (Name.CustomPollerName = 'DataPower_ObjectAdminStateStatusTbl' OR Name.CustomPollerName =  'DataPower_ObjectStatusTbl')
                              AND (adminState.[Status] = '1'  AND ObjStat.[Status] = '2' )
                              

                               

                              for example:

                               

                              you have used SQL and not SWQL, Why? 

                               

                              using SQL is just my preference

                               

                              Why did you use the?

                              WITH (NOLOCK) information To NOLOCK or not to NOLOCK, that is the question!

                               

                              yes you were join on the Where clause i wouldn't really recommend this and its but still works and its down to preference. it is also easy for me to read SELECT  then FROM then WHERE its must also have some benefits on queries because this is using the SQL order of execution..  https://www.periscopedata.com/blog/sql-query-order-of-operations