I had a requirement to deploy patches (part of patch testing) to Department (group of user around 200+) who resides in one OU in Active Directory .The computers used by them are different due to their work nature and they keep moving with different computers (with different apps). I need to get list of computers used by these 200+ users from specific Organizational unit in Active Directory.
With CM12,feature called User Device Affinity --associating a user with one or more specified devices and it eliminates the need to know the names of a user’s devices .More info about UDA ,refer http://technet.microsoft.com/en-us/library/gg699365.aspx
How to do I use this feature (UDA) to get computers that are associated with users from specific OU ?
Long ago ,I posted Collection query to get list of computers with primary user (UDA) is NULL means http://eskonr.com/2014/03/sccm-configmgr-2012-collection-for-computers-with-primary-user-uda-is-null/
I use instance called SMS_UserMachineRelationship to get the User machine relationship.
Create a device collection ,limit to whatever collection you want and then add query rule ,paste the below query and click ok.
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.ResourceID=SMS_UserMachineRelationship.MachineResourceID JOIN SMS_R_User ON SMS_UserMachineRelationship.UniqueUserName = SMS_R_User.UniqueUserName
WHERE SMS_UserMachineRelationship.RelationActive=1 AND SMS_R_User.UserOUName = "ESKONR.COM/BLG/WF/GIM"
Change the bold letters to the required OU.
ESKONR.COM/BLG/WF/GIM—Contains list of users and we are retrieving the computers which are associated with UDA.
Hope it helps!
5 Comments
Amazing Eskor , but can we change User OU Name to User Security Group ?
Hi Amr,
Yes ,you can but i haven't tried it though. Try to create one and test it . I will probably check it as well.
Thanks,
Eswar
Have you created a query for specific ad group of users snd their respective devices in sccm ?
Hi,
as long as the AD groups are being discovered through the AD security group discovery, you should be able to query them using SQL.
Thanks,
Eswar
Sorry for this late comment, just found this query today.
How can I add a column that shows the Full Name of the user (User Resource.Full User Name)? The query shows the system names for the users in the specified OU, but now I can't match the users with the systems when looking at the query result?