cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
cipher
Engaged Sweeper
I use Lansweeper (consulting license)on consulting engagements. Prior to starting the current engagement I deleted all the exiting assets. I also removed the domains from Scanning Methods. Unfortunately, I just realized that the previous customers AD domains are still in the database. How can I remove these without effecting the current customer domains?
1 ACCEPTED SOLUTION
Daniel_B
Lansweeper Alumni
If you don't prefer to completely reinstall Lansweeper (after taking a backup of your existing database), you could manually delete older data by executing SQL code under Configuration\Database scripts, section Database scripts.
The following code will remove AD object details which were updated before a certain date:

Delete From tblADobjects where LastScanned < '2015-03-20'

View solution in original post

6 REPLIES 6
Mister_Nobody
Honored Sweeper

Workaround SQL Query to delete orphaned adobjects

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()-30 and (tblADMembership.ChildAdObjectID is null and tblADGroups.ManagerADObjectId is null))

 

Mister_Nobody
Honored Sweeper

SQL Query to remove trash from tbladobjects:

Delete From tblADobjects where ADObjectID in (
Select tblADObjects.ADObjectID
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 < '2014-01-01' And tblADusers.ADObjectID Is Null
  And tblAdProperty.AdObjectId Is Null And tblADMembership.ChildAdObjectID Is
  Null And tblADMembership.parentAdObjectID Is Null And
  tblADComputers.ADObjectID Is Null And tblADGroups.ManagerADObjectId Is Null
  And tblADGroups.ADObjectId Is Null And tblBitLockerRecoveryKey.AdObjectId Is
  Null)

 

Mister_Nobody
Honored Sweeper

SQL Query to check conflicts:

Select 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,
  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 < '2014-01-01'
/* And tblADusers.ADObjectID Is Null
  And tblAdProperty.AdObjectId Is Null And tblADMembership.ChildAdObjectID Is
  Null And tblADMembership.parentAdObjectID Is Null And
  tblADComputers.ADObjectID Is Null And tblADGroups.ManagerADObjectId Is Null
  And tblADGroups.ADObjectId Is Null And tblBitLockerRecoveryKey.AdObjectId Is
  Null*/
Mister_Nobody
Honored Sweeper

There is no CASCADE Delete for 

table "dbo.tblADMembership", column 'ChildAdObjectID'
uSlackr
Engaged Sweeper II

Another way to handle this is to remove items for a specific domain with

Delete from tblADObjects where domain Like 'custdomain'

 I did run into an issue where we tried to remove data for an old child domain and received this error:

The DELETE statement conflicted with the REFERENCE constraint "FK_tblADMembership_tblADObject1". The conflict occurred in database "lansweeperdb", table "dbo.tblADMembership", column 'ChildAdObjectID'.
The statement has been terminated.

We might have had members in the child domain in parent domain groups.

Daniel_B
Lansweeper Alumni
If you don't prefer to completely reinstall Lansweeper (after taking a backup of your existing database), you could manually delete older data by executing SQL code under Configuration\Database scripts, section Database scripts.
The following code will remove AD object details which were updated before a certain date:

Delete From tblADobjects where LastScanned < '2015-03-20'