Description
This article explains how to troubleshoot errors encountered when using Chart Builder to build a dataset in Log View.
Scope
FortiAnalyzer.
Solution
In some cases, FortiAnalyzer's Chart Builder will return an error even though it can generate a SQL query based on configured parameters:
The following debug can be performed to see what is triggering the error:
diag debug app fazsvcd 255
diag debug en
[T7114:cmd_proxy.c:288] 1732155035 result of popen /bin/python /usr/local/python/sql-validator/validateSQL.py -s Dataset -i "select \`user\`, \`hostname\`, \`devid\` as \`regdevname\` from \$log where \$filter":
[{"example": [{"name": "Example 1", "dataset": "select coalesce(nullifna(`user`), nullifna(`unauthuser`), ipstr(`srcip`)) as user_src, sum(coalesce(sentbyte, 0)+coalesce(rcvdbyte, 0)) as bandwidth from $log where $filter and (logflag&1>0) group by user_src order by bandwidth desc", "position": [192, 233]}, {"name": "Example 2", "dataset": "select srcip, sum(sessions) as sessions from ###(select srcip, count(*) as sessions from $log where $filter and (logflag&1>0) and srcip is not null group by srcip order by sessions desc)### t group by srcip order by sessions desc", "position": [149, 186]}], "code": 1001, "message": "'group by' or 'order by' clause is expected in hcache.", "level": "Error"}]
In this case, the generated dataset is missing the 'Group By' and 'Order By' clauses, which are essential for optimizing SQL queries. This issue can be resolved by selecting an actual log field for the 'Group By' and 'Order By' parameters in the Chart Builder.:
