Hi,
Cross posting as didn't get a response in NPM area:
Thanks
Stuart
Hello Stuart, if I understand you well, your need from Custom Query resource to create expandable tree, or just displaying of the parent entity only once. AFAIK this is at other places in Node Management, etc. it's done by the JavaScript. The Custom query resource everytime gives you the table of results defined by the query. I'd wonder if something like this was even possible in SQL. However there should be possible to achieve it by using the Old Report Writer, make report for the info you need (similar principle to e.g. canned report All Disk Volumes) and add it to your website inside the "Report from Report Writer" resource. In upcoming release there should be also possibility to add the "Custom Table", as known from the Web-based reporting, to the view, which also should do the trick for you.
Regards,
Honza
Hi Honza,
Thanks for the response.
I am not really looking for the list to me expandable as in (like you say) the Node Management view, but to group by node name as the parent, with the interface entity's under. An SQL query would accomplish this with the "group by" clause, however would not display as cleanly as this resource could.
Stuart
Here's a query that returns the data you want. The formatting isn't ideal since Custom Query Resource doesn't give you a way to control the column span of the table cells.
SELECT
n.caption as Node,
'/Orion/images/StatusIcons/Small-' + n.StatusLED AS [_IconFor_Node],
n.ipaddress as IP_Address,
n.ObjectSubType as Polling_Method,
n.StatusDescription as Status,
n.NodeID,
1 AS Kind
FROM orion.nodes n
UNION ALL (
SELECT
I.Caption as Interface,
'/Orion/images/StatusIcons/Small-' + i.StatusLED AS [_IconFor_Interface],
NULL as IP_Address,
NULL as Polling_Method,
NULL as Status,
I.NodeID,
2 AS Kind
FROM Orion.NPM.Interfaces I
) UNION ALL (
SELECT
V.Caption as Interface,
'/Orion/images/StatusIcons/Small-' + V.StatusLED AS [_IconFor_Interface],
NULL as IP_Address,
NULL as Polling_Method,
NULL as Status,
V.NodeID,
3 AS Kind
FROM Orion.Volumes V
)
ORDER Y NodeID, Kind
Hi tdanner
Thanks for the pointers. This has pretty much achieved what I was looking for. Like you mentioned, it's a shame about the formatting.
I made a few changes - VolumeType Icon for volume status icon and corrected the typo on your line 31 (my line 38 below)
SELECT
n.caption as Node,
'/Orion/images/StatusIcons/Small-' + n.StatusLED AS [_IconFor_Node],
n.ipaddress as IP_Address,
n.ObjectSubType as Polling_Method,
n.StatusDescription as Status,
n.NodeID,
1 AS Kind
FROM orion.nodes n
UNION ALL (
SELECT
I.Caption as Interface,
'/Orion/images/StatusIcons/Small-' + i.StatusLED AS [_IconFor_Interface],
NULL as IP_Address,
NULL as Polling_Method,
NULL as Status,
I.NodeID,
2 AS Kind
FROM Orion.NPM.Interfaces I )
UNION ALL (
SELECT
V.Caption as Interface,
'/NetPerfMon/images/Volumes/' + V.VolumeTypeIcon AS [_IconFor_Interface],
NULL as IP_Address,
NULL as Polling_Method,
NULL as Status,
V.NodeID,
3 AS Kind
FROM Orion.Volumes V
)
ORDER bY NodeID, Kind
This will be perfect for now. Thanks again.
Stuart
tdanner or @stuartwhyte
what happened to my case, i just run a simple below query fro example in Custom table resource or custom query resource in Orion it throws me error as follows:
select Nodes.NodeiD as Name,Nodes.Status from orion.nodes Nodes where nodes.nodeid=550
union
(select Nodes.NodeiD as Name,Nodes.Status from orion.nodes Nodes where nodes.nodeid=163)
custom table
Custom Query
but in SWQL studio it works fine:
Where is the issue?
Another Question: Does html tag works in SWQL when run union/union all ?
/msarkar
There's a bug (internal number CORE-6278) in the report builder that causes it to mangle custom SWQL queries with UNION clauses. If the Custom Query Resource can meet your needs, you could use that instead. It does not suffer from this issue.
Custom Query Resource does not support embedding HTML markup in the query results.
I loved the trick you used here with the unions, but since I can never leave well enough alone I built it up some more. Pulled in the relationships between interfaces and their individual IP addresses when that info is available, tried to make it a bit easier to spot when we are looking at parent vs child objects, expanded on the status info, made your sorting mechanism invisible, and added a search (in case you guys haven't noticed this yet, EVERYTHING in Orion always needs to be searchable).
SELECT
n.caption as Node,
'/netperfmon/images/vendors/' + n.VendorIcon as [_IconFor_Node],
n.DetailsUrl as [_LinkFor_Node],
n.ipaddress as IP_Address,
n.ObjectSubType as Polling_Method,
n.StatusDescription as Status,
'/Orion/images/StatusIcons/Small-' + n.StatusLED AS [_IconFor_Status],
'' as [ ],
n.nodeid as [_linkfor_ ]
FROM orion.nodes n
--Where n.caption like '%${SEARCH_STRING}%'
UNION ALL (
SELECT
(' - '+ I.Caption) as Interface,
'/netperfmon/images/interfaces/' + i.InterfaceIcon AS [_IconFor_Interface],
i.DetailsUrl as [_LinkFor_Interface],
CASE WHEN i.IPAddress.IPAddress is not null then i.IPAddress.IPAddress
ELSE ip.IPAddress
END AS IP_Address,
NULL as Polling_Method,
concat(tostring(InPercentUtil),'% RX Utilization, ',tostring(OutPercentUtil),'% TX Utilization') as Status,
'/Orion/images/StatusIcons/Small-' + i.Statusicon AS [_IconFor_Status],
'' as [ ],
i.nodeid as [_linkfor_ ]
FROM Orion.NPM.Interfaces I
left join orion.NodeIPAddresses ip on ip.NodeID=i.NodeID and ip.IPAddressType='IPv4' and ip.interfaceindex is null
--Where i.node.caption like '%${SEARCH_STRING}%'
)
UNION ALL (
SELECT
(' - '+V.Caption) as Volume,
'/NetPerfMon/images/Volumes/' + V.VolumeTypeIcon AS [_IconFor_Volume],
v.DetailsUrl as [_LinkFor_Volume],
NULL as IP_Address,
NULL as Polling_Method,
concat(round(v.VolumePercentUsed,0),'% of ',round(v.volumespaceavailable/1073741824,0),' GB total') as Status,
'/Orion/images/StatusIcons/Small-' + v.StatusLED AS [_IconFor_Status],
'' as [ ],
v.nodeid as [_linkfor_ ]
FROM Orion.Volumes V
--Where v.node.caption like '%${SEARCH_STRING}%'
)
ORDER bY [_linkfor_ ], node desc
Loop1 Systems: SolarWinds Training and Professional Services
Not with SWQL, it only supports displaying plain text, icons, and making the text into a clickable link. If you were reallllllly dedicated you could create an icon of a bar chart for each percentage, so something like bar-1.gif through bar-100.gif and use them in the iconfor section but it feels overkill
SolarWinds solutions are rooted in our deep connection to our user base in the THWACK® online community. More than 195,000 members are here to solve problems, share technology and best practices, and directly contribute to our product development process.