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