Description
This article describes how to perform a FortiAnalyzer SQL database deletion and rebuild in response to an issue where an upgrade does not correctly update the SQL database and reporting ceases to function properly due to missing/misnamed columns and/or indexes.
'exec sql-local rebuild-db' is the recommended step to try first, but if it does not resolve the issue, deleting and rebuilding the database is the next step.
Be aware that rebuilding the database is a resource and time-consuming. The reports, log view, and FortiView will not be fully usable until the rebuild is complete.
Make sure to have enough resources available, as this process may impact system stability and performance.
Scope
FortiAnalyzer.
Solution
Remove (before 6.2) and re-create the SQL Database:
- Change operation mode to a collector (prior 6.2).
config system global
set log-mode collector
end
- 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.
diag sql remove rebuild-db-flag <----- Command to remove the rebuild DB for v7.2/v 7.4/ v7.6.
- Re-enable SQL (prior 6.2).
config system sql
set status local
end
- Change operation mode back to analyzer (prior 6.2).
config system global
set log-mode analyzer
end
- 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
- FortiAnalyzer SQL Database Rebuild.
execute sql-local rebuild-db <---It requires a restart.
execute sql-local rebuild-adom <AdomName> <--- (prior 7.6) 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 the SQL rebuild status.
diag sql show db-size <- This will show whether the DB size increases. If so, 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 filename mentions.
diag sql status rebuild-db <- This will show the progress of the SQL database rebuild ('rebuild-adom' can be used instead of 'rebuild-db' to see the progress of the ADOM level SQL database rebuild).
As of FortiAnalyzer v7.2.8 , the command 'diagnose sql status rebuild-db' display an error until 'diagnose sql status sqlplugind' report 100% beside 'logtbl-upgrade'.
diag sql status rebuild-db"
Checking db status error..
Command fail. Return code 1
diagnose sql status sqlplugind
.
.output trunkated
.
logtbl-upgrade: Running total=6623 finished=5947 perc=89% tbl-rewrite=no copy-upg=56 failed=0
diagnose sql status sqlplugind
.
.output trunkated
.
logtbl-upgrade: Done total=6623 finished=6623 perc=100% tbl-rewrite=no copy-upg=64 failed=0
diagnose sql status rebuild-db
Rebuilding all database accomplished on
Wed Nov 6 07:32:46 2024
exe tac report
exe top<- Let it run for 5 minutes.
Notes:
- There is no way to speed up the SQL DB rebuild process.
- The time required to rebuild the database depends on the amount of logs stored on the unit and resources. Approximately 1TB of the logs may require a rebuild period of 1-2 days. This depends on the environment, other tasks being run at the same time, and how many logs are being received.
- The rebuild-db command causes the unit to reboot, and the rebuild starts when the unit comes back up.
- Use the command 'diag sql status rebuild-db' to show the status of the rebuild.
- Although this procedure does not remove any log files, it is recommended to backup log files beforehand as a precaution.
- To terminate the database rebuild, this command can be run: 'diagnose sql remove rebuild-db-flag'.
Related article:
Technical Tip: Restarting SQL rebuilds
