Technical Tip: FortiSIEM migration and ClickHouse configuration procedure

Description

This article describes how to migrate FortiSIEM data from one system to another when ClickHouse is used as the online storage backend.

Scope

FortiSIEM.

Solution

This guide focuses on ClickHouse-based deployments. The same methodology can be adapted for other storage types; however, ClickHouse is the primary scope of this document.  

Requirements:

Both the source and target FortiSIEM systems must meet the following requirements:

• Same FortiSIEM version: This is strongly recommended, though database upgrades are possible if required.

• Online storage: ClickHouse.

• Valid license for the target FortiSIEM system.

Migration steps:

1. Copy the CMDB backup (Source -> Target).

Notes: 

The CMDB backup is located on the source FortiSIEM at /data/archive/cmdb/.

The CMDB backup file must be copied to the /tmp directory on the target FortiSIEM.

For example: phoenixdb_202X-XX-XXTXX-XX-XX.

2. Capture the original database values (target).

Before restoring the CMDB, capture the existing system configuration values on the target system.

psql -U phoenix -d phoenixdb -c "select id,value, property from ph_sys_conf" > ph_sys_conf_orig.txt

To view the captured file:

cat ph_sys_conf_orig.txt

3. Collect and save all passwords.

Run the following commands and securely store the output.

Each command returns a password that must be saved in case the FortiSIEM system needs to be reverted during or after the process.

phLicenseTool --showSvnPassword
phLicenseTool --showDatabasePassword
phLicenseTool --showRedisPassword
phLicenseTool --showServicePassword

4. Restore the CMDB.

1. Stop Services on the Target FortiSIEM.
   
   

systemctl stop crond.service
systemctl stop phxctl
phtools --stop all
phstatus
killall -9 java
killall -9 phMonitor
phstatus

2. Restore the database.

/opt/phoenix/deployment/db_restore.sh /tmp/phoenixdb_xxxxx

5. Reset database IPs on the target FortiSIEM.

Replace x.x.x.x with the Target FortiSIEM IP address.

psql -U phoenix -d phoenixdb
update ph_sys_server set ip_addr='x.x.x.x' where id=1;
delete from ph_sys_server where mode in (3,1);
update ph_sys_conf set value='https://x.x.x.x/svn' where property='svn_url';

6. Restart the App Server and re-upload the license.

1. Start FortiSIEM services:

phxctl start

2. Upload the license using the following URL:

https://x.x.x.x/phoenix/licenseUpload.jsf

Optional: restore GUI access (test user):

If GUI access is unavailable after migration, a temporary administrative user can be created using the following command:

psql -U phoenix phoenixdb -f /opt/phoenix/deployment/add-super-admin.sql

Credentials:

• Username: test_fsm.

• Password: Test*123.

7. Disable notifications (optional).

This step removes email configuration from the default notification templates.

psql -U phoenix phoenixdb
update ph_sys_conf set value='' where property='Mail_Server';

8. Clean cluster configuration (UI).

Navigate to: Admin -> Settings -> Cluster Config.

• Remove the old Workers' IPs.

• Update all IPs to point to the Supervisor.

• Save.

Password synchronization:

9. Update database and admin passwords.

1. Update the database password:

db_password=`phLicenseTool --showDatabasePassword`
psql -U phoenix phoenixdb -c "ALTER USER phoenix WITH PASSWORD '${db_password}';"

2. Restart database-related processes:

phtools --start phQueryMaster
phtools --start phRuleMaster

3. Capture the database password again.

phLicenseTool --showDatabasePassword

4. Update the admin password file (use the password provided during step 3 and change the password below).

su admin
vi /tmp/passwd.txt

Add:

AS_ADMIN_PASSWORD= A12G7XXgDn@8
AS_ADMIN_ALIASPASSWORD= A12G7XXgDn@8
AS_ADMIN_NEWPASSWORD= A12G7XXgDn@8

Save the file and exit.

10. Sync the admin password.

Run the following commands:

/opt/glassfish/bin/asadmin --user admin --passwordfile /tmp/passwd.txt change-admin-password
/opt/glassfish/bin/asadmin --user admin --passwordfile /tmp/passwd.txt update-password-alias phdbpwd

Hostname and SSH keys:

11. Update Hostname using the built-in-utility:

configFSM.sh

• Continue selecting Next / OK.

• At Configure Supervisor, choose option 3.

• Continue until completion.

12. Add SSH Public Keys (UI).

Admin -> License -> Nodes.

Admin SSH key.

su - admin
ssh-keygen -t rsa -b 4096
cat /opt/phoenix/bin/.ssh/id_rsa.pub

HA user SSH key:

cat /home/pghauser/.ssh/id_rsa.pub

Redis and ClickHouse Cleanup:

13. Clear the Redis Cache for ClickHouse.

1. Capture the Redis password. (The password shown here will reflect the source system due to the migration. This will be cleaned up in a later step.)

cat /opt/phoenix/config/phoenix_config.txt | grep redis_auth=

2. Connect to Redis with the password from step 1.

redis-cli -p 6666 -a <redis_password>

3. List ClickHouse Keys:

keys '*clickhouse*'

4. Delete keys individually:

del "cache:phDataManager:clickhouseQuery:21192"
del "cache:ClickHouse:clickhouseConfig"
del "cache:phDataManager:clickhouseQuery:21193"
del "cache:phDataManager:clickhouseLogIntegrity"
del "cache:phDataManager:clickhouseTableEngine"
del "cache:ClickHouse:clickhouseNodes"
del "cache:phDataManager:clickhouseLogIntegrityConsolidationActive"
del "cache:ClickHouse:clickhouseKeeperNodes"

5. Run the ClickHouse Cleanup script.

/opt/phoenix/phscripts/clickhouse/cleanup_clickhouse.sh

6. Update ClickHouse cluster configuration in the GUI. 

Navigate to Admin -> Settings -> ClickHouse Cluster.

• Configure Supervisor only for Keeper, Data, and Query.

• Test and Save.

7. Redis Sync (update all folders from the password result).

phLicenseTool --showRedisPassword

Update file below with correct Redis Password:

/opt/phoenix/config/phoenix_config.txt 
/opt/node-rest-service/ecosystem.config.js
/opt/phoenix/redis/bin/redis_ops.sh
/opt/phoenix/redis/conf/6666.conf 

Notes:

Make sure the entry for redis_auth OR redis_auth_6379 in the files are updated correctly

8. Stop the ClickHouse monitor.

killall -9 phClickHouseMonitor

9. Backup configuration files.

(This is a single code block. Copy the entire snippet and paste it on the target system.)

for f in /opt/node-rest-service/ecosystem.config.js \
/opt/phoenix/redis/bin/redis_ops.sh \
/opt/phoenix/redis/conf/6666.conf \
/opt/phoenix/config/phoenix_config.txt \
/opt/phoenix/config/svnlite.properties; do
cp "$f" "${f}_orig"
done

10. Run the script to update the Redis password across all necessary configuration files:

/opt/phoenix/deployment/jumpbox/ph_update_dr_configs.py 

14. Restart services:

1. Restart Apache and postgres.
   
   

service httpd restart
systemctl restart postgresql-$(postgres -V | awk '{print $3}' | cut -d. -f1).service

2. Restart Redis.
   
   

rm -f /opt/phoenix/redis/conf/6666.conf
cd /opt/phoenix/redis/bin/
./redis_ops.sh stop
./redis_ops.sh start

3. Restart Node services:

su admin
pm2 restart all 

15. Verification.

1. Allow 10–25 minutes for Redis to stabilize.

Monitor logs:

tail -f /opt/phoenix/log/phoenix.log | grep -i redis

2. Expected log message:

Redis connection is healthy

Once this message appears consistently, Redis synchronization is complete.

16. Clean residual hosts from the CMDB.

Some legacy ClickHouse nodes from the source system may still appear in the CMDB, and errors may be encountered when attempting to delete them.

1. Verify the hostnames that need to be removed from the CMDB.

2. Log into the console of the target device and access the database.

psql -U phoenix -d phoenixdb

3. Disable all triggers on the ph_device table to allow deletion without triggering foreign key constraint errors:
   
   

ALTER TABLE ph_device disable TRIGGER ALL;

4. Delete the devices using the exact hostnames:
   
   

DELETE FROM ph_device
WHERE name IN (
'keeper01.net',
'keeper02',
'super01.net',
'testworker01.net',
'testworker02.net'
);

5. Re-enable all triggers on the ph_device table:

ALTER TABLE ph_device enable TRIGGER ALL;

6. Exit the database and verify that the devices have been removed from the UI.