SWQL & SQL query versions NOT working in Report

My experience mostly lies with writing SQL queries. I am trying to learn more about translating those to SWQL moving forward. However, this one has me puzzled, I can't seem to get either version to work in a Report and I need some help.

The goal of the query is to collect all IPs our Orion environment is aware and display them with their related Node. Data is collected from the NodeIPAddresses table, the Nodes (NodesData) table and from APM_ComponentSetting (for PS script arguments where we have provided an IP that the Component PS script tests for IP response). Duplicates are stripped out and the results are combined and ordered by Node caption.

SQL Version

The SQL query below works perfectly fine in SSMS but fails in the Report with "Query is not valid":

(SELECT nd.Caption
        ,nd.SysName
        ,nip.IPAddress
        ,ncp.Node_Location
        ,ncp.Node_Reality
        ,ncp.Business_Unit
        ,CASE
                WHEN ncp.Comments IS NULL THEN ''
                ELSE ncp.Comments
                END AS 'Temporary Comment'
        ,ncp.Node_CIT_Responsible_Team
        ,ncp.Device_Type
FROM SolarWindsOrion.dbo.NodeIPAddresses nip
JOIN SolarWindsOrion.dbo.NodesData nd on nd.NodeID = nip.NodeID
JOIN SolarWindsOrion.dbo.NodesCustomProperties ncp on ncp.NodeID = nd.NodeID
WHERE ncp.Active_Node = 1 AND nip.IPAddress NOT LIKE '%::%')

UNION

(SELECT nd.Caption
        ,nd.SysName
        ,nd.IP_Address
        ,ncp.Node_Location
        ,ncp.Node_Reality
        ,ncp.Business_Unit
        ,CASE
                WHEN ncp.Comments IS NULL THEN ''
                ELSE ncp.Comments
                END AS 'Temporary Comment'
        ,ncp.Node_CIT_Responsible_Team
        ,ncp.Device_Type
FROM SolarWindsOrion.dbo.NodesData nd
JOIN SolarWindsOrion.dbo.NodesCustomProperties ncp on ncp.NodeID = nd.NodeID
WHERE ncp.Active_Node = 1 AND nd.IP_Address NOT LIKE '%::%')

UNION

(SELECT nd.Caption
        ,nd.SysName
        ,SUBSTRING(apmcs.Value,0,CHARINDEX(',',apmcs.Value)) AS 'IPAddress'
        ,ncp.Node_Location
        ,ncp.Node_Reality
        ,ncp.Business_Unit
        ,CASE
                WHEN ncp.Comments IS NULL THEN ''
                ELSE ncp.Comments
                END AS 'Temporary Comment'
        ,ncp.Node_CIT_Responsible_Team
        ,ncp.Device_Type
FROM SolarWindsOrion.dbo.APM_ComponentSetting apmcs
JOIN SolarWindsOrion.dbo.APM_Component apmc ON apmc.ID = apmcs.ComponentID
JOIN SolarWindsOrion.dbo.APM_Application apma ON apma.ID = apmc.ApplicationID
JOIN SolarWindsOrion.dbo.NodesData nd ON nd.NodeID = apma.NodeID
JOIN SolarWindsOrion.dbo.NodesCustomProperties ncp on ncp.NodeID = nd.NodeID
WHERE ncp.Active_Node = 1 AND apma.Name LIKE '%Communication Health%' AND apmcs.[Key] = 'ScriptArguments')

ORDER BY Caption ASC

The first two SELECT statements in the above run just fine, by themselves in the Report. The third fails with "Query is not valid". I was able to narrow down the cause of this to the fact that the following SQL query fails in the Report:

SELECT apmcs.Value
FROM SolarWindsOrion.dbo.APM_ComponentSetting apmcs

If I remove references to the APM_ComponentSetting table in the bottom SQL SELECT statement in the top query, as shown below, I get results in the Report:

SELECT nd.Caption
        ,nd.SysName
        ,ncp.Node_Location
        ,ncp.Node_Reality
        ,ncp.Business_Unit
        ,CASE
                WHEN ncp.Comments IS NULL THEN ''
                ELSE ncp.Comments
                END AS 'Temporary Comment'
        ,ncp.Node_CIT_Responsible_Team
        ,ncp.Device_Type
FROM SolarWindsOrion.dbo.APM_Component apmc
JOIN SolarWindsOrion.dbo.APM_Application apma ON apma.ID = apmc.ApplicationID
JOIN SolarWindsOrion.dbo.NodesData nd ON nd.NodeID = apma.NodeID
JOIN SolarWindsOrion.dbo.NodesCustomProperties ncp on ncp.NodeID = nd.NodeID
WHERE ncp.Active_Node = 1 AND apma.Name LIKE '%Communication Health%'

I then replaced that bottom query with the new SELECT statement directly above, adding `NULL AS 'IPAddress', coming up with the following SQL query that succeeds both in SSMS and the Report:

(SELECT nd.Caption
        ,nd.SysName
        ,nip.IPAddress
        ,ncp.Node_Location
        ,ncp.Node_Reality
        ,ncp.Business_Unit
        ,CASE
                WHEN ncp.Comments IS NULL THEN ''
                ELSE ncp.Comments
                END AS 'Temporary Comment'
        ,ncp.Node_CIT_Responsible_Team
        ,ncp.Device_Type
FROM SolarWindsOrion.dbo.NodeIPAddresses nip
JOIN SolarWindsOrion.dbo.NodesData nd on nd.NodeID = nip.NodeID
JOIN SolarWindsOrion.dbo.NodesCustomProperties ncp on ncp.NodeID = nd.NodeID
WHERE ncp.Active_Node = 1 AND nip.IPAddress NOT LIKE '%::%')

UNION

(SELECT nd.Caption
        ,nd.SysName
        ,nd.IP_Address
        ,ncp.Node_Location
        ,ncp.Node_Reality
        ,ncp.Business_Unit
        ,CASE
                WHEN ncp.Comments IS NULL THEN ''
                ELSE ncp.Comments
                END AS 'Temporary Comment'
        ,ncp.Node_CIT_Responsible_Team
        ,ncp.Device_Type
FROM SolarWindsOrion.dbo.NodesData nd
JOIN SolarWindsOrion.dbo.NodesCustomProperties ncp on ncp.NodeID = nd.NodeID
WHERE ncp.Active_Node = 1 AND nd.IP_Address NOT LIKE '%::%')

UNION

(SELECT nd.Caption
        ,nd.SysName
		,NULL AS 'IPAddress'
        ,ncp.Node_Location
        ,ncp.Node_Reality
        ,ncp.Business_Unit
        ,CASE
                WHEN ncp.Comments IS NULL THEN ''
                ELSE ncp.Comments
                END AS 'Temporary Comment'
        ,ncp.Node_CIT_Responsible_Team
        ,ncp.Device_Type
FROM SolarWindsOrion.dbo.APM_Component apmc
JOIN SolarWindsOrion.dbo.APM_Application apma ON apma.ID = apmc.ApplicationID
JOIN SolarWindsOrion.dbo.NodesData nd ON nd.NodeID = apma.NodeID
JOIN SolarWindsOrion.dbo.NodesCustomProperties ncp on ncp.NodeID = nd.NodeID
WHERE ncp.Active_Node = 1 AND apma.Name LIKE '%Communication Health%')

ORDER BY Caption ASC

I have therefore concluded that the Report does not like to use the APM_ComponentSetting table. But why? Is there some way to make this work using SQL?

SWQL Version:

The SWQL version translated from the top most SQL query shown in this article and which works perfectly fine in SWQL Studio, also fails in the Report with "Query is not valid":

SELECT nd.Caption
        ,nd.SysName
        ,nip.IPAddress
        ,ncp.Node_Location
        ,ncp.Node_Reality
        ,ncp.Business_Unit
        ,CASE
                WHEN ncp.Comments IS NULL THEN ''
                ELSE ncp.Comments
                END AS [Temporary Comment]
        ,ncp.Node_CIT_Responsible_Team
        ,ncp.Device_Type
FROM Orion.NodeIPAddresses nip
JOIN Orion.Nodes nd on nd.NodeID = nip.NodeID
JOIN Orion.NodesCustomProperties ncp on ncp.NodeID = nd.NodeID
WHERE ncp.Active_Node = 1 AND nip.IPAddress NOT LIKE '%::%'

UNION

(SELECT nd.Caption
        ,nd.SysName
        ,nd.IP_Address
        ,ncp.Node_Location
        ,ncp.Node_Reality
        ,ncp.Business_Unit
        ,CASE
                WHEN ncp.Comments IS NULL THEN ''
                ELSE ncp.Comments
                END AS [Temporary Comment]
        ,ncp.Node_CIT_Responsible_Team
        ,ncp.Device_Type
FROM Orion.Nodes nd
JOIN Orion.NodesCustomProperties ncp on ncp.NodeID = nd.NodeID
WHERE ncp.Active_Node = 1 AND nd.IP_Address NOT LIKE '%::%')

UNION

(SELECT nd.Caption
        ,nd.SysName
        ,SUBSTRING(apmcs.Value,0,CHARINDEX(',',apmcs.Value)) AS [IP_Address]
        ,ncp.Node_Location
        ,ncp.Node_Reality
        ,ncp.Business_Unit
        ,CASE
                WHEN ncp.Comments IS NULL THEN ''
                ELSE ncp.Comments
                END AS [Temporary Comment]
        ,ncp.Node_CIT_Responsible_Team
        ,ncp.Device_Type
FROM Orion.APM.ComponentSetting apmcs
JOIN Orion.APM.Component apmc ON apmc.ComponentID = apmcs.ComponentID
JOIN Orion.APM.Application apma ON apma.ID = apmc.ApplicationID
JOIN Orion.Nodes nd ON nd.NodeID = apma.NodeID
JOIN Orion.NodesCustomProperties ncp on ncp.NodeID = nd.NodeID
WHERE ncp.Active_Node = 1 AND apma.Name LIKE '%Communication Health%' AND apmcs.[Key] = 'ScriptArguments')

ORDER BY Caption ASC

In this case, each individual SELECT statement, including the one using the APM.ComponentSetting table succeeds on its own. To try to narrow on the problem with this SWQL query, I next narrowed the query down to just the first two SELECT statements and included the ORDER BY which still resulted in "Query is not valid":

SELECT nd.Caption
,nd.SysName
,nip.IPAddress
,ncp.Node_Location
,ncp.Node_Reality
,ncp.Business_Unit
,CASE
WHEN ncp.Comments IS NULL THEN ''
ELSE ncp.Comments
END AS [Temporary Comment]
,ncp.Node_CIT_Responsible_Team
,ncp.Device_Type
FROM Orion.NodeIPAddresses nip
JOIN Orion.Nodes nd on nd.NodeID = nip.NodeID
JOIN Orion.NodesCustomProperties ncp on ncp.NodeID = nd.NodeID
WHERE ncp.Active_Node = TRUE AND nip.IPAddress NOT LIKE '%::%'

UNION

(SELECT nd.Caption
,nd.SysName
,nd.IP_Address
,ncp.Node_Location
,ncp.Node_Reality
,ncp.Business_Unit
,CASE
WHEN ncp.Comments IS NULL THEN ''
ELSE ncp.Comments
END AS [Temporary Comment]
,ncp.Node_CIT_Responsible_Team
,ncp.Device_Type
FROM Orion.Nodes nd
JOIN Orion.NodesCustomProperties ncp on ncp.NodeID = nd.NodeID
WHERE ncp.Active_Node = TRUE AND nd.IP_Address NOT LIKE '%::%')

ORDER BY Caption ASC

It appears to me that the Report in this case is not liking the UNION. I found the article SWQL Union Script Help Please which said including the ORDER BY should fix the problem but I already have included ORDER BY. Does anyone see why this SWQL query is not working?

THANKS

Mike

Find more posts tagged with

report writer

orion

SWQL

orion sdk

Accepted answers

All comments

Mike_Lomax

Does anyone have any ideas on this issue? Help would be greatly appreciated.

THANKS

Mike

Mike_Lomax

I still could really use some help with this one. Any ideas on what could be causing this?

Interestingly, I can add a Custom Query widget, say to the NPM Summary page, add the query into that and get the results I am expecting. I tried the same with Custom Table and it fails just like inside a Report.

KMSigma.SWI

I don't have that particular SAM Application/Component, so I can't dig in much, but what I've found helpful is to reduce the number of JOINs. (See Decreasing JOINs - the Power of Navigation Properties )

-- "Outer Query" which pulls data from the inner UNION
-- I'm *pretty* sure this is what's required if you want to sort on a UNION
SELECT [OuterQuery].Caption
	,[OuterQuery].SysName
	,[OuterQuery].IPAddress
	,[OuterQuery].[Temporary Comment]
FROM (
	-- Node and its polling IP Address
	SELECT [Nodes].Caption
		,[Nodes].SysName
		,[Nodes].IPAddress
		,IsNull([Nodes].CustomProperties.Comments, '') AS [Temporary Comment]
	FROM Orion.Nodes AS [Nodes]
	WHERE [Nodes].IPAddress NOT LIKE '%::%'

	UNION

	(
		-- Node an its other IPs
		SELECT [Nodes].Caption
			,[Nodes].SysName
			,[Nodes].NodeIPAddresses.IPAddress
			,IsNull([Nodes].CustomProperties.Comments, '') AS [Temporary Comment]
		FROM Orion.Nodes AS [Nodes]
		WHERE [Nodes].NodeIPAddresses.IPAddress NOT LIKE '%::%'
		)
	) AS [OuterQuery]
ORDER BY [OuterQuery].Caption

I also don't have the same custom properties, but you should be able to get the idea from my above query as a starting point.