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)

Leave a Comment