SWQL to Identify Groups That Are Missing A Specific Dynamic Query

We have thousands of sites and thus thousands of groups. Group members are maintained using a dynamic query called "Local Nodes" which populates groups with devices that match specific naming criteria. I've recently discovered some engineers haven't been creating the "Local Nodes" dynamic query. I want to ascertain the scope of the problem but I really don't want to go through thousands of groups one by one.

I've had a look in SWQL but while I can find the groups thermselves in Orion.Containers haven't been able to find a way to check if the groups have the dynamic query "Local Nodes" present.

Find more posts tagged with

Dynamic Groups

containers

SWQL

dynamic query

Accepted answers

BaldFeegle

So I think I've worked out how to identify groups that don't contain a "Local Nodes" group, but I'm getting all of the sub-groups returned. How can I just return the top-level groups?

SELECT c.NAME FROM Orion.Container c
WHERE c.Name NOT IN
(
SELECT c.name
FROM Orion.ContainerMemberDefinition cm
JOIN Orion.Container c ON c.ContainerID = cm.ContainerID
WHERE cm.Name = 'Local Nodes'
order by c.Name
)

All comments

BaldFeegle

I should add that the "Local Nodes" query gets added just to the top level groups, so I also need a way to identify top level groups.

BaldFeegle

So I think I've worked out how to identify groups that don't contain a "Local Nodes" group, but I'm getting all of the sub-groups returned. How can I just return the top-level groups?

SELECT c.NAME FROM Orion.Container c
WHERE c.Name NOT IN
(
SELECT c.name
FROM Orion.ContainerMemberDefinition cm
JOIN Orion.Container c ON c.ContainerID = cm.ContainerID
WHERE cm.Name = 'Local Nodes'
order by c.Name
)

adam.beedell

A colleague of mine solved this problem but they've got home for the day. I took a swing through the "Ancestor.." property but might have got a bit misled

Are the site groups root level? I'm surprised they wouldnt be under a parent like "site groups", if so you could use the containermembership to resolve

BaldFeegle

Hmm. I wonder if there's a group where Containerid=1...

mesverrum

Unfortunately there is not a containerid 1 that hold the top level groups, they are just the ones that arent in any other container (or the container they were in gets deleted). In the past ive done it where i got my list of all the containers where their containerid doesn't show up in a subquery that grabs the object id of every orion.group object in the containermembers table. hopefully that makes sense

BaldFeegle

Turns out I had the answer yesterday, but I was looking at a filtered view of our groups without realising it. so the view didn't match up to the query results.