Duplication of CIs in BMC CMDB

Covers information on how to work with Reconciliation engines and rules.
Post Reply
hudatolah
Site Admin
Posts: 143
Joined: Thu Apr 04, 2013 8:10 pm
Are You a Headhunter?: Affirmative
Surfer?: Yes

Duplication of CIs in BMC CMDB

Post by hudatolah » Tue Nov 02, 2021 9:55 am

Running this query will expose duplicates generated by the defective BMC reconciliation engine and it will show which job had processed it:

Code: Select all

SELECT 
rjr.ActivityName, 
rjr.z_Tmp_JobName'Job Name',
a.Instanceid,
b.reconciliationidentity,
DATEADD(s,a.createdate,'19700101')'CI Create Date in Asset',
DATEADD(s,rjr.Create_Date,'19700101')'Job Run Date',
DATEADD(s,rjr.Modified_Date,'19700101')'Job End Date',
a.markasdeleted
      from BMC_Core_BMC_BaseElement a
      join BMC_CORE_BMC_BaseElement b on b.instanceid = a.instanceid 
     Join RE_JOB_RUns rjr on rjr.Instanceid = a.LastREJobRunId
where a.reconciliationidentity in 
                (
                select ReconciliationIdentity from bMC_CORE_BMC_BaseElement 
                where datasetid = 'BMC.ASSET' group by ReconciliationIdentity having count(*) > 1
                )
and a.datasetid = 'BMC.ASSET' and b.datasetid = 'BMC.ASSET'
Order by reconciliationidentity
The Blackholesurfer. My surfboard has teeth.

hudatolah
Site Admin
Posts: 143
Joined: Thu Apr 04, 2013 8:10 pm
Are You a Headhunter?: Affirmative
Surfer?: Yes

Re: Duplication of CIs in BMC CMDB

Post by hudatolah » Tue Nov 02, 2021 10:15 am

Marking the CI using an AI job:

Code: Select all

With Row_Numbered as 
		(select reconciliationidentity,instanceid,lastscandate, row_number() 
		over (partition by reconciliationidentity order by reconciliationidentity) as Row_Num
		from BMC_CORE_BMC_BaseElement  where datasetid = 'BMC.ASSET' ) 
select a.instanceid'A instanceid','DUPEbyREID' as DUPEbyREID,a.ReconciliationIdentity,DATEADD(s,a.LastScanDate,'19700101')'A scan date', DATEADD(s,r.LastScanDate,'19700101')'R scan date' from Row_Numbered r
join row_numbered a on a.reconciliationidentity = r.reconciliationidentity 
where r.Row_Num > 1
and a.lastscandate < r.lastscandate
The Blackholesurfer. My surfboard has teeth.

Post Reply