I see this request a lot where people want a list of all their nodes, plus all their related objects such as interfaces/volumes/app monitors. Using normal SQL/SWQL it is a mess because every time you join another table you end up with lots of duplicated data like this:
That one database server had 126 rows using normal joins between all the interfaces and volumes and app monitors.
A while back tdanner posted an example of a query that used unions to make a cleaner set. I saw how i could use that same method to build this SWQL report that I think does a much better job of presenting the relevant info about all the child objects being monitored on each node.
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_ ],
'0' as [_linkfor_order]
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],
i.IPAddress.IPAddress,
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_ ],
'1' as [_linkfor_order]
FROM Orion.NPM.Interfaces I
--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_ ],
'2' as [_linkfor_order]
FROM Orion.Volumes V
--Where v.node.caption like '%${SEARCH_STRING}%'
)
UNION ALL (
SELECT
(' - '+A.Name) as Application,
'/orion/apm/images/statusicons/Small-App-Blank.gif' AS [_IconFor_Application],
a.DetailsUrl as [_LinkFor_Volume],
NULL as IP_Address,
NULL as Polling_Method,
a.StatusDescription as Status,
'/Orion/images/StatusIcons/Small-' + a.StatusDescription + '.gif' AS [_IconFor_Status],
'' as [ ],
a.nodeid as [_linkfor_ ],
'3' as [_linkfor_order]
FROM Orion.APM.Application a
--Where A.node.caption like '%${SEARCH_STRING}%'
)
ORDER BY [_linkfor_ ], [_linkfor_order], node
-Marc Netterfield
Loop1 Systems: SolarWinds Training and Professional Services
