Technical Tip: How to troubleshoot missing logs in FortiAnalyzer

[lingky88]

Description	This articles describes troubleshooting steps to resolve the cause of missing logs in FortiAnalyzer.
Scope	FortiAnalyzer.
Solution	1) In some cases, both analytics and archive logs are no longer visible under Log View in FortiAnalyzer: 2) In most cases, the FortiAnalyzer is also not receiving logs from the managed devices: 3) This is very likely due to a corrupted database. Verify this with the following CLI command: # diagnose sql show db-size ERROR: Failed to connect to server. Command fail. Return code -1002 # diagnose sql process list ERROR: Failed to connect to server. Command fail. Return code -1002 # diag debug application sqlplugind 8 sqlplugind debug filter:        "" # diag debug enable FAZ # [1681699040] DEBUG: sqlplugind(1361):dbplugin.c:2879: Waiting for postgres server up... [1681699040] WARNING: sqlplugind(1350):pgsvr_main.c:1432: Postgres is not running. [1681699042] INFO: sqlplugind(1350):pgsvr_main.c:1496: Starting postgres server... [1681699044] INFO: sqlplugind(1350):pgsvr_main.c:817: creating airflow folder. [1681699046] DEBUG: sqlplugind(1361):pq_plugin.c:19: set PQconnectdb options parameter=[options='-c TimeZone=Asia/Kuala_Lumpur'] [1681699046] ERROR: sqlplugind(1361):pq_plugin.c:24: Cannot connect to database, error: FATAL:  the database system is starting up [1681699046] INFO: sqlplugind(1361):dbplugin.c:2889: Retry on connecting db... [1681699051] DEBUG: sqlplugind(1361):pq_plugin.c:19: set PQconnectdb options parameter=[options='-c TimeZone=Asia/Kuala_Lumpur'] [1681699051] ERROR: sqlplugind(1361):pq_plugin.c:24: Cannot connect to database, error: FATAL:  the database system is starting up [1681699051] INFO: sqlplugind(1361):dbplugin.c:2889: Retry on connecting db... If the FortiAnalyzer is hardware, also check the raid status: # diag system raid status Mega RAID: RAID Level: Raid-50 RAID Status: Degraded 4) If the RAID status is degraded, it is recommended to replace the non-working disk with a working one by raising a separate ticket with the TAC/RMA team. 5) Perform a database rebuild to fix the corrupted database: # execute sql-local rebuild-db <-- will trigger a reboot of the device. 6) After the database rebuild is executed, check the rebuild status with the following commands: # diag sql status rebuild-db <-- rebuild % will gradually increase. # diag sql show db-size <-- this command should show that the DB size is gradually increasing. 7) If the database rebuilds task hangs, run the following command to stop the database rebuild and then perform a rebuild again: # diagnose sql remove rebuild-db-flag # config system sql set start-time <for ex:00:00 2023/01/01> end # execute sql-local rebuild-db <-- perform the SQL rebuild again. 8) Lastly, after the database rebuild successfully completes, verify if the logs are now visible under Log View and confirm the logging status of the managed devices.