cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
Mister_Nobody
Honored Sweeper

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.

1 REPLY 1
Obi_1_Cinobi
Lansweeper Tech Support
Lansweeper Tech Support

Hello there!

Great contribution as usual! 😎

New to Lansweeper?

Try Lansweeper For Free

Experience Lansweeper with your own data.
Sign up now for a 14-day free trial.

Try Now