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

Products

• CA Service Management - Asset Portfolio Management
• CA Service Management - Service Desk Manager

Issue/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.

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

Resolution

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 (resource_name)
asset_tag (resource_tag)
serial_number
ca_logical_asset (host_name)
ca_logical_asset_property (dns_name)
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.