- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
‎03-26-2015 09:11 PM
Solved! Go to Solution.
- Labels:
-
General Discussion
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
‎03-27-2015 04:59 PM
The following code will remove AD object details which were updated before a certain date:
Delete From tblADobjects where LastScanned < '2015-03-20'
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
‎06-29-2023 10:38 AM
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))
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
‎06-29-2023 07:12 AM
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)
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
‎06-29-2023 07:12 AM
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*/
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
‎06-29-2023 06:40 AM
There is no CASCADE Delete for
table "dbo.tblADMembership", column 'ChildAdObjectID'
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
‎06-28-2023 05:19 PM
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.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
‎03-27-2015 04:59 PM
The following code will remove AD object details which were updated before a certain date:
Delete From tblADobjects where LastScanned < '2015-03-20'