Covers information on how to work with Reconciliation engines and rules.
-
hudatolah
- Site Admin
- Posts: 155
- Joined: Thu Apr 04, 2013 8:10 pm
- Are You a Headhunter?: Affirmative
- Surfer?: Yes
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: 155
- Joined: Thu Apr 04, 2013 8:10 pm
- Are You a Headhunter?: Affirmative
- Surfer?: Yes
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.