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 dataset generated is missing the 'Group By' and 'Order By' clauses, which are necessary for efficient SQL queries. This error can be rectified when an actual log field is selected for 'Group By' and 'Order By' parameters in Chart Builder:
