SQL wrong result between dataset and report

Question

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

hzhao_FTNT · Answer

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

Sign up

Login with SSO

Login to the community

Login with SSO