Changes of user relationship in AD can’t be correctly synchronized to SCSM

Issue Description

The relationship of users might be not correctly updated if related users were once re-named in AD or have duplicate records in DB.

Cause 1 – Known Issue: a renamed user in AD will be treated as a new object in SCSM

Symptom

There are multiple records for the same user in BaseManagedEntity.

Analysis

There is a blog talking about this kind of behavior.

I did a test in my lab environment (SCSM 2012 R2). After I rename a user in AD and run AD connector, there will be two objects co-existing in the table [dbo].[ManagedEntity] for the user, and two relationships in the table [dbo].[Relationship].

For example, after I change my user name from “wendi” to “wendii”, then to “wendie”, there are 3 user objects and 3 relationships in the DB.

Relationship:

BaseManagedEntity:

Then I manually deleted “wendi” from DB, and changed the manager from “weiwen” to “Administrator” in AD. The relationship got updated for the newest object “wendie”, but not for the old object “wendii”. That resulted in two managers for the user “wendii”/”wendie”, which is actually the same user in AD.

Resolution

Firstly remove the duplicated users from [dbo].[ManagedEntity].

1. If you don’t want to lose the relationships associated with the old object, please use the script in the blog to move all relationships from old object to new object.

Note: In the script there is a path pointing to SCSM PowerShell Module. You may need to alter it manually based on the real location.

2. Remove the duplicate user object:

$oldADUser = "wendi"
Get-SCClassInstance -Class (Get-SCClass -Name "System.Domain.User") -Filter "UserName -eq $oldADUser | Remove-SCClassInstance

After confirming there is no duplicate user objects, please change the manager relationship in AD, then check if the relationship can be updated correctly by AD connector.

Cause 2 – Duplicate AD connector introduces duplicate user records

If there is only 1 record in BaseManagedEntity, but multiple records in [LFXSTG].[AD_User], it is probably from duplicate AD connectors.

We can check the data sources of the records in [LFXSTG].[AD_User]:

This query can give you information about all data sources (connectors):

Select * from LFX.Datasource

A sample output:

Resolution

Disable all duplicate connectors. After that, all users modified in the future could be correctly updated.

For those users which are already affected by the issue, we can follow below steps to process them.

1. Run this query in ServiceManager to get the users who have duplicate relationships with relationship isDeleted = 0, as well as the connectors that brought the relationships.
   (This query focuses on “manager” relationship)

Select distinct
u.DisplayName 'User Display Name',
u.UserName_6AF77E23_669B_123F_B392_323C17097BBD 'User', 
Manager.UserName_6AF77E23_669B_123F_B392_323C17097BBD 'Manager', 
r.RelationshipId, 
r.IsDeleted 'Is Relationship Deleted',
C.DisplayName 'Connector',
BME.IsDeleted 'Is Connector Deleted',
R.LastModified
from Relationship R
left join RelationshipType RT on R.RelationshipTypeId = RT.RelationshipTypeId
left join MT_System$Domain$User Manager on manager.BaseManagedEntityId = R.SourceEntityId
left join MT_System$Domain$User U on u.BaseManagedEntityId = r.TargetEntityId
inner join DiscoverySourceToRelationship DSTR on R.RelationshipId = DSTR.RelationshipId
Left join DiscoverySource DS on DS.DiscoverySourceId = DSTR.DiscoverySourceId
left join MT_Connector C on convert(nvarchar(256),DS.ConnectorId) = C.Id
Left join BaseManagedEntity BME on C.BaseManagedEntityId = BME.BaseManagedEntityId
where RelationshipTypeName like '%System.UserManagesUser%' and u.BaseManagedEntityId in
(
Select 
u.BaseManagedEntityId
from Relationship R
left join RelationshipType RT on R.RelationshipTypeId = RT.RelationshipTypeId
left join MT_System$Domain$User Manager on manager.BaseManagedEntityId = R.SourceEntityId
left join MT_System$Domain$User U on u.BaseManagedEntityId = r.TargetEntityId
where RelationshipTypeName like '%System.UserManagesUser%' and r.IsDeleted = 0
group by U.UserName_6AF77E23_669B_123F_B392_323C17097BBD, u.BaseManagedEntityId
Having count(u.UserName_6AF77E23_669B_123F_B392_323C17097BBD) > 1)
order by U.UserName_6AF77E23_669B_123F_B392_323C17097BBD

2. Use below steps to automate the removal of un-needed relationships whilst keeping the most current one.

   a. Copy the query result with headers and save as a .csv file.
   b. Remove the needed relationships from the .csv file.
   c. Use below PowerShell commands to remove the un-needed relationships.

   # You may change the file path.
   $listcsv = Import-Csv C:\Files\UnneededRelationships.csv
   foreach($list in $listcsv)
   {
       Get-screlationshipinstance -id $listcsv.RelationshipId | remove-screlationshipinstance
   }