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