Technical Tip: How to troubleshoot missing logs in FortiAnalyzer

[Anonymous]

Description	This articles describes troubleshooting steps to resolve the cause of missing logs in FortiAnalyzer.
Scope	FortiAnalyzer.
Solution	In some cases, both analytics and archive logs are no longer visible under Log View in FortiAnalyzer: In most cases, the FortiAnalyzer is also not receiving logs from the managed devices: 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 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. Perform a database rebuild to fix the corrupted database: execute sql-local rebuild-db <-- will trigger a reboot of the device. 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. 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 Then change the start-time from the default value. For example, if the analytics policy is set for 60 days, it is recommended to change the start-time to count 60 days before the current day. config system sql set start-time <for ex:00:00 2023/01/01> end execute sql-local rebuild-db <-- perform the SQL rebuild again. 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.