‎02-06-2024
05:04 AM
- last edited on
‎04-01-2024
12:31 PM
by
Mercedes_O
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.
‎05-03-2024 02:05 PM
Hello there!
For reference, a bug was logged under ID LAN-16449: Orphaned AD Objects. You can see which bugs have been fixed in our changelog: https://www.lansweeper.com/changelog.aspx
‎02-12-2024 03:57 PM
Hello there!
Great contribution as usual! 😎
‎05-02-2024 05:44 AM
‎05-02-2024 10:55 AM - edited ‎05-02-2024 10:57 AM
Hello there!
I will look into this one, at first glance, I could not find a current bug for this, but I will do some more digging. Or get it logged...
‎05-02-2024 05:28 PM
‎05-03-2024 10:59 AM
Hello there!
Thanks for bringing these posts to my attention, I will make sure these get added to the bug report.
Experience Lansweeper with your own data. Sign up now for a 14-day free trial.
Try Now