Community
- Command Central
- MVP Program
- Monthly Mission
- Blogs
- Groups
- Events
- Media Vault
Products
- Observability
- Network Management
- Application Management
- IT Security
- IT Service Management
- System Management
- Database Management
Content Exchange
- SolarWinds Platform
- Server & Application Monitor
- Database Performance Analyzer
- Server Configuration Monitor
- Network Performance Monitor
- Network Configuration Manager
- SQL Sentry
- Web Help Desk
Free Tools & Trials

SWQL Union command is only showing the first select query

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

Find more posts tagged with

SWQL

Accepted answers

All comments

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/

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.

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.

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.