Monday, August 29, 2016

WQL Query to Find Duplicate Users in ConfigMgr

Recently it was brought to my attention that a user had multiple user objects in Configmgr. After investigating I found we had 20,685 duplicate users objects! These were caused by a problem with our old identity management system. We had some instances in the past that we jokingly called the "mass firings" where some employee's accounts were affected which caused them to be removed from Active Directory among our other systems. While trying to repair the damage, they were then recreated with different SID's, causing ConfigMgr to discover and import them again. This problem is long behind us (I hope) as we now utilize a different identity management system.

Investigating the various Site Maintenance options in ConfigMgr, I discovered none that would remove duplicate or obsolete user accounts, they only worked on device accounts. Now having duplicate user accounts was not causing any hardship that I could see. The only thing we used user accounts for at this time is to advertise software to users in our Application Catalog, and to find the primary user of a device. So I imagine there is potental for trouble where a user would not get software delivered to them because an obsolete user object was added to the collection instead of their current object.


Not finding any Site Maintenance options, I came up with the following wql query to find duplicate users and created a collection:


select R.ResourceID,R.ResourceType,R.Name,R.UniqueUserName,R.WindowsNTDomain from SMS_R_User as r   full join SMS_R_User as s1 on s1.ResourceId = r.ResourceId   full join SMS_R_User as s2 on s2.Name = s1.Name   where s1.Name = s2.Name and s1.ResourceId != s2.ResourceId


Machine generated alternative text:
Users - rnplicate Users 
All Users 

I then deleted the users in batchs, I did about 500 every day or every couple of hours depending on how much time I had. Active Directory User Discovery would then bring the current user accounts back in overnight.

One thing to watch out for is if you have users directly added to collections, they may not reappear back in those collections after they are deleted and reimported. This is not a common occurance in our environment, as our software is deployed to devices or User Groups rather than Users themselves.
Hope this can be of help if anyone experiences the same problem!