Description
This article describes examples of how the hcache query structure in the datasets can affect the overall accuracy of the the FortiAnalyzer reports.
A basic description of what hcache is and the related terms can be found in the FortiAnalyzer documentation Hcache.
Scope
FortiAnalyzer all versions.
Solution
The report queries are normally run on the hcache, not directly on the database. As a result, the report result includes only the data that was already aggregated in the respective hcache tables. This makes the report generation faster, but the hcache number of rows per-table is limited (the example below is the default limit of a FortiAnalyzer-VM):
diagnose sql config hcache-max-rpt-row
max number of report hcache: 18000
When a query from a dataset is run to create hcache, the variables $log and $filter are resolved, and the hcache limit is applied as an SQL limit, at the end of the resulting query. For example (this output is visible from the live debug of 'sqlreportd').
<...>
Execute SQL query(624): DROP TABLE IF EXISTS tmpins; CREATE TEMPORARY TABLE tmpins AS /*SQLQRY*/ select 60871 as hid, * from (/*HCACHE-RPT (FSFADOM3-FGT-tlog-1686653280)*/ SELECTCOALESCE(nullifna("user"), nullifna("unauthuser"), ipstr("srcip")) AS user_src, action, service, COUNT(*) AS sessions FROM (SELECT ti1.*, ti2."devid",ti2."vd","devname","csf","devgrps" FROM "FSFADOM3-FGT-tlog-1686653280" ti1 LEFT JOIN "devtable_ext" ti2 ON ti1.dvid=ti2.dvid ) t1 WHERE (1=1) GROUP BY user_src, action, service ORDER BY sessions DESC limit 18001) t; insert into hcache."FSFADOM3-HCACHE_7376" select * from tmpins
<...>
In other words, if the hcache query returns more results, only the top 18’001 records (by number of sessions in this example) will be used in the count function of the outer query. Normally, that is fine, since the least significant results contribute very little to the total count.
However, a lack of or incorrectly set GROUP BY and ORDER BY in the hcache query can significantly reduce the accuracy of the final result.
Selecting columns where the records cannot be practically grouped (for example: 'itime') or selecting too many columns, making each result distinct, would also impact the accuracy.
Here are a few examples:
- Run the query on the right directly on the log database (no hcache, no limit). The result is:
| Result |
Dataset query
|
| type |
sessions |
| Allow |
4169225 |
| Block |
9387355 |
|
SELECT
(CASE WHEN action = 'accept' THEN 'Allow' ELSE 'Block' END) AS type,
sum(sessions) AS sessions
FROM
###(
SELECT action, COUNT(*) AS sessions
FROM $log
WHERE $filter
GROUP BY action
ORDER BY sessions DESC
)### t
GROUP BY type
|
- Use the same query, in a dataset, chart, and report. The result in the generated report (where hcache limit of 18'000 rows is applied) shows a visible difference, but 3000 sessions out of 13 million sessions in total, still give an acceptable accuracy of about 99.98%:
| type |
sessions |
| Allow |
4169221 |
| Block |
9385423 |
|
SELECT
(CASE WHEN action = 'accept' THEN 'Allow' ELSE 'Block' END) AS type,
sum(sessions) AS sessions
FROM
###(
SELECT action, COUNT(*) AS sessions
FROM $log
WHERE $filter
GROUP BY action
ORDER BY sessions DESC
)### t
GROUP BY type
|
- If just the ORDER BY of the hcache query is changed, the accuracy of the result changes, and the difference now increases to 650'000 sessions, resulting in approximately 95% accuracy:
| type |
sessions |
| Allow |
3969819 |
| Block |
8934329 |
|
SELECT
(CASE WHEN action = 'accept' THEN 'Allow' ELSE 'Block' END) AS type,
sum(sessions) AS sessions
FROM
###(
SELECT action, COUNT(*) AS sessions
FROM $log
WHERE $filter
GROUP BY action
ORDER BY sessions ASC
)### t
GROUP BY type
|
- This last example adds the 'itime' column to the hcahe query. Since the timestamp can't be practically aggregated (unless all sessions terminated in the same exact second), even with correct ORDER BY, the difference from the real number of sessions increases dramatically to 4.3 million, resulting an accuracy of just 33%:
| type |
sessions |
| Allow |
2215238 |
| Block |
2266877 |
|
SELECT
(CASE WHEN action = 'accept' THEN 'Allow' ELSE 'Block' END) AS type,
sum(sessions) AS sessions
FROM
###(
SELECT itime, action, COUNT(*) AS sessions
FROM $log
WHERE $filter
GROUP BY itime, action
ORDER BY sessions DESC
)### t
GROUP BY type
|
If higher accuracy is required, the hcache limits can be increased (albeit not indefinitely):
- Globally by these CLI commands:
diagnose sql config hcache-max-fv-row set {1000-500000}
diagnose sql config hcache-max-rpt-row set {1000-500000}
- Or per-report by enabling 'High Accuracy Caching' (increasing the hcache for the report to 500’000 row) in the report Settings tab.
However, with many reports or charts, this may require a significant increase of the system resources, such as Memory, CPU, IOPS, and Storage space, due to the larger hcache. The general rule of thumb is 16 GB and 8 CPU cores for every 100’000 hcache rows, or more if the hcache limits of FortiView are also increased. These numbers are, of course, just for guidance. The actual hardware requirements may vary depending on the specifics.
Related article:
Technical Tip: How to create FortiAnalyzer reports using custom SQL queries
