Technical Tip: How to run a report on a Time period older than the Analytic logs period

Description

This article describes what happens when running a report for a time period that is older than the oldest Analytic data.

When configuring Log Storage Policy, it is common to keep Archive data longer than Analytic data. 

Scope

FortiAnalyzer.

Solution

Reports can only be run on analytic data, which is log data that has been inserted into the SQL database and can be viewed in Log View.

To insert older data back into the SQL database, rebuild the entire database from an earlier date:

1. Configure the date to start the rebuild from, see FortiAnalyzer SQL database rebuild start-time.

Rebuild from the specific date: Defines the starting date from which the database is rebuilt.

config system sql
   set start-time <hh>:<mm> <yyyy>/<mm>/<dd>
end 

Example:
If the current date is 01-Sep-2025 and the logs for the last 90 days are needed, set the start date to 01-Jun-2025.

config system sql
    set start-time <00>:<00> <2025>/<06>/<01>
end

2. Make sure that the Log Storage Policy is adjusted to allow for more Analytic data. Otherwise, the FortiAnalyzer will immediately start trimming back analytic data again.

3. Start the rebuild for that ADOM:
   
   

execute sql-local rebuild-adom <ADOM name> 

Alternatively, rebuild the entire FortiAnalyzer using the command:

execute sql-local rebuild-db

Note:
The 'rebuild-db' command causes the FortiAnalyzer to reboot, and the rebuild begins once the system is back online. Although this procedure does not remove any log files, it is highly recommended to back up the system and log files beforehand as a precaution.

Rebuilding the SQL database can take some time (several hours to several days), depending on the amount of log data to be inserted.
During the rebuild, logging and reporting functionality will be limited, but logs will continue to be received.

To check the rebuild process status:

diagnose sql status rebuild-db

Alternatives to rebuilding the database: 

1. Reimport log files that have been exported. 
   The import automatically triggers the insertion of those logs into the SQL database, essentially adding them to the existing database.
   As with the SQL rebuild, make sure the Log Retention Policy has been adjusted to extend back far enough to include these newly imported logs. 
   

Note:
If this data was previously exported from the same FortiAnalyzer, this may result in duplicate log entries in the Archive logs (unless care was taken to delete those logs after exporting).

2. If another FortiAnalyzer VM is available, use Log Fetch, which is ideally suited to pulling older log data that meets filtered criteria.
   This is the most elegant solution as it avoids log duplication and the need to repeatedly change the Log Retention Policy on the production FortiAnalyzer.
   
   

Related article:

Technical Tip: FortiAnalyzer SQL database rebuild