[Create 2 collections from AD group and return only the users/machines in both collections]

Not sure why anyone didn't acknowledge me?

Anyhow I worked it out without too many complications.

 

Create the following collections.

Collection A: Query membership based on AD group A
Collection B: Limited to members of Collection A and query membership based on AD group B.
Collection B would contain users who are members of both groups.

[Create 2 collections from AD group and return only the users/machines in both collections]

Any Ideas?

[Create 2 collections from AD group and return only the users/machines in both collections]

I have a requirement to create 2 collections from AD groups and return only the users/machines that are in both.

I have tried something like the following:-

SELECT 

...

WHERE ResourceID IN (SELECT ResourceID FROM SMS_R_User where SMS_R_User.SecurityGroupName = <GroupA>) 

AND ResourceID IN (SELECT ResourceID FROM SMS_R_User where SMS_R_User.SecurityGroupName = <GroupB>)

 

But if a user is in both groups the result is 0 not the expected result

 

e.g

Collection from ADGroup1 = 12345

Collection from ADGroup2 = 34567

Collection items in ADGroup1 and ADGroup2 = 345

[Create 2 collections from AD group and return only the users/machines in both collections]

I have a requirement to create 2 collections from AD groups and return only the users/machines that are in both.

I have tried something like the following:-

SELECT 

...

WHERE ResourceID IN (SELECT ResourceID FROM SMS_R_User where SMS_R_User.SecurityGroupName = <GroupA>) 

AND ResourceID IN (SELECT ResourceID FROM SMS_R_User where SMS_R_User.SecurityGroupName = <GroupB>)

 

But if a user is in both groups the result is 0 not the expected result

 

e.g

Collection from ADGroup1 = 12345

Collection from ADGroup2 = 34567

Collection items in ADGroup1 and ADGroup2 = 345