SCCM Device Collections for Primary User Groups
Would you like an automated way to group computers by the role of their primary user? For example, do you want a collection that shows all the primary staff computers and another that shows all shared computers in your environment?
If you already have AD security groups for any group of users, you can quickly create a SCCM collection containing the primary computers belonging to those users. You can also create the inverse for any of these. Here is how the collection query language would look that shows the primary computers for the group DOMAIN\\GROUPNAME
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\\GROUPNAME')
To use this, just specify the group name on the very last line.
Let’s say you work in education and want a collection showing all staff computers, all student computers, and computers that are used by generic users/non staff or student users). You would create three collections. The first two would use the collection query language from above. You would set the SMS_R_User.SecurityGroupName value for a staff group in the first collection and a student group in the second collection. The third collection would include all computers and exclude these two other collections.
With those three collections, you could do a couple of extra things like:
- Export the collection members to AD security groups.
- Sort computers into sub-OUs automatically based on their primary user.
Because this data updates within SCCM automatically, you don’t have to worry about the administrative overhead of updating them. Hopefully, this type of hybrid collection will make your environment a bit easier to manage!