This discussion has been locked. The information referenced herein may be inaccurate due to age, software updates, or external references.

You can no longer post new replies to this discussion. If you have a similar question you can start a new discussion in this forum.

SWQL Union Script Help Please

mfoxNDX over 3 years ago

I have the following SWQL script. This was working fine until we upgraded our Orion modules, then something broke. It's a union query of two separate, smaller queries. The two queries work indendently of the union. It's like the full query has a problem with the "(" and ")" surounding the second query that is unioned to the first query. Any help would be appreciated.

--Nodes with MUTE turned ON
SELECT
Nodes.Caption AS [Device],
Nodes.DetailsURL AS [_LinkFor_Device],
'/Orion/images/StatusIcons/Small-' + StatusIcon AS [_IconFor_Device],
Nodes.IP_Address AS [IP],
Nodes.DetailsURL AS [_LinkFor_IP],
DATETRUNC('MINUTE', Alerts.SuppressFrom) AS [Mute from],
DATETRUNC('MINUTE', Alerts.SuppressUntil) AS [Mute Until],
NULL AS [Custom m_Mute],
Nodes.CustomProperties.Comments

FROM Orion.AlertSuppression AlertS
JOIN Orion.nodes ON Nodes.uri = AlertS.EntityURI

UNION

(SELECT
Nodes.caption AS [Device],
Nodes.DetailsURL AS [_LinkFor_Device],
'/Orion/images/StatusIcons/Small-' + StatusIcon AS [_IconFor_Device],
Nodes.IP_Address AS [IP],
Nodes.DetailsURL AS [_LinkFor_IP],
NULL AS [Mute from],
NULL AS [Mute Until],
Nodes.CustomProperties.m_Mute AS [Custom m_Mute],
Nodes.CustomProperties.Comments

FROM Orion.Nodes

WHERE
Nodes.Status != '2'
AND Nodes.CustomProperties.m_Mute = 'TRUE')

Parents

0 mesverrum over 3 years ago

Add Order by Device to the end of your query and it will work, just tested in my environment. At some point the custom query resource started getting fussy about rejecting more complex SQL like unions and some forms of subqueries unless you tell it how to order them. 99% of the time if I can run it in SWQL studio but can't run it on the web console that's why.
Cancel
Vote Up 0 Vote Down

Cancel

Reply

0 mesverrum over 3 years ago

Add Order by Device to the end of your query and it will work, just tested in my environment. At some point the custom query resource started getting fussy about rejecting more complex SQL like unions and some forms of subqueries unless you tell it how to order them. 99% of the time if I can run it in SWQL studio but can't run it on the web console that's why.
Cancel
Vote Up 0 Vote Down

Cancel

Children

No Data