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

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: https://blogs.technet.microsoft.com/manageabilityguys/2013/06/17/managing-username-changes-in-service-manager/

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:

DataSourceId DataSourceName DataSourceAddress DisplayName
2 ADConnector.af492f92b2d04b4092d2f0c6108a4aff LDAP://DC=contoso,DC=com CONTOSO AD Connector
11 ADConnector.762fe647adc14c2daba1191291e6b37f LDAP://OU=AdminUsers,OU=Users,DC=contoso,DC=com CONTOSO AD Connector - Users
12 ADConnector.b93d4f78d799462280e841e228f5f921 LDAP://DC=contoso,DC=com CONTOSO AD Connector - Printers
13 ADConnector.d49f58ebf7b84c509c3426cde55c7cd5 LDAP://DC=contoso,DC=com CONTOSO AD Connector - Computers
14 ADConnector.e56e3f09b5614c5ea554e3d0c06c7da4 LDAP://DC=contoso,DC=com CONTOSO AD Connector - Groups
15 ADConnector.747d1f1f252f46a59634378686a32dbe LDAP://OU=SCSM,OU=Groups,DC=contoso,DC=com CONTOSO AD Connector - SCSM Groups

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.

  • 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
  • Use below steps to automate the removal of un-needed relationships whilst keeping the most current one.
    1) Copy the query result with headers and save as a .csv file.
    2) Remove the needed relationships from the .csv file.
    3) 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
}
posted @ 2019-03-21 21:50 娘阔菜菜 阅读(...) 评论(...) 编辑 收藏
耐克篮球多少钱
平特码多少倍 淘宝快3属于什么情况 微信吉林快三骗局揭秘 亚洲彩票投注平台 七乐彩复式投注 六合彩赚率 大乐透官网app 竟彩足球既时比分 搜狐彩票吧 彩票开奖时间表图片 河南十一选五下载 北京赛车公式技巧 金冠娱乐城怎样赢 浙江20选5 平码平肖综合资料大全