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.

• Some alert and/or symptom definitions are missing in Aria Operations 8.18.0 and no recent upgrade has been performed.
• CIS Compliance Pack not activating alerts/symptoms.
  • A report run against the out-of-the-box CIS Compliance Pack has no data shown

Environment

Aria Operations 8.18.x

Aria Operations 8.14.x
VMware vRealize Operations 8.10.x
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.

Note:
In one Aria Operations 8.18.0 case after completing the above steps to remove the orphaned symptoms it was necessary to upgrade Aria Operations 8.18.0 to at least 8.18.2.