Getting an SCCM Collection of Department Computers

 

These are directions on how to get a collection of computers used by a department, based off of the computer's primary users.

• Example: Get all computers where a "Technology" department member is a primary user. 
• This user has Technology listed as their department in Active Directory. 

First, create a user collection for the department, changing the department name in the query below:

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 = "Technology"

Once you create the user collection, note the Collection ID. In this example, I will say its COL00023.

Then, create a device collection using the query below, replacing the collection ID with yours:

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 INNER JOIN SMS_Cm_Res_Coll_COL00023 ON SMS_CM_Res_Coll_COL00023.ResourceID=SMS_R_User.ResourceID) AND SMS_UserMachineRelationship.Types[0]=1

References:

• https://sw-euc.com/2020/09/09/primary-device-collection-based-on-user-collection-with-sms_usermachinerelationship-types01/

Share on Google Plus

About Tom DeMeulenaere

Highly accomplished information technology professional with extensive knowledge in System Center Configuration Manager, Windows Server, SharePoint, and Office 365.