Improving report performance with report-grouping

Description

FortiAnalyzer (FAZ)  relies upon hcache tables to build reports. If a report is not scheduled and autocache is not enabled, the first run of the report will take extra time because the FAZ needs to first build the required hcache tables. 

Provided the filters associated with the report are not changed, subsequent runs of the reports will be much faster.  However, when the filter is changed the hcache tables need to be built again.

Case Scenario: User Filters

Consider the case in which a report is run with a filter for user01 and then that same report is rerun with the filter set to match user02. Unfortunately the report filtered for user02 will need to build the hcache table again and it will slow down report generation.

In this case scenario we can use feature called "report grouping" .  FAZ prepares hcache tables for all fields listed under report grouping configuration.

The report grouping must be configured from the Command Line Interface [CLI] of the FAZ.

Sample CLI syntax for Report Grouping based upon "user" field:

Report name:Top-web-user-request
Report id: 6  (came from #exec sql-report list-schedule <adom> )
Report filter: user=USER01, user=USER02

config system report group
    edit 1
        set adom "54ADOM"
            config group-by
                edit "user"  <----------| grouping by filtering field
                next
            end
        set report-like "Top-web-user"  <-----------| matches reports which include this string in their name

    next
end

To check the status of hcache table building:
#exec sql-report hcache-check <adom> <report ID> <start date> <end date>

To manually trigger the building of hcache tables (normally not required):
#exec sql-report hcache-build <adom> <report ID> <start date> <end date>

e.g. #exec sql-report hcache-build 54ADOM 6 "00:00 2017/01/01" "00:00 2018/01/01"

Summary:
This report grouping configuration will ensure that subsequent runs of the report will remain fast even if the user filter is changed.

To ensure that the initial run of the report is faster, make sure that the report is scheduled or has auto-cache enabled.