Very few information on sccm query to join both user resources and system resources database together. However my manger wants to see the device and is owner’s job title, manger, etc. so I have this sccm script to show all the information needed:
it will list the AD site, computer name, last logon user, user’s full name, email address, job title, department, user’s description, lost logon time stamp, machine SN, machine asset tag ID, operation system name and version, IP address and Mac address. Plus it limited to machine type Lenovo T450s. you can change the filter according to your own needs.
select SMS_R_System.ADSiteName, SMS_R_System.Name, SMS_R_System.LastLogonUserName, SMS_R_User.UniqueUserName, SMS_R_User.FullUserName, SMS_R_User.Mail, SMS_R_User.title, SMS_R_User.manager, SMS_R_User.description, SMS_R_System.LastLogonTimestamp, SMS_G_System_PC_BIOS.SerialNumber, SMS_G_System_SYSTEM_ENCLOSURE.SMBIOSAssetTag, SMS_R_System.OperatingSystemNameandVersion, SMS_R_System.IPAddresses, SMS_R_System.MACAddresses from SMS_R_System inner join SMS_R_User on SMS_R_System.LastLogonUserName=SMS_R_User.UserName inner join SMS_G_System_COMPUTER_SYSTEM_PRODUCT on SMS_G_System_COMPUTER_SYSTEM_PRODUCT.ResourceID = SMS_R_System.ResourceId inner join SMS_G_System_PC_BIOS on SMS_G_System_PC_BIOS.ResourceId = SMS_R_System.ResourceId inner join SMS_G_System_SYSTEM_ENCLOSURE on SMS_G_System_SYSTEM_ENCLOSURE.ResourceId = SMS_R_System.ResourceId where SMS_G_System_COMPUTER_SYSTEM_PRODUCT.Version like “%T450S%”