12 Replies Latest reply on Apr 14, 2015 9:39 AM by cgregors

    Custom  SWQL Query Questions

    jkirschn

      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

        • Re: Custom  SWQL Query Questions
          joiellis

          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.

          • Re: Custom  SWQL Query Questions
            tdanner

            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.

              • Re: Custom  SWQL Query Questions
                svindler

                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.

                  • Re: Custom  SWQL Query Questions
                    joiellis

                    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

                      • Re: Custom  SWQL Query Questions
                        tdanner

                        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.

                    • Re: Custom  SWQL Query Questions
                      erik.boyer@hcr-manorcare.com

                      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?

                        • Re: Custom  SWQL Query Questions
                          madhavan

                          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
                          
                            • Re: Custom  SWQL Query Questions
                              erik.boyer@hcr-manorcare.com

                              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!

                                • Re: Custom  SWQL Query Questions
                                  madhavan

                                  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'