How to get CMDB Reconciliation errors without Logs

[hudatolah]

First you should begin with a SQL query that gives you an idea on the size of the errors per job:

Code: Select all

SELECT count(*) as CI_Failure#, JobName
case when Module_Name = 0 then 'Reconciliation' ELSE 'Normalization' END as JobType
from CMDB_JobRunInfo 
group by JobName, Module_Name
order by CI_Failure# DESC

This will give you the number of CI failures that failed in that job and the type of the job.
Next you should probably work it by the highest count of errors. I recommend this query:

Code: Select all

select NE_ClassName, NE_ClassNamespace, DatasetID, JobName, JobRunFailure_RecommendedActio, JobRunFailure_ErrorDescription, Job_Run_Instance_ID, CIInstanceId, JobRunFailure_Category, CIName 
from CMDB_JobRunInfo where jobname = '(put in the job name with the highest count or errors)'

Each error has an explanation to fix the CI. You may need some expert help to get it resolved.
If you like to use my consulting service then please find me (https://www.linkedin.com/in/daniel-hudsky-350b5b1/) on LinkedIn or the product vendor (BMC Software) to get help fixing these errors. I can do this much faster then the product vendor, but if you're already paying for support then follow your best course.

The errors are going to be mostly due to duplication of records in the CMDB or Product Catalog. Keep in mind that CI's that fail Normalization are excluded from Normalization. Your CMDB maybe missing thousands of records due to lack of maintenance.
Note: Detailed report for all CI is shown in my reply below

Daniel

[hudatolah]

Run this query to create a report related to your data:

Code: Select all

select
be.name as CIName, 
be.classid as ClassId,
be.category as Category, 
be.type as Type, 
be.item as Item,
be.model as Model, 
be.manufacturername as Manufacturer, 
a.JobName as JobName, 
case
   when a.Module_Name = 1 and be.MarkAsDelete = 1 then 'NORM: Missing from PCT although purge is recommended.'
   when a.Module_Name = 1 and be.MarkAsDeleted <> 1 then 'RE: Fails to Identify or Merge. Check Identification or Merge Rules. Validate Normalization Status'
   when a.Module_Name = 0 and be.MarkAsDeleted <> 1 then 'RE: Fails to Identify or Merge. Check Identification or Merge Rules. Validate Normalization Status'
    when a.Module_Name = 0 and be.MarkAsDelete = 1 then 'RE: Consider purging this CI'
end as JobType_Fix,
be.instanceid as InstanceId,
be.MarkAsDeleted
from BMC_CORE_BMC_BaseElement be
 left join  CMDB_JobRunInfo a on a.CIIinstanceid = be.instanceid
where be.instance in
 (select CIInstanceId from CMDB_JobRunInfo)

Run this as often as you can and watch the trends.
Results will show the CI related info, but also help you categorize Normalization versus Reconciliation errors.
The results can be saved based on the MarkAsDeleted status. Where NULL and 0 means NOT Soft Deleted and 1 means Soft Deleted true