Unsupported: Use at your own risk
Sometimes you load data into metaverse that is supposed to be unique and you do not want to use FindMVEntries technique due to performance impact.
This SQL scripts allow you to find out directly from DB if you have unexpected duplicates so you can talk to data store owners to go and fix their data.
The following example looks for repeated mailNickName attribute values in metaverse. Just replace “mailNickName” with “SAMAccountName”, “uid” or whatever attribute you have that should be unique:
SELECT mailNickName, COUNT(mailNickName) AS NumOccurrences
FROM FIMSynchronizationService.dbo.mms_metaverse with (nolock)
where object_type = 'person'
GROUP BY mailNickName
HAVING ( COUNT(mailNickName) > 1 )
order by NumOccurrences desc
You could also use this technique to find the opposite, that is, records with a particular value that occur exactly once:
GROUP BY email
HAVING ( COUNT(email) = 1 )
However, the previous queries give just details about the number of records that you have duplicates or unique. Finding out more details about the metaverse records require more elaborated SQL query.
In the following example, we first find duplicated sAMAccountName values and store in temporary “tblDups” table, that then we use to join with full metaverse (“MV”) and show “displayName” and “object_id” attributes.
select tblDups.sAMAccountName, MV.displayName, MV.object_id
SELECT sAMAccountName, COUNT(sAMAccountName) AS NumOccurrences
GROUP BY sAMAccountName
HAVING ( COUNT(sAMAccountName) > 1 )
) as tblDups
FIMSynchronizationService.dbo.mms_metaverse AS MV WITH (nolock)
tblDups.sAMAccountName = MV.sAMAccountName
You can also add WHERE sentences at the end of the query to find specific values, such as those sAMAccountNames duplicates that belong to HR department:
WHERE MV.department = 'HR'