Create User collections based on AD department attribute with Powershell

If you are an organization who uses the Department attribute in Active directory and want to target users withing those departments for different deployments but you have a lot of departments and you don’t know where to start, well then this post might be useful for you.

The script in this post retrieves all the departments that gets collected by the Users AD attribute by ConfigMgr (Not turned on by default needs to get added. See guide below) and from those departments it creates a user collection with a query that populates the collection with all users who are part of that specific department.

Below you will the script a step by step guide on how to do this, so lets get started.

<#   
    .NOTES
    ===========================================================================
     Created on:    12/12/2017 
     Created by:    Timmy Andersson
     Twitter:       @TimmyITdotcom
     Blog:          blog.ctglobalservices.com/author/tan/
                    www.timmyit.com
    ===========================================================================
    .DESCRIPTION
    Gets department attribute from user and creates user collections based on that adds all the memebers of that department to the user collection. 
    This script is ment to run on the Primary Site. 
#>
 

$SiteCodeObjs = Get-WmiObject -Namespace "root\SMS" -Class SMS_ProviderLocation -ComputerName $env:COMPUTERNAME -ErrorAction Stop

    foreach ($SiteCodeObj in $SiteCodeObjs)
        {
            if ($SiteCodeObj.ProviderForLocalSite -eq $true)
                {
                    $SiteCode = $SiteCodeObj.SiteCode
                }
        

$SitePath = $SiteCode + ":"
Import-module ($Env:SMS_ADMIN_UI_PATH.Substring(0, $Env:SMS_ADMIN_UI_PATH.Length - 5) + '\ConfigurationManager.psd1')

}


$LimitingCollections = "All Users"
$Users = Get-WmiObject -Namespace "root\SMS\Site_$($Sitecode)" -Class SMS_R_User -ComputerName $env:COMPUTERNAME -ErrorAction Stop
$Groups = $Users.department | Sort-Object | Get-Unique 
Set-location $SitePath
$Sched = New-CMSchedule -DayOfWeek Sunday

foreach ($Group in $Groups)
    {
      if (Get-CMUserCollection -name $Group)

      {
        
      }

        Else

      {
        
            $Query = "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.department = '$($Group)'"
            New-CMUserCollection -Name $Group -LimitingCollectionName $LimitingCollections -RefreshSchedule $Sched
            Sleep 1
            Add-CMDeviceCollectionQueryMembershipRule -CollectionName $Group -QueryExpression $Query -RuleName $Group


      }
              
          

    }

Guide

First of all we need to gather the department data from each user in Active directory.

Go to Administraton -> Hierchy Configuration -> Discovery Methods And right-click on Active Directory User Discovery

Go the pane “Active Directory Attributes” and from there you need to find “Department” in the left side and add it to the right column. When that’s done you

need to initiate a full scan by right-clicking on Active Directory User Discovery and choose “Run full discovery now”

In active directory the attribute looks like this and this is the information we want to gather.

Before we run the script there’s no user collection except for the default ones,

We run the script locally on the Primary Site server

And ones the script finishes (If you have a lot of departments it could take some time to process, approx 1-2 seconds per department)

And we know have User collections based on departments and its users as members of the group.

That’s all for me and if there’s any questions just post them below.

You can also find me over at www.timmyit.com and don’t forget to follow me on twitter https://twitter.com/TimmyITdotcom

Until next time, Cheers !

//Timmy