SCCM Report for User Device Affinity

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)

Leave a Reply