This discussion has been locked. The information referenced herein may be inaccurate due to age, software updates, or external references.
You can no longer post new replies to this discussion. If you have a similar question you can start a new discussion in this forum.

Custom SWQL Query Questions

Hi All 

Is there a custom sql script i can use to see all the nodes and the groups they are in and if they are in multiple groups.  Here's the" why"  we have a  US map  with store locations. These store location  points  are put on the map by  dragging the group bubble to the map. We have added a sub group and added the non essential nodes  to the sub group. This way if a  non essential node goes down the entire bubble does turn red. We place the  essential items  at the root of the bubble this will show the bubble red if a root item has changed its status.

Is this logic the best way to handle this situation?  I ask because the management of the "Groups"  for the  Bubble and sub-bubble MAP  items  is time consuming and I'm not sure its the best practice.

Any suggestions?

Thanks

Jeff Kirschner

  • We have an inhouse-developed browser-based map, and we didn't use NPM Groups/Containers, as we find that using Groups causes our large SolarWinds installation to become practically unusable.  We planted a few custom properties on all of our nodes, one that links nodes by site/location, and several that tag them with roles and outage severities, and our map code uses these to 'group' nodes by location and type inside the map.

  • To get a list of the groups and the nodes that are members of them, you can use a query like this:

    SELECT c.Name, c.Members.Name AS NodeName

    FROM Orion.Container c

    WHERE c.Members.MemberEntityType='Orion.Nodes'

    Note that this will only include the direct group memberships.

  • How would you build a query to list the nodes that don't have a group membership? I would like to have a weekly report on nodes, that haven't been assigned at least one group.

    Next step will be a sanity check of the group memberships and your query seems like a perfect match for that.

  • Here's an SWQL query I built before we abandoned using Groups.  As we don't have any groups defined at all now, I can't test this properly... This returns the same number of rows as nodes in our system, IE everything we monitor is not in a group, so that side seems to be okay.

    SELECT NodeID

    FROM Orion.Nodes

    LEFT OUTER JOIN Orion.ContainerMembers ON NodeID=MemberPrimaryID

    WHERE MemberPrimaryID IS NULL

  • Here's a minor enhancement to that query:

    SELECT Nodes.NodeID, Nodes.Caption

    FROM Orion.Nodes

    LEFT JOIN Orion.ContainerMembers ON ContainerMembers.MemberPrimaryID=Nodes.NodeID AND MemberEntityType='Orion.Nodes'

    WHERE ContainerMembers.ContainerID IS NULL

    You need to check MemberEntityType. If MemberEntityType is not Orion.Nodes (it might be Orion.Containers, Orion.NPM.Interfaces, or some other object type), then MemberPrimaryID is not a NodeID.

  • tdanner‌ is there a way to run this query and get nodes that in nested groups? IE:

    Parent Group

    - Child Group 1

    - - Node1

    - - Node2

    - Child Group 2

    - - Node1

    Etc?

  • Hi,

    Query provided below can be used to get parent, child group information along with node details. Try and let me know if this solves your need.

    SELECT Container.Name AS PARENTGROUPNAME, P.NAME AS CHILDGROUPNAME, P.NODEID, P.CAPTION

    FROM Orion.ContainerMembers

    LEFT JOIN Orion.Container on ContainerMembers.ContainerID = Container.ContainerID

    LEFT JOIN (SELECT Container.Name, Nodes.NodeID, Nodes.Caption, ContainerMembers.ContainerID

    FROM Orion.Nodes

    LEFT JOIN Orion.ContainerMembers ON ContainerMembers.MemberPrimaryID=Nodes.NodeID AND MemberEntityType='Orion.Nodes'

    LEFT JOIN Orion.Container ON ContainerMembers.ContainerID = Container.ContainerID

    WHERE ContainerMembers.ContainerID IS NOT NULL) P

        ON P.CONTAINERID = CONTAINERMEMBERS.MEMBERPRIMARYID

    WHERE MemberEntityType='Orion.Groups'

    ORDER BY PARENTGROUPNAME, CHILDGROUPNAME, NODEID

  • Hi madhavan,

    I ran this query but it times out (takes over two minutes) and won't let me add as a report for the same reason. I don't have permission to run a query directly against the DB as I'm not one of our SQL administrators (and getting one of them to run it would be a challenge / take a long time).

    Is there a faster query that won't timeout?

    Thanks!

  • Hi,

    Provided below the modified query. This query will support only 2 level of groups. (Nodes -> child Group -> Parent Group).

    If it is more than 2 groups (like Nodes -> child Group -> Parent 1 Group -> Parent 2 Group), it will not work and not possible to represent in a single swql query.

    This query would be much more faster. Try this and let me know.

    select parent_container_name, parent_containerid, child_container_name, child_container_id, c.entityid as nodeid, c.name as caption

    from  (

        select c.name as parent_container_name, containerid as parent_containerid, entityid as child_container_id, s.name as child_container_name

        from orion.containermembersnapshots s

        inner join orion.container c on c.containerid = s.containerid

        where s.entitytype = 'orion.groups'

          ) as p

    left join orion.containermembersnapshots c

        on p.child_container_id = c.containerid

    where c.entitytype = 'orion.nodes'

  • In your subselect

    select c.name as parent_container_name, containerid as parent_containerid, entityid as child_container_id, s.name as child_container_name

        from orion.containermembersnapshots s

        inner join orion.container c on c.containerid = s.containerid

        where s.entitytype = 'orion.groups'

    entityid isn't exposed in the API in V2 or V3