Technical Tip: FortiSIEM migration and ClickHouse configuration procedure

[aebadi]

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: Copy the CMDB backup (Source -> Target). 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. 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 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 Restore the CMDB. Stop Services on the Target FortiSIEM. systemctl stop crond.service systemctl stop phxctl phtools --stop all phstatus killall -9 java killall -9 phMonitor phstatus Restore the database. /opt/phoenix/deployment/db_restore.sh /tmp/phoenixdb_xxxxx 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'; exit Restart the App Server and re-upload license. Start FortiSIEM services: phxctl start 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 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'; exit 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: Update database and admin passwords. Update the database password: db_password=`phLicenseTool --showDatabasePassword` psql -U phoenix phoenixdb -c "ALTER USER phoenix WITH PASSWORD '${db_password}';" Restart database-related processes: phtools --start phQueryMaster phtools --start phRuleMaster Capture the database password again. phLicenseTool --showDatabasePassword 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. 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 exit Hostname and SSH keys: Update Hostname using the built-in-utility: configFSM.sh Continue selecting Next / OK. At Configure Supervisor, choose option 3. Continue until completion. 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 exit HA user SSH key: cat /home/pghauser/.ssh/id_rsa.pub Redis and ClickHouse Cleanup: Clear the Redis Cache for ClickHouse. 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= Connect to Redis with the password from step 1. redis-cli -p 6666 -a <redis_password> List ClickHouse Keys: keys '*clickhouse*' 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" exit Run the ClickHouse Cleanup script. /opt/phoenix/phscripts/clickhouse/cleanup_clickhouse.sh Update ClickHouse cluster configuration in the GUI.  Navigate to Admin -> Settings -> ClickHouse Cluster. Configure Supervisor only for Keeper, Data, and Query. Test and Save. Redis Sync (update all folder from the password result). phLicenseTool --showRedisPassword Files to update: /opt/phoenix/config/phoenix_config.txt redis_auth= redis_6379_auth= /opt/node-rest-service/ecosystem.config.js /opt/phoenix/redis/bin/redis_ops.sh /opt/phoenix/redis/conf/6666.conf  Stop the ClickHouse monitor. killall -9 phClickHouseMonitor 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 Run the script to update the Redis password across all necessary configuration files: /opt/phoenix/deployment/jumpbox/ph_update_dr_configs.py  Restart services: Restart apache and postgres. service httpd restart systemctl restart postgresql-$(postgres -V | awk '{print $3}' | cut -d. -f1).service Restart Redis. rm -f /opt/phoenix/redis/conf/6666.conf cd /opt/phoenix/redis/bin/ ./redis_ops.sh stop ./redis_ops.sh start Restart Node services su admin pm2 restart all exit Verification. Allow 10–25 minutes for Redis to stabilize. Monitor logs: tail -f /opt/phoenix/log/phoenix.log | grep -i redis Expected log message: Redis connection is healthy Once this message appears consistently, Redis synchronization is complete. 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. Verify the hostnames that need to be removed from the CMDB. Log into the console of the target device and access the database. psql -U phoenix -d phoenixdb Disable all triggers on the ph_device table to allow deletion without triggering foreign key constraint errors: ALTER TABLE ph_device disable TRIGGER ALL; Delete the devices using the exact hostnames: DELETE FROM ph_device WHERE name IN ( 'keeper01.net', 'keeper02', 'super01.net', 'testworker01.net', 'testworker02.net' ); Re-enable all triggers on the ph_device table: ALTER TABLE ph_device enable TRIGGER ALL; Exit the database and verify that the devices have been removed from the UI.