SCCM User Collections: Filtering by AD Department Attribute

Building Configuration Manager user collections for every department in a large organisation is a fairly simple task, but a time-consuming one to do manually, one collection at a time. Timmy Andersson at CTGlobal wrote a PowerShell script that automates exactly this: it reads the department attribute from Active Directory user objects and creates a matching ConfigMgr user collection for each distinct department found, complete with the appropriate query membership rule.

The General Approach

The script works by querying AD for the distinct set of values in the department attribute across your user objects, then for each one it creates a corresponding ConfigMgr user collection with a WQL query rule filtering SMS_R_User.Department to that value — effectively automating what would otherwise be dozens of near-identical manual collection creations. Because this creates collections in bulk against your live ConfigMgr environment, review the full script from CTGlobal’s original write-up before running it, and test in a lab or pilot OU first — particularly checking how it handles blank department attributes and any department names containing characters that need escaping in a WQL query.

The Native ConfigMgr Cmdlet Alternative (Updated for 2026)

If you don’t need the AD-driven auto-discovery part of Timmy’s script and just want the collection-creation step, Configuration Manager’s own PowerShell module now covers this natively with two documented cmdlets: New-CMUserCollection to create the collection, and Add-CMUserCollectionQueryMembershipRule to attach the WQL query rule to it. For example:

foreach ($dept in $departments) {
    New-CMUserCollection -Name "Users - $dept" -LimitingCollectionName "All Users"
    Add-CMUserCollectionQueryMembershipRule -CollectionName "Users - $dept" `
        -RuleName $dept `
        -QueryExpression "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 = '$dept'"
}

New-CMUserCollection requires a collection -Name and a -LimitingCollectionName (or the ID/pipeline equivalents), plus optional scheduling and comment parameters. Add-CMUserCollectionQueryMembershipRule requires the target collection, a -RuleName, and the -QueryExpression WQL string. This still leaves the “get the distinct list of departments from AD” step to you (a simple Get-ADUser -Filter * -Properties department | Select-Object -ExpandProperty department -Unique covers it), but it means the actual collection creation runs entirely on Microsoft’s own supported cmdlets rather than raw WQL assembled by a third-party script.

Resources

• Timmy Andersson
• Timmy Andersson’s PowerShell collections guide
• Browse our Windows Admin Toolkit picks on Amazon