Fortinet Forum
The Forums are a place to find answers on a range of Fortinet products from peers and product experts.
pcraponi
Contributor II

SQL wrong result between dataset and report

Hi,

 

I created a SQL and execute it as DATASET. The result are ok... When I execute it on a report, it fail to "group by"...

When debug the SQL on CLI, I can see some diferences between the query on DataSet and Report:

 

DATASET:

[code lang=sql][1466596186] DEBUG: sqlplugind(484):sqlreport_main.c:1546: Execute SQL query: select * from (select coalesce(nullifna("user"), nullifna("unauthuser"), ipstr("srcip")) as user_src from "mgrtmp_t_7f9593835348" where itime >= 1466589600 and itime <= 1466593199 and wanoptapptype = 'web-proxy' group by user_src order by user_src asc ) t limit 1000

 

REPORT:

[code lang=sql][1466596334] DEBUG: sqlplugind(484):sqlreport_main.c:1546: Execute SQL query: drop table if exists hcache."HCACHE_104029";create table hcache."HCACHE_104029" with (autovacuum_enabled=false, toast.autovacuum_enabled=false) as select coalesce(nullifna("user"), nullifna("unauthuser"), ipstr("srcip")) as user_src from "FGTADOM293-tlog-1466591940" where (TRUE) and wanoptapptype = 'web-proxy' group by user_src order by user_src asc limit 10001

 

On the second case, the report output did not group the user_src and I got some duplicate results.

FAZ 5.2.7

 

Regards,

Paulo R, NSE8

Regards, Paulo Raponi

2 REPLIES 2
hzhao_FTNT
Staff
Staff

Hi Paulo,

 

It is a bug in 5.2. This will occur when there is only one column in query. We fixed it in 5.4.

For workaround, please add an additional column in dataset, and keep your current chart setting as is (select only one column in chart).

 

select coalesce(nullifna("user"), nullifna("unauthuser"), ipstr("srcip")) as user_src, count(*) as total_num from $log where $filter and wanoptapptype = 'web-proxy' group by user_src order by user_src asc 

 

Regards,

hz

pcraponi

Hi Hai,

 

It's works. Thanks

Regards, Paulo Raponi