Technical Tip: FortiAnalyzer SQL database delete and rebuild

[mdeparisse_FTNT]

Description

This article describes that occasionally an upgrade does not correctly update the SQL database and reporting will cease to function properly due to missing/misnamed columns and/or indexes.

'# exec sql-local rebuild-db' is the first option, but if that does not resolve the issue then deleting and rebuilding of the database is the next step.

Be aware that rebuilding the database is resource and time-consuming and the reports as well as log view and FortiView will not be fully usable until the rebuild is over.

Make sure to have enough resources as it may impact system stability and performance.

Solution

Remove (before 6.2) and re-create the SQL Database:

1. Change operation mode to collector (prior 6.2).
   
   config system global
       set log-mode collector
   end
   
   
2. Disable SQL and remove the current database (prior 6.2).
   
   config system sql
       set status disable
   end
   
   execute sql-local remove-db      <----- This command does not exist after 6.2.0.
   
   
3. Re-enable SQL (prior 6.2).
   
   config system sql
       set status local
   end
   
   
4. Change operation mode back to analyzer (prior 6.2).
   
   config system global
       set log-mode analyzer
   end
   
   
5. Set the following value to rebuild the database with logs from the expected date (this depends on how many Analytics days are configured for this ADOM).
   
   For example, if 90 days have been requested and it is the 1st of September 2021.
   
   config system sql
       set start-time <for ex:00:00 2021/06/01>
   end
   
   
6. Rebuild database.
   
   execute sql-local rebuild-db            <----- Require a restart. Or
   
   execute sql-local rebuild-adom adomName       <----- Does not require a restart.

Troubleshooting SQL rebuild.

diag test app sqllogd 4    <----- This will let the user know which file the rebuilding process is handling at present, it is necessary to run it multiple times to check SQL rebuild status.

diag sql show db-size       <----- This will show if the DB size increases if it is run it multiple times a few minutes apart.

diag sql process list       <----- It is possible sometimes to see a process here with commands like 'Create table'. 'Insert' or mentioning filenames.

exe tac report

exe top                   <----- Let it run for 5 min.

(1) The rebuild-db command causes the unit to reboot and the rebuild starts when the unit comes back up.

(2) Use the command 'diag sql status rebuild-db' to show the status of the rebuild.

(3) The time required to rebuild the database depends on the amount of logs stored on the unit and resources.

(4) Although this procedure does not remove any log files it is recommended to backup log files beforehand as a precaution.