ConfigMgr CB: ‘Delete Aged Discovery Data’ Internals (with Case Study)

Posted on by — No Comments ↓

This post has been republished via RSS; it originally appeared at: Configuration Manager Archive articles.

First published on TECHNET on Jun 29, 2017

https://blogs.technet.microsoft.com/umairkhan/2017/06/29/configmgr-cb-delete-aged-discovery-data-internals-with-case-study/

 

Hi Folks,

Many a times we do get cases where the Delete Aged Discovery Data is not deleting the records that we expect it to delete. So here are some findings that are not documented and I have had quite a few cases.

 

Starting with where do we set it; Pretty easy as everyone knows we configure it in Site Maintenance.

 

 

So the task is supposed to run and clean the data which are above the threshold of the configured value.

In troubleshooting cases where you don’t want to change the schedule but want to run it now. You can do this by Making the RunNow to 1.

 

select * from SQLTaskStatus where TaskName = 'Delete Aged Discovery Data'

A sample of the SMSDBMON.log when this task runs.

 

06-03-2017 00:02:47.051 SMS_DATABASE_NOTIFICATION_MONITOR 8332 (0x208c) CTriggerManager::ExecuteTask - deleting discovery items that haven't been discovered in 14 days 

06-03-2017 00:04:29.284 SMS_DATABASE_NOTIFICATION_MONITOR 8332 (0x208c) CleanupAgedGroupMembership - Successfully cleaned up obsolete memberships in DB. 

06-03-2017 00:04:29.448 SMS_DATABASE_NOTIFICATION_MONITOR 8332 (0x208c) CleanupAgedGroupMembership - Successfully flatten the Group membership 

06-03-2017 00:04:29.449 SMS_DATABASE_NOTIFICATION_MONITOR 8332 (0x208c) Cleaning up aged records from ActiveDirectoryObjectInfo. 

06-03-2017 00:04:31.694 SMS_DATABASE_NOTIFICATION_MONITOR 8332 (0x208c) CleanupAgedGroupMembership - Successfully clean up aged records from ActiveDirectoryObjectInfo. 

06-03-2017 00:04:31.811 SMS_DATABASE_NOTIFICATION_MONITOR 8332 (0x208c) DeleteDiscoveryItems deleted 0 rows of architecture User Group 

06-03-2017 00:04:31.831 SMS_DATABASE_NOTIFICATION_MONITOR 8332 (0x208c) STATMSG: ID=2404 SEV=I LEV=M SOURCE="SMS Server" COMP="SMS_DATABASE_NOTIFICATION_MONITOR" SYS=Computer.domain.COM SITE=012 PID=23652 TID=8332 GMTDATE=Fri Jun 02 23:04:31.830 2017 ISTR0="Delete Aged Discovery Data - User Group" ISTR1="0" ISTR2="" ISTR3="" ISTR4="" ISTR5="" ISTR6="" ISTR7="" ISTR8="" ISTR9="" NUMATTRS=0 

06-03-2017 00:04:32.569 SMS_DATABASE_NOTIFICATION_MONITOR 8332 (0x208c) DeleteDiscoveryItems deleted 0 rows of architecture User 

06-03-2017 00:04:32.571 SMS_DATABASE_NOTIFICATION_MONITOR 8332 (0x208c) STATMSG: ID=2404 SEV=I LEV=M SOURCE="SMS Server" COMP="SMS_DATABASE_NOTIFICATION_MONITOR" SYS=Computer.domain.COM SITE=012 PID=23652 TID=8332 GMTDATE=Fri Jun 02 23:04:32.570 2017 ISTR0="Delete Aged Discovery Data - User" ISTR1="0" ISTR2="" ISTR3="" ISTR4="" ISTR5="" ISTR6="" ISTR7="" ISTR8="" ISTR9="" NUMATTRS=0 

06-03-2017 01:06:17.499 SMS_DATABASE_NOTIFICATION_MONITOR 8332 (0x208c) DeleteDiscoveryItems deleted 417 rows of architecture System 

06-03-2017 01:06:17.500 SMS_DATABASE_NOTIFICATION_MONITOR 8332 (0x208c) STATMSG: ID=2404 SEV=I LEV=M SOURCE="SMS Server" COMP="SMS_DATABASE_NOTIFICATION_MONITOR" SYS=Computer.domain.COM SITE=012 PID=23652 TID=8332 GMTDATE=Sat Jun 03 00:06:17.500 2017 ISTR0="Delete Aged Discovery Data - System" ISTR1="417" ISTR2="" ISTR3="" ISTR4="" ISTR5="" ISTR6="" ISTR7="" ISTR8="" ISTR9="" NUMATTRS=0 

06-03-2017 01:06:17.920 SMS_DATABASE_NOTIFICATION_MONITOR 8332 (0x208c) DeleteDiscoveryItems deleted 0 rows of architecture IP Network 

06-03-2017 01:06:17.921 SMS_DATABASE_NOTIFICATION_MONITOR 8332 (0x208c) STATMSG: ID=2404 SEV=I LEV=M SOURCE="SMS Server" COMP="SMS_DATABASE_NOTIFICATION_MONITOR" SYS=Computer.domain.COM SITE=012 PID=23652 TID=8332 GMTDATE=Sat Jun 03 00:06:17.921 2017 ISTR0="Delete Aged Discovery Data - IP Network" ISTR1="0" ISTR2="" ISTR3="" ISTR4="" ISTR5="" ISTR6="" ISTR7="" ISTR8="" ISTR9="" NUMATTRS=0


So here we go by line to delete Systems, Users etc. We see it goes by checking different architectures ‘User Group’, ‘User’, ‘System’, ‘Ip Network’.

The flow here is simple, First it find all contender ItemKeys for deletion for the specific architectures and then deletes it from the dependent and base tables.

So what are different architectures and their base tables.

 

 

So for the DiscArchKey 3,4,5,6 we do run a SP and find the Itemkeys that are above the retention threshold. So what do we actually run?

Exec [dbo].[sp_GetAgedDiscoveryItems] 
@cutoffDate = (getdate() - @NoOfDaysToRetainConfiguredInTask), discArchKey

 

So this would run for each architecture and then for the System Architecture ItemKeys obtained it removes their dependent records in dependent lower tables first by running spRemoveResourceDataForDeletion .

And pretty much for all the Architectures it then deletes it from the base table one by one the ItemKeys obtained above. (As simple as delete from BaseTable where Itemkey = @ItemKey in loop)

SO that’s pretty much it with respect to the flows.

Case Study

We generally here get cases for the records still remain or not do not get deleted even after the task runs successfully.

For eg. Running the below query we see there are like more than 10K plus record which last had the DDR processed on more than 60 days back. But this site has been configured to clean up anything older than 14 days.

 

select sys.resourceid, sys.Name0, sys.client0, agenttime, 
DATEDIFF(dd,disc.agenttime,getdate()) from v_R_System sys
left join (select resourceid, max(agenttime) agenttime from 
v_AgentDiscoveries group by resourceid) as disc
on sys.ResourceID = disc.ResourceId
where DATEDIFF(dd,disc.agenttime,getdate()) > 14
and sys.Operating_System_Name_and0 like '%windows%'
order by agenttime


So as explained I start with running what does Configuration Manager thinks of old records with respect to System architecture.

Ran


Exec [dbo].[sp_GetAgedDiscoveryItems] @cutoffDate = (getdate() - 14), @discArchKey =5

 

It gave only like 150 records. So ConfigMgr doesn’t think we have to archive 10K records which came from above query but only 150 records.

Why?

Looking at the query from the SP_GetAgedDiscoveryItems closely

 

declare @siteRangeStart int
declare @siteRangeEnd int
set @siteRangeStart = dbo.fnGetSiteRangeStart()
set @siteRangeEnd = dbo.fnGetSiteRangeEnd()
declare @cutoffdate datetime = getdate() - 14
declare @isReservedRangedSite int
select @isReservedRangedSite = isnull(Value3, 0) from 
SC_SiteDefinition_Property where SiteNumber = dbo.fnGetSiteNUmber() and Name = 
N'ReplicatesReservedRanges'
select dia.DiscArchKey, dia.ItemKey 
from DiscItemAgents dia inner join System_DISC sd on dia.ItemKey = sd.ItemKey 
 
where ((dia.ItemKey between 
@siteRangeStart and @siteRangeEnd) or ((dia.ItemKey between 2063597568 and 
2147483647) and @isReservedRangedSite = 1))
group by dia.ItemKey, dia.DiscArchKey, sd.Client_Type0 
having max(AgentTime) <= @cutoffDate and dia.DiscArchKey = 5 and 
isnull(sd.Client_Type0, 1) = 1

 

 

Check the where condition in the clause.

We delete the Itemkeys within the range of each site. And Itemkeys in general are 8 digit keys. However our Itemkeys were 10 digit ones that came in the 1 st query and they didn’t have the ConfigMgr clients.

 

  • So here is the thing, A new record that gets discovered from AD discovery it gets that long 10 digit key and only after the registration\heartbeat DDR they get into the site range Itemkeys.
  • So these are those records that got discovered but NEVER were ACTIVE ConfigMgr clients. But they should be cleaned right ?

 

Before I answer that condition lets understand the highlighted condition

((dia.ItemKey between @siteRangeStart and @siteRangeEnd) or ((dia.ItemKey between 2063597568 and 2147483647) and @isReservedRangedSite = 1))

Contains two parts

 

  1. ((dia.ItemKey between @siteRangeStart and @siteRangeEnd) : This was meant for System architecture Itemkeys.
  2. ((dia.ItemKey between 2063597568 and 2147483647) and @isReservedRangedSite = 1)) : This is meant for User Itemkeys. Note that User Itemkeys are 10 digit long. And we have one more condition isReservedRangedSite = 1. This means that we treat the User as global data and don’t delete it from every site. We only delete it from the first primary site. That’s what the condition (isReservedRangedSite = 1) means. Run this (select ReplicatesReservedRanges, * from Sites where ReplicatesReservedRanges = 1) to find the first primary site.

 

  • So we delete System architecture data from all sites but User Itemkeys from the first primary site for that being global data would be replicated to all sites from there.
  • But our case was we were having those 10 digit long Itemkeys for System architecture. So that also would be only falling in that condition. Meaning those System Itemkeys would be deleted only from the first Primary site.
  • So that also explains why these were getting deleted after 60+ days and not 14 days. Because the first primary site has configured the value for deletion as 60 days.

 

One more important takeaway is Even if you set Task to delete aged data as 14, it will still follow the first Primary site value (in our case it was 60) for deletion of User Itemkeys.


So that’s it now we what’s happening in the background. And what are the conditions for the ItemKeys selection for deletion.

Hope it helps if you are troubleshooting the task next time!

 

~UK

Support Escalation Engineer | Microsoft System Center Configuration Manager

Disclaimer: This posting is provided “AS IS” with no warranties and confers no rights.

Leave a Reply

Your email address will not be published. Required fields are marked *

*

This site uses Akismet to reduce spam. Learn how your comment data is processed.