Creating a User Collection based on AD User Group

ConfigMgr, SCCM 2012 12 comments

If you want to deploy software to a particular AD user group then create a User Collection and use the following Query Statement:


select

SMS_R_USER.ResourceID,SMS_R_USER.ResourceType,SMS_R_USER.Name,SMS_R_USER.UniqueUserName,SMS_R_USER.WindowsNTDomain

from SMS_R_User

where SMS_R_User.UserGroupName = "<Domain>\\<AD Group>"

Remember to make sure you have Discovery set up on your AD or specific OU containing groups. To do this click Administration>Discovery Methods>Active Directory Group Discovery. Right click and choose Properties.

Click Add and configure accordingly.

12 comments

  1. Pingback: Creating a User Collection based on users not in a AD User Group | SCCM Tips and Fixes

  3. Thanks for the query, but when I am trying to create user collection based on AD administrators groups it does not seems to work. I get error “this query has syntax error. are you sure you want to save it?”

    Reply

    1. Vickky,

      It maybe that the ” ” are not being translated properly from your copy paste as I have no included them in a code box on the site. Try replacing them after you paste with a Shift+2 from your keyboard. Let me know. If it fails I’ll take a look later.

      Reply

  6. Please help me how to query machines that have no record in Active Directory/not in AD anymore.. We want to have a collection with computers that still in SCCM but does not exists in AD anymore. Thank you.

    Reply

    1. Try this 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 where SMS_R_System.Name in (select Name from SMS_R_System where ((DATEDIFF(day, SMS_R_SYSTEM.AgentTime, getdate()) >=45) and AgentName = “SMS_AD_SYSTEM_DISCOVERY_AGENT”)) and SMS_R_System.Name in (select Name from SMS_R_System where ((DATEDIFF(day, SMS_R_SYSTEM.AgentTime, getdate()) >=45) and AgentName = “Heartbeat Discovery”)). https://www.reddit.com/r/SCCM/comments/1d5bbu/old_machines_showing_in_sccm_not_in_ad/

      Reply

  7. How can i get users that match 2 different groups. i tried like this.
    SELECT * FROM SMS_R_Users WHERE (SMS_R_User.UserGroupName =”domain\group0″ AND SMS_R_User.UserGroupName =”domain\group1″)

    i get 0 results 😦

    Reply

    1. Stefan, the following query should get you up and running.
      select SMS_R_USER.ResourceID,SMS_R_USER.ResourceType,SMS_R_USER.Name,SMS_R_USER.UniqueUserName,SMS_R_USER.WindowsNTDomain from SMS_R_User WHERE ResourceID IN (SELECT ResourceID FROM SMS_R_User where SMS_R_User.SecurityGroupName =”domain\\group1″) AND ResourceID IN (SELECT ResourceID FROM SMS_R_User where SMS_R_User.SecurityGroupName = “domain\\group2”)

      Reply
  8. Pingback: Creating a User Collection based on Mutiple AD User Groups – SCCMentor – Paul Winstanley

Leave a Reply