cancel
Showing results for 
Search instead for 
Did you mean: 
Create Post

Node Summary Report with Interfaces, Volumes, Applications

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:

pastedImage_0.png

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.

pastedImage_1.png

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

Comments

Hi Mark,

Is there a specific table I run this query from on the NPM database?  I am VERY VERY new to opening the Orion hood.  This report is gold.

Don't run it in the database, this is written to be used inside a resource in the web console called "Custom Query"

Hello

Thanks for this - This looks brilliant - I wanted to know if it can be modified to run on the latest version of NPM. Because its giving a few errors when I put it in the SQL\WSQL query window.

We've just upgraded to NPM 12.4

Should need any changes to run in 12.4, I have it running on my lab right now.

It is written to be used inside the Custom Query resource though, not as a Custom Table

Once again, another terrific resource for the community to use.

Thank you for sharing, mesverrum​!

-Will

mesverrum​ thankyou for your query it is a massive help. Any chance someone would be kind enough to point out where I change this query to only display nodes with a specific custom peoperty (e.g. System). Baiscally adding a filter so we can target certain areas of the business?

Anyway to update this to the latest release?  When I try to run a custom query using this it just reports back "There was an error processing the request.".

Version history
Revision #:
1 of 1
Last update:
‎05-29-2018 07:27 PM
Updated by: