SCCM Collection Queries - Active Directory Group (AP)

Posted on 2011-10-14
Last Modified: 2012-05-12

We have SCCM 2007 up and running in our environment.

Does anyone know a query so that we can create a Collection for users? More importantly, a Collection of users that aren't in a certain group/groups?

Question by:tlcsupport
    LVL 8

    Expert Comment


    first you need to enable the correct discovery method.  active directory group discovery and system group discovery.

    LVL 31

    Expert Comment

    The correct Discovery method to enable is Active Directory User Discovery
    LVL 1

    Author Comment

    Yeah I've got the discovery working.

    Its just the collections that I'm having problems with.

    I need to create a collection to find users that aren't in AD Group A, or AD Group B.
    LVL 31

    Accepted Solution

    Here we go. All Users which aren't in GroupA or GroupB
    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.ResourceID NOT IN 
    								(SELECT ResourceID 
    								FROM SMS_R_User 
    								WHERE SMS_R_User.UserGroupName = 'Domain\\GroupA' OR SMS_R_User.UserGroupName = 'Domain\\GroupB')

    Open in new window


    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Don't lose your head updating email signatures!

    Do your end users still have the wrong email signature? Do email signature updates bore you or fill you with a sense of dread? You can make this a whole lot easier on yourself by trusting an Exclaimer email signature management solution. Over 50 million users should you!

    When you hear the word proxy, you may become apprehensive. This article will help you to understand Proxy and when it is useful. Let's talk Proxy for SQL Server. (Not in terms of Internet access.) Typically, you'll run into this type of problem w…
    Redirected folders in a windows domain can be quite useful for a number of reasons, one of them being that with redirected application data, you can give users more seamless experience when logging into different workstations.  For example, if a use…
    This tutorial will walk an individual through the steps necessary to enable the VMware\Hyper-V licensed feature of Backup Exec 2012. In addition, how to add a VMware server and configure a backup job. The first step is to acquire the necessary licen…
    This tutorial will walk an individual through the process of upgrading their existing Backup Exec 2012 to 2014. Either install the CD\DVD into the drive and let it auto-start, or browse to the drive and double-click the Browser file: Select the ap…

    737 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    18 Experts available now in Live!

    Get 1:1 Help Now