SQWL: list nodes not in NCM and member of group

I'm trying to create a report of all Orion node that are NOT in NCM and not a member of a particular group.  I have the first part tackled, but struggling to figure out how to exclude node that belong to that group.

What I have working so far:

SELECT Caption
FROM Orion.Nodes orionnode
LEFT JOIN NCM.Nodes ncmnode ON orionnode.NodeID=ncmnode.CoreNodeID
WHERE orionnode.Vendor='Cisco' AND ncmnode.NodeID IS NULL

I'm pretty new to writing my own SWQL code, and can often muddle through modifying code I've found, but I'm not getting anywhere with this one yet.  We have a group of discommisioned hardware that is kept in the system for record retention for some time before being deleted, and I want to exclude them from this list.

Parents
  • I don't have NCM installed right now, so I can't verify this directly, but try this:

    SELECT Caption
    FROM Orion.Nodes orionnode
    LEFT JOIN NCM.Nodes ncmnode ON orionnode.NodeID=ncmnode.CoreNodeID
    LEFT JOIN Orion.ContainerMemberSnapshots AS cms ON orionnode.NodeID=cms.EntityID
    LEFT JOIN Orion.Container AS c ON c.ContainerID=cms.ContainerID AND c.Name = 'MyGroup'
    WHERE orionnode.Vendor='Cisco'
       AND ncmnode.NodeID IS NULL
       AND c.ContainerID IS NULL

Reply
  • I don't have NCM installed right now, so I can't verify this directly, but try this:

    SELECT Caption
    FROM Orion.Nodes orionnode
    LEFT JOIN NCM.Nodes ncmnode ON orionnode.NodeID=ncmnode.CoreNodeID
    LEFT JOIN Orion.ContainerMemberSnapshots AS cms ON orionnode.NodeID=cms.EntityID
    LEFT JOIN Orion.Container AS c ON c.ContainerID=cms.ContainerID AND c.Name = 'MyGroup'
    WHERE orionnode.Vendor='Cisco'
       AND ncmnode.NodeID IS NULL
       AND c.ContainerID IS NULL

Children