How to check for duplicate Assets or Configuration Items based on CORA.

Document ID : KB000107484
Last Modified Date : 20/07/2018
Show Technical Document Details
Introduction:
Sometimes, it is not possible to save modification that have been made on some assets (identical model). 
The following error is or could be displayed: 
AHD05543: Your changes to Registration parameters match an existing Configuration Item.
Background:
Based on CORA Rules, a duplicate Asset or CI is being recognized.
And it would be nice to check the mdb database content for possible duplicates yourself.
Environment:
Windows
Instructions:
Please check on the below information. Apparently, a 'duplicate asset' is being recognized.

Here is the list of CORA related mssql tables:
ca_asset
ca_logical_asset
ca_logical_asset_property
ca_asset_source 
ca_discovered_hardware 
ca_owned_resource 

There are 6 columns being used to determine the uniqueness of an asset:
ca_asset                 : label(resource_name),asset_tag(resource_tag),serial_number
ca_logical_asset         : host_name
ca_logical_asset_property: dns_name and mac_address

With the corresponding queries:
select 'ca_asset',asset_uuid,label,asset_tag,serial_number,asset_type_id from ca_asset
select 'ca_logical_asset',logical_asset_uuid,asset_uuid,host_name from ca_logical_asset
select 'ca_logical_asset_property',logical_asset_uuid,dns_name,mac_address from ca_logical_asset_property
select 'ca_asset_source',asset_source_uuid,logical_asset_uuid from ca_asset_source
select 'ca_owned_resource',resource_name,resource_tag,serial_number,host_name,dns_name,mac_address,own_resource_id,asset_type_id,asset_source_uuid,resource_contact_uuid from ca_owned_resource

And some CORA mssql queries:
First, from Machine name into ca-asset:
select 'ca_asset',* from ca_asset where label like '%HW%'

Then, from ca_asset into ca_logical_asset(using asset_uuid):
select 'ca_logical_asset',* from ca_logical_asset
where asset_uuid in (select asset_uuid from ca_asset
                      where label like '%HW%')
 
The ca_logical-asset_property will show the logical instances of the same asset.
E.G., if the same asset is registered by CORA with different DNS and/or MAC address but same HOST name, 
CORA recognizes it is the same assets and stores 2 logical instances in this table.
 
select 'ca_logical_asset_property',* from ca_logical_asset_property
where logical_asset_uuid in
  (select logical_asset_uuid from ca_logical_asset
   where asset_uuid in (select asset_uuid from ca_asset 
                         where label like '%HW%'))
 
Then, from ca_logical_asset to ca_asset_source (using logical_asset_uuid):
select 'ca_asset_source',* from ca_asset_source 
where logical_asset_uuid in
   (select logical_asset_uuid from ca_logical_asset 
    where asset_uuid in 
      (select asset_uuid from ca_asset 
       where label like '%HW%'))
 
Then, from ca_asset_source into DSM ca_discovered_hardware (using asset_source_uuid):
select 'ca_discovered_hardware',* from ca_discovered_hardware 
where asset_source_uuid in 
  (select asset_source_uuid from ca_asset_source 
   where logical_asset_uuid in 
     (select logical_asset_uuid from ca_logical_asset 
      where asset_uuid in 
        (select asset_uuid from ca_asset 
         where label like '%HW%')))

Then, from ca_asset_source into UAPM/USD ca_owned_resource 
(using asset_source_uuid):
select 'ca_owned_resource',* from ca_owned_resource 
where asset_source_uuid in 
  (select asset_source_uuid from ca_asset_source 
   where logical_asset_uuid in 
     (select logical_asset_uuid from ca_logical_asset 
      where asset_uuid in 
        (select asset_uuid from ca_asset 
         where label like '%HW%')))

Based on the 6 columns being used to determine the uniqueness of an asset:
ca_asset                 : label(asset_name),asset_tag(alternate_id),serial_number
ca_logical_asset         : host_name
ca_logical_asset_property: dns_name and mac_address

And then the PDM_DISCIMP query:
SELECT DISTINCT
 a.label,a.asset_tag,a.serial_number,
 la.host_name,la.logical_asset_uuid,
 lap.dns_name,lap.mac_address
 FROM
 ca_asset a,
 ca_logical_asset la,
 ca_logical_asset_property lap,
 ca_asset_source asr
 WHERE
 a.asset_type_id = 1 AND
 la.asset_uuid = a.asset_uuid AND
 lap.logical_asset_uuid = la.logical_asset_uuid AND
 (NOT (asr.logical_asset_uuid = la.logical_asset_uuid AND
  la.logical_asset_uuid IN
    (SELECT ca_asset_source.logical_asset_uuid
     FROM ca_asset_source
     WHERE ca_asset_source.subschema_id = 3)) AND
 asr.logical_asset_uuid = la.logical_asset_uuid AND
 asr.delete_time IS NULL)
 ORDER BY a.label
Additional Information:
Please turn on the traces to reproduce the issue: pdm_logstat -f mdb_registration.c TRACE
Trace entries will then be written to the stdlog file. 
Please reproduce the problem situation and stop the trace by running: pdm_logstat -f mdb_registration.c
Then, please attach the stdlog file to the issue for analysis.