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”

4 Replies to “SCCM Query to See Device Owner Using User Device Affinity (uda)”

  1. Hey All, I need some help. I am try to run a report for our upcoming Windows 10 upgrade. I am using the SCCM Built-In Report “Operating System requirement status for computers in a specific collection” under the Upgrade Assesment folder. This will show me what computers are eligable to upgrade to Windows 10.

    They have asked me to add 2 columns to the report that will show the User Name of the person using the computer and what department they fall under. How can I add this to the report results? I am not that good at SQL. Thank you

    1. HI Danny, I am afraid you have to build a custom report for that additional username and the department he/she is in. you can refer to some basic SQL statement from here (it’s not that hard :)) :https://rui-qiu.com/sccm/create-sccm-application-catalog/

  2. hi,

    may i know where to define sccm query in sccm console, thank you

    1. On the left side, “Monitoring”- “Queries”

Leave a Reply