4 Replies Latest reply on Jun 22, 2016 12:36 PM by bobteal

    SWQL Union command is only showing the first select query

    dkeyser

      Hello all,

       

      We've made great progress toward our end goal for the custom SWQL resource we are wanting. We have the down nodes showing and the interfaces that are down or flapping showing in two separate resources. The NOC would like these combined and sorted by trigger date/time. Figured it'd be as simple as a Union like SQL but we're seeing something unexpected.

       

      Upon setting up the Union as such:

       

      select all the things from

      (select kung from fu

      UNION

      (select foo from bar))

      Order by timedatestamp

       

      We are finding that it's only displaying the first select results. So if the interfaces query comes first it'll only show the interfaces in the resource, if the nodes query comes first then it'll show all down nodes only.

       

      Is there something I'm not getting about the union operator or maybe setting this up incorrectly?

       

      Your help is much appreciated.

      P.S. I've used the following examples as the framework: Custom SWQL resource, Re: Custom SWQL Query Resource Help

        • Re: SWQL Union command is only showing the first select query
          bluefunelemental

          MIght be best to post your swql or even better- do it first in sql to confirm your compliant then try to translate to SWQL- I find this removes a lot of trouble where SWQL just doesn't Like my functions But the script is fine.


          Rules to union data:

          • Each query must have the same number of columns
          • Each column must have compatible data types
          • Column names for the final result set are taken from the first query
          • ORDER BY and COMPUTE clauses can only be issued for the overall result set and not within each individual result set
          • GROUP BY and HAVING clauses can only be issued for each individual result set and not for the overall result set
          • use a NuLL for any columns needed so you have an equal number of columns in each section

           

          http://www.mssqltips.com/sqlservertip/1387/joining-data-using-union-and-union-all-in-sql-server/

          1 of 1 people found this helpful
            • Re: SWQL Union command is only showing the first select query
              dkeyser

              We did test it in SSMS and it worked great. Translating it to SWQL and it breaks only in that way. I'll post the whole report tomorrow. Might be something we simply missed.

              • Re: SWQL Union command is only showing the first select query
                dkeyser

                Here is the SQL conversion of our SWQL that is working in SSMS:

                 

                Select Q.Caption, Q.City, Q.Country, Q.[OU_Code], Q.[Trigger Time Stamp], Q.Name, Q.[Acknowledged by], Q.Notes, Q.OrionServer

                from

                (

                SELECT n.Caption AS Caption, cp.City AS City, cp.Country AS Country, cp.[OU_Code] AS [OU_Code],  a.TriggerTimeStamp AS 'Trigger Time Stamp',

                d.Name AS Name, a.Acknowledgedby AS 'Acknowledged by', a.Notes as Notes,

                o.Name as OrionServer

                FROM EOC_AlertStatus a

                JOIN EOC_AlertDefinition d ON a.AlertDefID = d.AlertDefID

                JOIN EOC_Nodecore n ON n.NodeID = a.ActiveObject AND a.ObjectType = 'Node' and n.OrionID = a.OrionID

                JOIN EOC_orion o ON o.OrionID = a.OrionID

                JOIN EOC_NodeCustomProperties cp on n.NodeID = cp.NodeID and cp.OrionID = n.OrionID

                 

                 

                 

                 

                 

                 

                UNION ALL

                 

                (

                SELECT i.FullName AS Caption, cp.city AS City, cp.country AS Country, cp.[ou_code] AS [OU_Code], a.triggertimestamp AS 'Trigger Time Stamp',

                d.name AS Name, a.acknowledgedby AS 'Acknowledged by', a.notes AS Notes, o.name as OrionServer

                from eoc_alertstatus a

                join eoc_alertdefinition d on a.alertdefid=d.alertdefid

                join eoc_interfacecore i on i.interfaceid=a.activeobject and i.orionid=a.orionid

                join eoc_orion o on o.orionid=a.orionid

                join eoc_nodecustomproperties cp on i.nodeid = cp.nodeid and cp.orionid = i.orionid

                join Statusinfo z on z.StatusID = i.Status

                ))  Q

                where (q.name = 'PRI DOWN' OR q.name = 'PRI Flap' OR q.name ='_High Tx/Rx Percent Utilization(1)' or (q.name = 'Node Down'))

                order by Q.[Trigger Time Stamp] desc

                 

                Here is the SWQL query that doesn't work fully:

                 

                SELECT n.Caption, cp.City, cp.Country, cp.[OU_Code],  a.TriggerTimeStamp, d.Name, a.Acknowledgedby, a.Notes,

                o.Name as OrionServer, o.Website + 'Orion/NetPerfMon/Alerts.aspx?NetObject=N%3A' + ToString(n.NodeID) AS [_LinkFor_Name],

                  o.Website + 'Orion/View.aspx?NetObject=N%3A' + ToString(n.NodeID) AS [_LinkFor_Caption], '/images/Small-' + GroupStatus AS [_IconFor_Caption]

                FROM EOC.AlertStatus a

                JOIN EOC.AlertDefinition d ON a.AlertDefID = d.AlertDefID

                JOIN EOC.Node n ON n.NodeID = a.ActiveObject AND a.ObjectType = 'Node' and n.OrionID = a.OrionID

                JOIN EOC.Orion o ON o.OrionID = a.OrionID

                JOIN EOC.Node.CustomProperty cp on n.NodeID = cp.NodeID and cp.OrionID = n.OrionID

                Where (d.name = 'Node Down' and n.status = 2)

                 

                 

                 

                 

                 

                UNION

                 

                (

                SELECT i.FullName as CAPTION, cp.city, cp.country, cp.[ou_code], a.triggertimestamp, d.name, a.acknowledgedby, a.notes, o.name as OrionServer,

                o.Website + 'Orion/NetPerfMon/Alerts.aspx?NetObject=I:' + ToString(i.InterfaceID) AS [_LinkFor_Name],

                  o.website + 'Orion/Interfaces/InterfaceDetails.aspx?NetObject=I:' + Tostring(i.interfaceid) as [_LinkFor_CAPTION],

                '/images/Small-' + z.IconPostfix + '.gif' AS [_IconFor_CAPTION]

                from eoc.alertstatus a

                join eoc.alertdefinition d on a.alertdefid=d.alertdefid

                join eoc.interface i on i.interfaceid=a.activeobject and i.orionid=a.orionid

                join eoc.orion o on o.orionid=a.orionid

                join eoc.node.customproperty cp on i.nodeid = cp.nodeid and cp.orionid = i.orionid

                join EOC.Statusinfo z on z.StatusID = i.Status

                where d.name = 'PRI DOWN' OR d.name = 'PRI Flap' OR d.name = '_High Tx/Rx Percent Utilization(1)'

                )

                order by a.TriggerTimeStamp desc

                 

                Again I appreciate the help on this.

              • Re: SWQL Union command is only showing the first select query
                bobteal

                Was there ever a solution to this issue? I am currently having a similar issue while trying to setup a custom SQL query alert trigger for auditing events. When I use a union it seems to only pull results for the 1st portion of the union.