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

%3CLINGO-SUB%20id%3D%22lingo-sub-2026455%22%20slang%3D%22en-US%22%3EHow%20to%20make%20a%20WQL%20Query%20for%20devices%20and%20join%20AD%20user%20fields%2C%20attributes%20or%20info%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2026455%22%20slang%3D%22en-US%22%3E%3CP%3EI%20would%20like%20to%20create%20a%20query%20or%20queries%20in%20SCCM%20based%20on%20either%20the%20system%20resource%20or%20user%20resource%20grabbing%20a%20device%20with%20the%20attached%20user%20information%20such%20as%20department%2C%20title%2C%20manager%20other%20custom%20fields%20from%20AD%20and%20etc.%20how%20would%20I%20do%20this%3F%26nbsp%3B%3CBR%20%2F%3E%3CBR%20%2F%3EI%20have%20already%20added%20the%20custom%20fields%20in%20discovery%2C%20so%20the%20data%20is%20there.%26nbsp%3B%3CBR%20%2F%3E%3CBR%20%2F%3E%3C%2FP%3E%3CP%3EI%20even%20have%20a%20device%20collection%20of%20systems%20I%20can%20run%20it%20against.%20I%20just%20can%E2%80%99t%20seem%20to%20join%20user%20resource%20and%20system%20resource%20together.%26nbsp%3B%3CBR%20%2F%3E%3CBR%20%2F%3E%3C%2FP%3E%3CP%3EI%20don%E2%80%99t%20understand%20why%20SCCM%20or%20Endpoint%20manager%20made%20this%20so%20difficult%3F%3CBR%20%2F%3E%3CBR%20%2F%3E%3C%2FP%3E%3CP%3Eheres%20an%20example%20of%20one%20query%20below.%20How%20would%20I%20add%20the%20AD%20data%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Eshould%20I%20use%20user%20name%2C%20user%20principal%20name%2C%20full%20user%20name%3F%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3CBR%20%2F%3E%3CSPAN%3Eselect%20SMS_R_System.Name%2C%20SMS_R_System.LastLogonUserName%2C%20SMS_R_System.ADSiteName%2C%20SMS_G_System_OPERATING_SYSTEM.Caption%2C%20SMS_G_System_OPERATING_SYSTEM.BuildNumber%2C%20SMS_R_System.DistinguishedName%20from%26nbsp%3B%20SMS_R_System%20inner%20join%20SMS_G_System_OPERATING_SYSTEM%20on%20SMS_G_System_OPERATING_SYSTEM.ResourceID%20%3D%20SMS_R_System.ResourceId%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2026455%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3ECM%202012%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3Eendpoint%20manager%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EGeneral%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EQuery%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3Esccm%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EWQL%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E
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

 

 

0 Replies