Smarts NCM: How to remove duplicate device datafield records?

Products

• VMware Smart Assurance

Issue/Introduction

Symptoms:


How to remove duplicate devices from the Smarts Network Configuration Manager (Smarts NCM) database?
How to remove duplicate device datafield records in Smarts NCM?


More than one row with the given identifier was found after Smarts NCM migration

The following error is received after Smarts NCM migration:

Error: ava.rmi.ServerException: RuntimeException; nested exception is:
class com.powerup.common.CMSystemException:More than one row with the given identifier was found

Environment

VMware Smart Assurance - NCM

Cause

Due to a possible migration failure, duplicate records are found in the Smarts NCM database.

Resolution

The following sections explain how to determine/identify and delete duplicate devices indicated in the above error.

Identifying the duplicate devices
To see the number of devices and which devices are duplicates run the following query:

Query to see duplicate devices via HEX:

SELECT
                cm_device.device_idx,
                cm_device.device_name,
                encode(sq_multi_record_devices.device_id,'hex'),
                encode(sq_multi_record_devices.device_datafields_id,'hex'),
                sq_multi_record_devices.version,
                sq_multi_record_devices.v0,
                sq_multi_record_devices.v1,
                sq_multi_record_devices.v2
FROM (
                SELECT
                                cm_device_datafields.device_id,
                                device_datafields_id,
                                version,
                                v0,
                                v1,
                                v2
                FROM (
                                SELECT
                                                device_id,
                                                count(*) AS record_count
                                FROM
                                                cm_device_datafields
                                GROUP BY 1
                                ) AS sq_records_per_device
                INNER JOIN
                                cm_device_datafields
                ON
                                (sq_records_per_device.device_id=cm_device_datafields.device_id)
                WHERE
                                (sq_records_per_device.record_count > 1)
                ) AS sq_multi_record_devices
INNER JOIN
                cm_device
ON
                (cm_device.device_id=sq_multi_record_devices.device_id)
ORDER BY
                cm_device.device_idx ASC,
                sq_multi_record_devices.device_datafields_id DESC;

Deleting duplicate devices
To remove duplicate devices run the following delete query:

DELETE FROM
                cm_device_datafields_test
USING
                cm_device_datafields_test AS self
WHERE
                (cm_device_datafields_test.device_id = self.device_id) AND
                (cm_device_datafields_test.device_datafields_id < self.device_datafields_id);

Additional Information

If you wish to keep the lowest device_datafields_id value, simply change the less than (<) operator on the last line to a greater than (>) operator in the delete query
To log into postgres, see 17344 : "Smarts NCM or VoyenceControl: How do I log in to the PostgreSQL Control Database?"