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!
Amazing Eskor , but can we change User OU Name to User Security Group ?
Yes ,you can but i haven't tried it though. Try to create one and test it . I will probably check it as well.
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?