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

Michael Farber 1 Reputation point
2020-09-04T15:16:22.313+00:00

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.

Microsoft Configuration Manager

5 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Andreas Baumgarten 102.2K Reputation points MVP
    2020-09-04T19:59:19.533+00:00

    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

    0 comments
  2. Simon Ren-MSFT 31,756 Reputation points Microsoft Vendor
    2020-09-07T03:06:18.323+00:00

    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.

  3. Simon Ren-MSFT 31,756 Reputation points Microsoft Vendor
    2020-09-14T02:55:34.08+00:00

    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

    0 comments
  4. Michael Farber 1 Reputation point
    2020-09-15T16:30:42.233+00:00

    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

    0 comments
  5. Simon Ren-MSFT 31,756 Reputation points Microsoft Vendor
    2020-09-16T10:06:18.257+00:00

    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://video2.skills-academy.com/en-us/answers/products/mem

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

    25231-microsoftteams-image-6.png

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

    0 comments