SCCM Report for User Device Affinity
Updated: Here is a better sccm query for creating a device collection based on primary users: https://adminsccm.com/2018/10/07/creating-device-collections-based-on-users-and-vice-versa/ 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, SMS_UserMachineRelationship.UniqueUserName 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_ABC00001 ON SMS_CM_RES_COLL_ABC00001.ResourceID = SMS_R_User.ResourceID) Since we are switching to target users instead of machines with SCCM, here is my SQL Query for finding devices with no primary user and devices with more than 1 primary user:
— Check for duplicates select MachineResourceName, count(*) from v_UserMachineRelationship group by MachineResourceName having count(*) > 1 order by count(*) — Detailed Data from Source select * from v_FullCollectionMembership_Valid where CollectionID = ‘NOTE1’ select * from v_UserMachineRelationship where RelationActive = 1 —Wrong SQL Query select fcm.Name, umr.UniqueUserName, umr.RelationActive from v_FullCollectionMembership_Valid fcm left join v_UserMachineRelationship umr on fcm.ResourceID = umr.MachineResourceID where CollectionID = ‘NOTE1’ AND ( RelationActive IS NULL OR RelationActive = 1 ) order by fcm.Name —Correct One ----Basic SQL Query select fcm.Name, umr.UniqueUserName from v_FullCollectionMembership_Valid fcm left join v_UserMachineRelationship umr on fcm.Name = umr.MachineResourceName AND umr.RelationActive = 1 where fcm.CollectionID = ‘NOTE1’ ----Check Machines without Primary user select fcm.Name, umr.UniqueUserName from v_FullCollectionMembership_Valid fcm left join v_UserMachineRelationship umr on fcm.Name = umr.MachineResourceName AND umr.RelationActive = 1 where fcm.CollectionID = ‘Note1’ AND RelationActive IS NULL ----Check Machines with more than 1 Primary user select MachineResourceName,UniqueUserName from v_UserMachineRelationship where MachineResourceName IN ( select fcm.Name from v_FullCollectionMembership_Valid fcm left join v_UserMachineRelationship umr on fcm.Name = umr.MachineResourceName AND umr.RelationActive = 1 where fcm.CollectionID = ‘Note1’ group by fcm.Name having count(*) > 1 ) order by MachineResourceName
‘Note1’ is the collection ID you want to query on. If you want to check all the SQL database name for SCCM, check this page https://docs.microsoft.com/en-us/previous-versions/system-center/system-center-2012-R2/dn581963(v=technet.10)