Recently we needed a collection of the primary devices of our early adopter users a.k.a. pilot users, because, you know, the output of all the helpers like Desktop Analytics or M365 Apps Readiness in #MEMCM is a collection of machines. But being an Early Adopter is a user role.

To create a device collection for primary devices of a collection of users, you need to access the user collection by the collection ID (e.g. SBX00963).

If you don’t specify SMS_UserMachineRelationship.Types the collection will also contain the devices of ‘Users of this device in the last 90 days’. ‘SMS_UserMachineRelationship.Types[0]=1’ filters only the assigned primary users.

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_SBX00963 ON SMS_CM_Res_Coll_SBX00963.ResourceID=SMS_R_User.ResourceID) AND SMS_UserMachineRelationship.Types[0]=1