SCCM Query to See Device Owner Using User Device Affinity (uda)
When I google online, lots of information there. but none worked :( here is the sccm query to see device owner by using User Device Affinity. it will list computer name, the device’s owner, user’s full name, email address, job title, manger name and description( these three needs to manually add to SCCM User Discovery first). Here is the SCCM Query:
select SMS_R_System.Name, SMS_R_User.UniqueUserName, SMS_R_User.FullUserName, SMS_R_User.Mail, SMS_R_User.title, SMS_R_User.manager, SMS_R_User.description from SMS_R_User JOIN SMS_UserMachineRelationship ON SMS_UserMachineRelationship.UniqueUserName=SMS_R_User.UniqueUserName JOIN SMS_R_System ON SMS_R_System.Name=SMS_UserMachineRelationship.ResourceName
Here is another version that works (Updated):
select distinct SMS_R_SYSTEM.Name,SMS_R_User.UniqueUserName,SMS_R_SYSTEM.Client from SMS_R_System JOIN SMS_UserMachineRelationship ON SMS_R_System.ResourceID=SMS_UserMachineRelationship.ResourceID JOIN SMS_R_User ON SMS_UserMachineRelationship.UniqueUserName=SMS_R_User.UniqueUserName WHERE SMS_UserMachineRelationship.Types=1 AND SMS_UserMachineRelationship.IsActive=1 AND SMS_R_User.UserOUName = X.LAN/USER HIERARCHY/TECHNOLOGY”
or SMS_R_User.UserGroupName=“X.LAN\InfoSec”