Hcache

The hcache is a proprietary FortiAnalyzer report caching system. The hcache runs Report/FortiView queries on the matured DB log tables in advance and stores the results in the DB “hcache tables”. When a report or FortiView is running, it will search the pre-built hcache results first instead of running the whole dataset query based on the log tables. This significantly decreases the time for running queries.

In datasets, “###(“ and “)###” are the start and end hcache tags; the query between them is called an hcache query. These are used for creating hcache tables. The query outside the three hash signs will use the results in the hcache table.

The hcache tags are optional. If a dataset query does not use three hashes but has a log table macro, "$log", the whole query will be an hcache query. In this case, the report engine will automatically add three hashes and the rest of the query outside the hcache query. The whole query can be found by clicking Analyze when creating or editing the dataset.

Hcache base queries are between "###base(" and ")base###". Some hcache queries share the same hcache base queries.

Both Fortiview and dataset queries may have /*tag:rpt_base_t_top_app*/ tags. Those tags are for naming the hcache, for example, “rpt_base_t_top_app” is a report top-application hcache base on traffic logs.

Filter-drilldown

The filter-drilldown, "where $filter-drilldown", comes outside the hcache query. When the filter values vary, the hcache query will not be affected, so the hcache table does not need to be rebuilt. Normally, you do not need to add the filter-drilldown to the query as it’s automatically added by the report engine where applicable.

Group by and order by clauses

Due to performance concerns, the number of rows in hcache tables are limited. An hcache table will be trimmed if its length exceeds the maximum limit. Group by and order by clauses are needed in the hcache query to aggregate and select the significant results.

Note that "/*skipSTART*/" and "/*SkipEND*/" are usually used around the order by clauses. The auto-cache engine will monitor if the data trimming happens while running the hcache. If there is no data trimming, then the order by is not necessary for the hcache. In this case, the order by clause inside the tags will be removed for better query performance.

The /*fabricStart*/ and /*fabricEnd*/ are for FortiAnalyzer fabric cases in the supervisor.

Grouping by itime or dtime

If a result is grouped by "itime" or "dtime", the important results may be trimmed and lost.

The dtime is the device time when FortiGate creates logs, and the itime is the timestamp when logs come to FortiAnalyzer.

If dtime does not exist, FortiAnalyzer will add it into logs. In fact, there is a special handling on FortiAnalyzer dtime:

dtime = the timestamp (epoch time) when the log was created + the timezone offset in seconds.

In such a way, it’s easy to get the FortiGate log local time with from_dtime(dtime) in SQL.

As there are often FortiGates from various time zones registered in FortiAnalyzer, the best practice is to use itime, which is in UTC timestamp and timezone independent. The dtime is mainly used when showing FortiGate local time or calculating time duration; for example, max(dtime) - min(dtime).

Regarding time accuracy, as the default FortiGate config to send logs is near real-time, the itime timestamp created by FortiAnalyzer when it receives FortiGate logs is acceptable as opposed to the timestamp when FortiGate sends the log. When there is a very strict requirement on time accuracy, we can use eventtime, which is in nanoseconds when the FortiGate log is generated.

The "$flex_timescale(timestamp)" will give data points based on report range. For example, one data point per hour for a seven-day report, and one data point per five minutes for a one-day report.