How to make a WQL Query for devices and join AD user fields, attributes or info?

Occasional Contributor

I would like to create a query or queries in SCCM based on either the system resource or user resource grabbing a device with the attached user information such as department, title, manager other custom fields from AD and etc. how would I do this? 

I have already added the custom fields in discovery, so the data is there. 

I even have a device collection of systems I can run it against. I just can’t seem to join user resource and system resource together. 

I don’t understand why SCCM or Endpoint manager made this so difficult?

heres an example of one query below. How would I add the AD data?

 

should I use user name, user principal name, full user name? 

 
select SMS_R_System.Name, SMS_R_System.LastLogonUserName, SMS_R_System.ADSiteName, SMS_G_System_OPERATING_SYSTEM.Caption, SMS_G_System_OPERATING_SYSTEM.BuildNumber, SMS_R_System.DistinguishedName from  SMS_R_System inner join SMS_G_System_OPERATING_SYSTEM on SMS_G_System_OPERATING_SYSTEM.ResourceID = SMS_R_System.ResourceId

 

 

1 Reply

I am trying to figure this out as well. Anyone have any ideas?