Symptom and/or alert definitions missing after upgrading to Aria Operations 8.10 or later product or management pack

Products

• VMware Aria Suite

Issue/Introduction

Symptoms:

• After upgrading to Aria Operations 8.10 or later, or upgrading a Management Pack on Aria Operations 8.10 or later, some alert and/or symptom definitions are missing
• Log /storage/log/vcops/log/analytics=UUID.log contains errors like:

2023-09-13T13:14:25,546+0000 ERROR [ServerConnection on port 10000 Thread 3226] com.vmware.vcops.kvstore.postgres.PostgresDao.updateDB - Update DB failed, errMsg=PreparedStatementCallback; SQL [INSERT INTO kv_symptomproblemdefinition (col_kv_strvalue, col_kv_valuetype,
    col__kv_version, key) VALUES (?, ?, ?, ?)]; ERROR: duplicate key value violates unique constraint "kv_symptomproblemdefinition_pkey"
    Detail: Key (key)=(SymptomRelationship-SymptomDefinition-physicaldisks.physdiskoverall.Warning) already exists.; nested exception is org.postgresql.util.PSQLException: ERROR: duplicate key value violates unique constraint "kv_symptomproblemdefinition_pkey"
    Detail: Key (key)=(SymptomRelationship-SymptomDefinition-physicaldisks.physdiskoverall.Warning) already exists.

Environment

VMware Aria Operations 8.14.x
VMware vRealize Operations 8.10.x
VMware Aria Operations 8.12.x

Resolution

This issue affects Aria Operations 8.10.x or later.  There is no resolution at this time.

Workaround:

It is recommended to take snapshots following How to take a Snapshot of VMware Aria Operations before following the workaround steps.

To work around this issue, remove orphaned symptom relationships from the database.

1. Log into the Primary node as root via SSH or Console.
2. Run the following command to export the current symptom and alert definitions table to a csv file:

su - postgres -c "/opt/vmware/vpostgres/current/bin/psql -d vcopsdb -p 5433 -c \"COPY kv_symptomproblemdefinition TO '/tmp/kv_symptomproblemdefinition.csv' WITH (FORMAT CSV, HEADER TRUE);\""

3. Copy the /tmp/kv_symptomproblemdefinition.csv file to a location other than the primary node as a backup.
4. Run the following command to get a list of orphaned SymptomRelationship rows in the kv_symptomproblemdefinition table:

su - postgres -c "/opt/vmware/vpostgres/current/bin/psql -d vcopsdb -p 5433 -c \"

SELECT sr.key
FROM kv_symptomproblemdefinition AS sr
WHERE sr.key LIKE 'SymptomRelationship-SymptomDefinition-%'
    AND NOT EXISTS (
        SELECT *
        FROM kv_symptomproblemdefinition AS sd
        WHERE sd.key = RIGHT(sr.key, -LENGTH('SymptomRelationship-'))
    );\""

Note: If no rows are returned, contact support for additional troubleshooting.

5. Run the following command to delete the orphaned SymptomRelationship rows:

su - postgres -c "/opt/vmware/vpostgres/current/bin/psql -d vcopsdb -p 5433 -c \"
DELETE FROM kv_symptomproblemdefinition AS sr
WHERE sr.key LIKE 'SymptomRelationship-SymptomDefinition-%'
    AND NOT EXISTS (
        SELECT *
        FROM kv_symptomproblemdefinition AS sd
        WHERE sd.key = RIGHT(sr.key, -LENGTH('SymptomRelationship-'))
    );\""

6. Optional: Retry the upgrade or management pack install.
7. Verify expected symptom and alert definitions now exist in the Aria Operations UI.
    

Additional Information

Impact/Risks:
It is recommended to take snapshots following How to take a Snapshot of VMware Aria Operations  before following the workaround steps.