SCCM – Join SMS_R_System and SMS_R_User

In SCCM, this is how you join the System and User tables.

Screenshot

Code

SELECT * FROM  SMS_R_System INNER JOIN SMS_R_User ON SMS_R_User.Username = SMS_R_System.LastLogonUserName

 

Notes

Add SMS_G_System_COMPUTER_SYSTEM

SELECT DISTINCT SMS_G_System_COMPUTER_SYSTEM.Model, SMS_R_System.Name,SMS_R_User.displayName, SMS_R_User.UserName, SMS_R_User.Mail, SMS_R_User.employeeID FROM  SMS_R_System inner join SMS_R_User ON SMS_R_User.Username= SMS_R_System.LastLogonUserName INNER JOIN SMS_G_System_COMPUTER_SYSTEM ON SMS_G_System_COMPUTER_SYSTEM.ResourceID = SMS_R_System.ResourceId where SMS_R_System.LastLogonUserName = SMS_R_System.LastLogonUserName

 

Run Query in SQL

SELECT
v_R_System.Name0, v_R_System.Operating_System_Name_and0, v_R_User.User_Name0,v_R_User.displayName0,v_R_User.Mail0, v_GS_COMPUTER_SYSTEM.Model0, v_R_User.employeeID0
FROM
v_R_System
INNER JOIN
v_R_User ON v_R_User.User_Name0 = v_R_System.User_Name0
INNER JOIN
v_GS_COMPUTER_SYSTEM ON v_GS_COMPUTER_SYSTEM.ResourceID = v_R_System.ResourceId

 

Or this to include nulls

SELECT
v_R_System.Name0, v_R_System.Operating_System_Name_and0, v_R_User.User_Name0,v_R_User.displayName0,v_R_User.Mail0, v_GS_COMPUTER_SYSTEM.Model0, v_R_User.employeeID0
FROM
v_R_System
LEFT JOIN
v_R_User ON v_R_User.User_Name0 = v_R_System.User_Name0
INNER JOIN
v_GS_COMPUTER_SYSTEM ON v_GS_COMPUTER_SYSTEM.ResourceID = v_R_System.ResourceId