Version 2

    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