1 Reply Latest reply on Sep 3, 2015 1:55 PM by josh.haberman

    Group View member nodes list

    josh.haberman

      I'm trying to generate a list of all the nodes that are members of a top level parent group and I haven't had any success.

       

      Here is how my groups are structured:

       

      1. Main parent group for each business site.

      2. 2 child groups, one for LAN-based nodes and one for WAN-based nodes

       

      All the Nodes have a custom property for the "site code" of each business location applied to them.  This custom property matches the name of the parent group exactly.

       

      The group members Resource only shows the WAN and LAN child groups and does not show those group's nodes.

       

      Any ideas on how to implement this?

        • Re: Group View member nodes list
          josh.haberman

          OK, I figured it out.

           

          After never having touched SQL in my life, I Google taught myself it and did a lot of experimenting with queries in the Solarwinds "Database Manager" and this is what I came up with.

           

          A little background first.  We use Groups (aka Containers) in NPM for representing physical locations (I've dubbed "Site Groups") and for alerting.

          The alerting groups contain the nodes at these site in a WAN group and a LAN group which in turn are child groups of the Site Group.

          Manage Groups.png

           

          There is then a dependency in place with the WAN group being the parent of the LAN group to avoid multiple alerting for a WAN outage.

           

          The reason for the WAN group is the site's have redundant WAN connections so, this way a site only shows a warning state if one of the connections go down.

           

          Now, back to my issue.  The Site Group pages are the main hubs of information for our sites.  I wanted to have a table of all the devices at the site along with their status and other info but, since they're not members of the Site Group they wouldn't show up in the Group Members Resource.

           

          To get around this, I have a custom property for all the nodes called "Subnets".  These CPs are the identical to the names of the Site Groups for the sites so, they can associate the nodes with those sites.

           

          In order to create a device table on the Site Group page, I inserted the following SQL query into a Custom Query Resource:

           

          SELECT Caption AS Name, '/Orion/images/StatusIcons/Small-' + StatusLED AS [_IconFor_Name], IPAddress AS IP_Address, N.Description, C.Device_Type, StatusDescription AS Status, N.DetailsUrl AS [_LinkFor_Name]

          FROM Orion.Nodes N

          left JOIN Orion.NodesCustomProperties C

          ON N.NodeID=C.NodeID

          WHERE C.Subnets = '${Name}'

           

          The end result is this:

           

          SQL table.png

           

          I'd thought i'd share this newb query, in case it can help someone else.