Description
This article describes what is hcache and when a manual rebuild of hcache tables is advisable.
As with log viewing and searching, reporting builds upon analytical data.
But rather than running directly upon the original SQL database, report generation makes use of intermediate tables called hcache tables.
Use of hcache tables allow for faster report generation provided the hcache tables are successfully built in advance.
In most circumstances, the hcache tables will automatically be built in the background if either of the following is checked:
- Enable Schedule.
- Enable Auto-cache (available in FortiAnalyzer 5.4 and later).
auto-cache acts on logs arriving (inserted) after one or both of these options has been selected.
Scope
FortiAnalyzer.
Solution
When a manual rebuild of the hcache tables is advised.
A manual hcache rebuild is recommended:
- When the report grouping configuration has been changed Will speed up associated reports.
- When a new filter is applied to report changes (without any applicable report grouping applied).
- A dataset associated with a report is changed.
In rare circumstances, it may also be useful in the following scenario:
- The report shows 'no matching log data' but dataset shows results and chart seems to be correctly configured.
- Report generation does not complete (for issues other than high CPU utilization).
An Hcache rebuild does not require a reboot, unlike an SQL rebuild, and is less time-consuming than an SQL rebuild.
Note:
In environments with high log rates (more than 30000 logs/sec) this process can last several days.
Hcache rebuild can be run per report and so is more specific than SQL rebuild.
How to trigger a manual rebuild of hcache tables.
(CLI syntax below is for FortiAnalyzer 5.4. Check the appropriate CLI reference guide for other firmware).
- For a single report template:
exec sql-report hcache-build <adom> <report template>
To check whether hcache tables have been built for each of the charts in the report:
exec sql-report hcache-check <adom> <report template>
- For a time period (all reports):
diag sql hcache rebuild-report <yyyy-mm-dd hh:mm:ss> <yyyy-mm-dd hh:mm:ss>
As an example with start and end time for one month:
diagnose sql hcache rebuild-report "2015-08-01 00:00:00" "2015-09-01 00:00:00"
- For a time period (per adom-name):
diagnose sql hcache rebuild-report <adom-name> <start-time> <end-time>
As an example with start and end time for one month (per adom-name):
diagnose sql hcache rebuild-report root "2025-05-12 00:00:00" "2025-06-12 00:00:00"
It is possible to verify the rebuild status by examining the output of:
diagnose test application sqlrptcached 2
Number of log table pending: all=17534(rpt=16735, fv=798, increment-fv=1)
If this number is stable around 0, then the rebuild has been completed.
The same check can be also performed with the command:
diagnose sql hcache status <adom> <----- Where <adom> is the ADOM where the rebuild has been executed.
auto-cache order: oldest-first
....
hcache pending=17534(rpt=16735, fv=798, increment-fv=1)
Note: The following command can be used to rebuild both the FortiView hcache and Reports hcache. This is useful when issues are encountered with report generation or FortiView data display.
diagnose sql hcache rebuild-both
Related article:
Technical Tip: Examples of how the FortiAnalyzer hcache query affects the report accuracy
