LS has bug in SQL Model - partially not working Remove AD object function.
When LS removes AD object SQL has some triggers which can not correctly clean AD objects info.
Firsty, you can run report to check count of orphaned objects in your database:
Select Distinct Top 1000000 tblADObjects.sAMAccountName,
tblADObjects.domain,
tblADObjects.LastScanned,
tblADusers.ADObjectID As asuser_id,
tblAdProperty.AdObjectId As property_id,
tblADMembership.ChildAdObjectID As membership_child,
tblADMembership.parentAdObjectID As membership_parent,
(Select tblADGroups.name From tblADGroups
Where tblADMembership.parentAdObjectID = tblADGroups.ADObjectID) As
group_name,
tblADComputers.ADObjectID As computer_id,
tblADGroups.ManagerADObjectId As group_manager_id,
tblADGroups.ADObjectId As group_id,
tblBitLockerRecoveryKey.AdObjectId As bitlock_id
From tblADObjects
Left Join tblADComputers On tblADObjects.ADObjectID =
tblADComputers.ADObjectID
Left Join tblADGroups On
tblADObjects.ADObjectID = tblADGroups.ManagerADObjectId Or
tblADObjects.ADObjectID = tblADGroups.ADObjectId
Left Join tblADMembership On tblADObjects.ADObjectID =
tblADMembership.ChildAdObjectID Or
tblADObjects.ADObjectID = tblADMembership.parentAdObjectID
Left Join tblAdProperty On tblADObjects.ADObjectID = tblAdProperty.AdObjectId
Left Join tblADusers On tblADObjects.ADObjectID = tblADusers.ADObjectID
Left Join tblBitLockerRecoveryKey On tblBitLockerRecoveryKey.AdObjectId =
tblADComputers.ADObjectID
Where tblADObjects.LastScanned < GetDate() - 7
Order By tblADObjects.LastScanned Desc
To clean such objects you have to create SQL job with 2 steps (next acts are not supported by LS)
1. clean membership:
delete from tblADMembership where tblADMembership.ChildAdObjectID in (Select tblADObjects.ADObjectID
From tblADObjects
Left Join tblADMembership On
tblADObjects.ADObjectID=tblADMembership.ChildAdObjectID or tblADObjects.ADObjectID=
tblADMembership.parentAdObjectID
Where tblADObjects.LastScanned < getdate()-7 and (tblADMembership.ChildAdObjectID is not null ))
2. Delete Objects:
Delete From tblADobjects where ADObjectID in (Select tblADObjects.ADObjectID
From tblADObjects
Left Join tblADGroups On
tblADObjects.ADObjectID =tblADGroups.ManagerADObjectId or
tblADObjects.ADObjectID= tblADGroups.ADObjectId
Left Join tblADMembership On
tblADObjects.ADObjectID=tblADMembership.ChildAdObjectID or tblADObjects.ADObjectID=
tblADMembership.parentAdObjectID
Where tblADObjects.LastScanned < getdate()-7 and (tblADMembership.ChildAdObjectID is null and tblADGroups.ManagerADObjectId is null))
I have scheduled it one time per week.
