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 displayed: 

AHD05543: Your changes to Registration parameters match an existing Configuration Item.

Based on CORA Rules, a duplicate Asset or CI is being recognized.

How to check the MDB database for possible asset/CI duplicates?

Environment

CA Service Desk Manager 17.x

Resolution

Here is the list of CORA related MDB tables:

• ca_asset
• ca_logical_asset
• ca_logical_asset_property
• ca_asset_source
• ca_discovered_hardware
• ca_owned_resource 
  
  There are six (6) columns being used to determine the uniqueness of an asset:

1. ca_asset (resource_name)
2. asset_tag (resource_tag)
3. serial_number
4. ca_logical_asset (host_name)
5. ca_logical_asset_property (dns_name)
6. mac_address

The corresponding database 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

CORA database queries:

From Machine name into ca-asset:

select 'ca_asset',* from ca_asset where label like '%HW%'

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.  For example, 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 two (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%'))
 
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%'))
 
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%')))

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%')))

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