Create a Device Collection based on a User Collection (Primary Computer)

Credit going to Steven Hosking

There's probably a slightly easier way to work this, but hey if it works... ¯\_(ツ)_/¯

Case scenario:

• I have a user security group in AD (for example, SG_User). 
• I want to create a device collection, using SG_Userto find the primary device. 

 

Solution:

1. Create a user collection, referencing SG_User. In this example, I called this user collection CM_User. Replace 'domain\\SG_User' with your own domain name and security group.
   

   	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.UserGroupName = "<domain>\\SG_User"

   view raw gistfile1.txt hosted with ❤ by GitHub
   
   
2. Create a device collection, referencing user collection CM_User's ID. Replace 'P0100024' with your own ID.
   

   	select sms_r_system.name
   	from SMS_R_System
   	where SMS_R_System.resourceID in
   	(select SMS_UserMachineRelationship.resourceID
   	from SMS_CM_RES_COLL_P0100024
   	join SMS_UserMachineRelationship on SMS_UserMachineRelationship.UniqueUserName = SMS_CM_RES_COLL_P0100024.SMSID)

   view raw gistfile1.txt hosted with ❤ by GitHub
   
   

How do you find the ID? 

In your User Collections, right-click on header row and click Collection ID.

 

And that's it.