Removing duplicate device datafield records

Products

• VMware Smart Assurance

Issue/Introduction

Overview of how to:

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

More than one row with the given identifier was found after 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

NCM 10.1.X

Cause

Due to a possible migration failure, duplicate records are found in the 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