Technical Tip: How to Remove Decommissioned Windows Agents from CMDB when Encountering an Error

Before performing the actions:

• Take a snapshot of the VM.
• Copy a Backup of CMDB to a different location.

Follow these steps to remove the affected devices:

1. Verify Device Information in Relevant Tables. Export the ph_device and ph_windows_agent_status tables to review the entries for the hostname and device_id:

psql -U phoenix phoenixdb -c "SELECT * FROM ph_device;" --csv > /tmp/log/ph_device.csv

psql -U phoenix phoenixdb -c "SELECT * FROM ph_windows_agent_status;" --csv > /tmp/log/ph_windows_agent_status.csv

Note:

The --csv > /tmp/log/ option is optional and can be omitted if not needed. Identify the device_id or id (for ph_device) of the target device. This article will use below as an example:

device_id = 12345678910

Hostname = duplicate01

2. Verify Associated Entries in Other Tables. Check for related entries in the ph_incident2device and ph_installed_software tables:

psql -U phoenix phoenixdb -c "SELECT * FROM ph_incident2device;" --csv > /tmp/log/ph_incident2device.csv

psql -U phoenix phoenixdb -c "SELECT * FROM ph_installed_software;" --csv > /tmp/log/ph_installed_software.csv

3. Access the PostgreSQL Database. Login to PostgreSQL to execute SQL commands:

psql -U phoenix -d phoenixdb

4. Query Entries for the Target Device. Check for rows in ph_incident2device where device_id = 12345678910 and host_ip = 'duplicate01':

SELECT * FROM ph_incident2device WHERE device_id = 12345678910 AND host_ip = 'duplicate01';

Query all rows in ph_incident2device for device_id = 12345678910:

SELECT * FROM ph_incident2device WHERE device_id = 12345678910;

Inspect the entry in ph_device where id = 12345678910:

SELECT id, name, access_ip, win_machine_guid FROM ph_device WHERE id = 12345678910;

5. Update and Remove Associated Entries. Update and delete rows in the database to disassociate and clean up the records:

• Update the 'device_id' in ph_incident2device to 0:

UPDATE ph_incident2device SET device_id = 0 WHERE device_id = 12345678910;

• Delete entries from ph_installed_software:

DELETE FROM ph_installed_software WHERE device_id = 12345678910;

• Delete the device entry from ph_device:

DELETE FROM ph_device WHERE id = 12345678910;

6. Delete the Device from the GUI. Once the above steps are completed, attempt the deletion via the FortiSIEM GUI.