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”