SCCM Collection WQL Query – Include Device’s Primary User Full Name

Logbook of the Captain – sidereal time: 2017.07.05

Sometimes you’ll asking yourself about things you are thinking you are not able to do, but after some hours you’ll reach the destination and everything works like ordered by customers.

One of the customers I’ve worked for trying to sort computer objects in “ConfigMgr” to device collections by using department name. But they do not use “Active Directory” attributes or something else to gather the data for department ID’s. They are using the “Display Name” in “Active Directory” and append the department in brackets like:

lastname, forename (department)

But what is the challenge at sorting the clients by properties like display name? The display name of an user in “Active Directory” will be imported into “ConfigMgr” as an user object, but you want to return a value containing an client object. Nevertheless there is another challenge: Try to get the primary user of some devices is no problem while enabling the “User Device Affinity” function from “ConfigMgr” via “Client Settings”. The WMI class we want to use only returns the Pre Win 2000 name like “domain\username”. You have to translate the return value into the display name or also called user full name in “ConfigMgr WMI Library”. So what to do?

Create a “Client Settings Set” in “ConfigMgr” with “User and Device Affinity” functions enabled and customize the Settings like you need:

05-07-2017 14-18-41

(I choose 60 minutes for usage in a period of one day, caused on the situation we have to identify users on streamed machines in a short period of time)

Now enable the user discovery to use the affinity functions :

05-07-2017 14-21-48

From now the affinity in “ConfigMgr” is enabled and ready to use. Create now the collection with the following query:

select SMS_R_SYSTEM.ResourceID,SMS_R_SYSTEM.ResourceType, SMS_R_SYSTEM.Name,SMS_R_SYSTEM.SMSUniqueIdentifier, SMS_R_SYSTEM.ResourceDomainORWorkgroup,SMS_R_SYSTEM.Client from SMS_R_System JOIN SMS_UserMachineRelationship ON SMS_R_System.Name=SMS_UserMachineRelationship.MachineResourceName  JOIN SMS_R_User ON SMS_UserMachineRelationship.UniqueUserName=SMS_R_User.UniqueUserName  Where SMS_R_User.UniqueUserName in (select UniqueUserName from SMS_R_User where SMS_R_User.FullUserName is like “%Department%”)

Please replace the bold word with your parameter.

Captain over and out…

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s