Technical Tip: CMDB page shows duplicate key error while accessing the GUI

This issue can occur when a duplicate key entry is repeatedly processed in a loop, which prevents the CMDB page from loading properly and results in an error similar to the following:

ERROR: duplicate key value violates unique constraint "ph_external_reputation_20260221_value_value_type_source_key"
Detail: Key (value, value_type, source)=(108.181.221.59, 3, FortiGuard IOC Lookup) already exists.

To resolve this issue, run the following commands from the Supervisor CLI as an admin.

1. Remove temporary message queue files.

# su admin
# rm -f /opt/glassfish/domains/domain1/imq/instances/imqbroker/fs370/txnlog
# rm -f /opt/glassfish/domains/domain1/imq/instances/imqbroker/fs370/message/Qjms_RequestQueue/vrfile
# rm -f /opt/glassfish/domains/domain1/imq/instances/imqbroker/fs370/message/Qjms_EmailQueue/vrfile
# rm -f /opt/glassfish/domains/domain1/imq/instances/imqbroker/fs370/incompleteTxnStore/vrfile

2. Clear the cache and restart the application server.

# cd /opt/phoenix/cache/
# mv ip_dir ipdir_.old
Replace ip_dir with the Supervisor IP address.
Example:
mv 1.1.1.1 1.1.1.1-old

Then clear the application server cache and restart the service:

# cd /opt/glassfish/domains/domain1/
# rm -rf generated/
# rm -rf osgi-cache/
# kill -9 $(cat /opt/glassfish/domains/domain1/config/pid)

Log in to the FortiSIEM GUI again, and it will resolve the issue. If the issue persists, it may indicate an inconsistent user ID entry in the database, such as:

Unable to find com.ph.phoenix.model.cmdb.User with id 151882531
javax.persistence.EntityNotFoundException: Unable to find com.ph.phoenix.model.cmdb.User with id 151882531

Run the following commands on the Supervisor node to verify the database entries:

# psql -U phoenix phoenixdb -c "select * from ph_group_item where item_id='151882531';"

# psql -U phoenix phoenixdb -c "select * from ph_user where id=151882531;"

# psql -U phoenix phoenixdb -c "select id,cust_org_id,name,manager_id from ph_user where id=151882531;" > /tmp/users.txt

# psql -U phoenix phoenixdb -c "select id,cust_org_id,name,manager_id from ph_user where manager_id=151882531;" > /tmp/managers.txt

Review the output files to determine whether there are any invalid or orphaned user references.

/tmp/users.txt
/tmp/managers.txt

Note: Direct database modifications are not recommended. If backend changes are required, ensure that a full snapshot or backup of the environment is taken before making any modifications.