Creating Computer based collection based on a primary user in AD Group

Question

I am trying to create a system collection based on a primary user in AD Group.
I have tried the following but getting an error on all of the WQL:
select SMS_R_SYSTEM.ResourceID,SMS_R_SYSTEM.ResourceType,SMS_R_SYSTEM.Name,SMS_R_SYSTEM.SMSUniqueIdentifier,SMS_R_SYSTEM.ResourceDomainORWorkgroup,SMS_R_SYSTEM.Client from SMS_R_System left join SMS_UserMachineRelationship ON SMS_UserMachineRelationship.ResourceID = SMS_R_System.ResourceId WHERE SMS_UserMachineRelationship.UniqueUserName IN (SELECT SMS_R_User.UniqueUserName FROM SMS_R_User WHERE SMS_R_User.SecurityGroupName = 'Domain\Group')

Select SMS_R_SYSTEM.ResourceID,SMS_R_SYSTEM.ResourceType,SMS_R_SYSTEM.Name,SMS_R_SYSTEM.SMSUniqueIdentifier,SMS_R_SYSTEM.ResourceDomainORWorkgroup,SMS_R_SYSTEM.Client, SMS_R_User.UniqueUserName
FROM SMS_R_System JOIN SMS_UserMachineRelationship ON SMS_R_System.Name=SMS_UserMachineRelationship.MachineResourceName JOIN SMS_R_User ON SMS_UserMachineRelationship.UniqueUserName=SMS_R_User.UniqueUserName
Where SMS_R_User.UniqueUserName in (select UniqueUserName from SMS_R_User where UserGroupName = "Domain\Group")

Select SMS_R_SYSTEM.ResourceID,SMS_R_SYSTEM.ResourceType,SMS_R_SYSTEM.Name,SMS_R_SYSTEM.SMSUniqueIdentifier,SMS_R_SYSTEM.ResourceDomainORWorkgroup,SMS_R_SYSTEM.Client FROM SMS_R_System JOIN SMS_UserMachineRelationship ON SMS_R_System.Name=SMS_UserMachineRelationship.ResourceName JOIN SMS_R_User ON SMS_UserMachineRelationship.UniqueUserName=SMS_R_User.UniqueUserName WHERE SMS_UserMachineRelationship.Types=1 AND SMS_R_User.UserGroupName="Domain\Group"

select SMS_R_SYSTEM.ResourceID,SMS_R_SYSTEM.ResourceType,SMS_R_SYSTEM.Name,SMS_R_SYSTEM.SMSUniqueIdentifier,SMS_R_SYSTEM.ResourceDomainORWorkgroup,SMS_R_SYSTEM.Client from SMS_R_System LEFT JOIN SMS_UserMachineRelationship ON SMS_UserMachineRelationship.ResourceID = SMS_R_System.ResourceId WHERE SMS_UserMachineRelationship.UniqueUserName IN (SELECT SMS_R_User.UniqueUserName FROM SMS_R_User WHERE SMS_R_User.SecurityGroupName = 'Domain\Group')

Can you guys please help me understand the process and what do i have wrong in the WQL?
Thank you.

Answer 1

This question is related to ConfigManager. Please use the tag "me-cm-general" instead of "msc-orchestrator-deployment". This will increase the chance to get a fast and good answer for your question.

Kind regards
Andreas Baumgarten

Answer 2

Hi,

Thank you for coming Microsoft MECM Q&A forum.

Please change "Domain\Group" to 'Domain\Group' and try the following (customize the command accordingly):

SELECT
SMS_R_SYSTEM.ResourceID,
SMS_R_SYSTEM.ResourceType,
SMS_R_SYSTEM.Name,
SMS_R_SYSTEM.SMSUniqueIdentifier,
SMS_R_SYSTEM.ResourceDomainORWorkgroup,
SMS_R_SYSTEM.Client,
SMS_UserMachineRelationship.UniqueUserName

FROM SMS_R_System
LEFT JOIN SMS_UserMachineRelationship ON
SMS_UserMachineRelationship.ResourceID = SMS_R_System.ResourceId

WHERE SMS_UserMachineRelationship.UniqueUserName IN
(SELECT SMS_R_User.UniqueUserName
FROM SMS_R_User
WHERE SMS_R_User.SecurityGroupName = 'Contoso\TestUsers')

For more detailed information, please refer to:
Creating Device Collections Based on Primary Users (and vice versa)
SCCM 2012 buid computer collection based on user group membership / primary user

Note: One is non-Microsoft link, just for your reference. Thanks for your time.

Best regards,
Simon
If the response is helpful, please click "Accept Answer" and upvote it.

Answer 3

Hi,

Just checking in to see if there is any update. We haven't heard from you for a few days and would like to know the current status of the problem. Is the problem solved? Do you need any further assistance? Look forward to hearing from you.

Thanks for your time.

Best regards,
Simon

Answer 4

Thanks for the help.

Still getting an syntax error when trying to save it.
with 'Contoso\TestUsers' or 'Contoso\TestUsers'

I was not able to figure out how to use tags "me-cm-general", how can i see available tags?

Thank you

Answer 5

Hi,

Thanks for your reply.

Have you tried the queries in the link?
https://adminsccm.com/2018/10/07/creating-device-collections-based-on-users-and-vice-versa/

We can see all Configuration Manager related tags in the Microsoft Endpoint Manager on Q&A:
https://learn.microsoft.com/en-us/answers/products/mem

If someone has needed permissions, there is a editable option to manipulate the tags as shown below:

Best regards,
Simon
If the response is helpful, please click "Accept Answer" and upvote it.