Not Equal Results between Dataset and Report Chart

[AtiT]

Hello,

I have a FAZ-VM running on 5.2.7. I have a problem with the different output when I test the dataset and when I generate the report.

I issued the following commands but the results are the same: # diagnose sql remove hcache All hcache tables will be erased! Do you want to continue? (y/n)y Done.

# diagnose sql remove query-cache Done.

# diagnose sql remove tmp-table Done.

What I get is attached.

Is it a bug or I missed something?

[AtiT]

Hello hzhao_FTNT

Now it is clear for me.

Thank you very much! 

[shaneboy]

Hi, I found this thread and I wanna know if you can help me. I need to do a dataset for a report where show this fields:

app    |   sum(bandwidth) of today   |   average of sum(bandwidth) of the last N days

my query is this one:

[code lang=sql]select t1.app,(sum(coalesce(t1.sentbyte, 0)+coalesce(t1.rcvdbyte, 0)))/$days_num as bandwidth_historico,t2.bandwidth_hoy

from $log t1 inner join

###(select app,sum(coalesce(sentbyte, 0)+coalesce(rcvdbyte, 0)) as bandwidth_hoy

from $log where $filter and (logflag&1>0) and
nullifna(app) is not null and to_timestamp(itime)::date >= current_date

group by app having sum(coalesce(sentbyte, 0)+coalesce(rcvdbyte, 0))>0 order by bandwidth_hoy desc
)### as t2 on t1.app=t2.app
 where $filter and (logflag&1>0) and
nullifna(t1.app) is not null group by t1.app,t2.bandwidth_hoy having sum(coalesce(t1.sentbyte, 0)+coalesce(t1.rcvdbyte, 0))>0

order by bandwidth_hoy desc

So, the first column would be for the app, the second will be the sum of the bandwidth and then it will be divided by the number of days selected in the $filter, and the third will be the bandwidth of the present day. And then we Join that fields with a subquery where it is indicated that it will select today's records exclusively.

And it works on the dataset section. It looks like the right table below. It works, I checked the results of the third column with another dataset that I made to see if the data was the same.

The problem ocurrs when I run the report where the chart that uses this query, it shows the same app more than once in some cases (red oval). And it's not even the same info although is the same query with the same time parameters (blue and green ovals). The right one is the correct one.

And I'm not sure is there's something wrong or that it could improve on my query to show the correct data on the report. Or maybe do I have to do something like the example above? something like:

[code lang=sql]select field1,field2 from ###( select...) ### as t1 inner join ###(select...)### as t2

on t1.app = t2.app

I hope you could help me.

Regards.

[hzhao_FTNT]

Hi shaneboy,

Because of the hcache, do not join $log with other tables.

Try below query:

select app, sum(case when dom=to_char(now(), 'YYYY-MM-DD') then bandwidth else 0 end) as today_bandwidth, cast(sum(bandwidth)/count(distinct dom) as decimal(18,0)) as avg_bandwidth from ###(select app, $DAY_OF_MONTH as dom, sum(coalesce(sentbyte, 0)+coalesce(rcvdbyte, 0)) as bandwidth from $log where $filter and (logflag&1>0) group by app, dom order by bandwidth desc)### t group by app order by avg_bandwidth desc

Regards,

hz

[shaneboy]

Thanks hzhao_FTNT,

Your query really helped me but it seems that's not working properly when the "today_bandwidth" comes. The result is always 0, the "avg_bandwidth" works fine. When I choose "today" as the time period it does work on both columns, but if I choose another time period like "7 days", "last N days" or anyone else. It shows 0 :\

I thought it was because of the condition on the "Case" condition. It compares the function now() casted to char versus $DAY_OF_MONTH but the now() function shows date like this:

2019-11-08

and $DAY_OF_MONTH shows just the number of the day, so it would be comparing something like this "2019-11-08" = 5.

I made a few changes to your query to compare the current date with a field and now it is like this:

[code lang=sql]select app, sum(case when dom>=current_date then bandwidth else 0 end) as today_bandwidth,

cast(sum(bandwidth)/$days_num as decimal(18,0)) as avg_bandwidth

from ###(select app, from_itime(itime)::date as dom, sum(coalesce(sentbyte, 0)+coalesce(rcvdbyte, 0))

as bandwidth from $log where $filter and (logflag&1>0)

group by app, dom order by bandwidth desc)###

t group by app order by avg_bandwidth desc

But it's not working neither on the today_bandwidth, the avg one works fine. Any ideas of why is not working? I'm kinda out of ideas.

Regards.

[hzhao_FTNT · Accepted solution]

Report "last 7 days" doesn't include today's logs. Because $filter will automatically add device and time filter to your query, please make sure you have correctly set time period in report settings. You can use Today/This Week/This Month/This Year or Custom Period.

[shaneboy]

Hi hzhao_FTNT,

It worked fine, thanks a lot!

[gioberta]

Hi, I know it is quite some time that this thread is inactive, however, it's the only one I found on the matter.

Currently I am experiencing this issue where the SQL query on my dataset produces a different results when I hit the "Test" button from the data I get inside the report.

The SQL query is the following:

"

select srcip as source_ip, dstip as dest_ip, srcintf as source_interface, dstintf as dest_interface, proto as protocol, dstport as dest_port, service as service, app as application, appcat as application_cat, sum(coalesce(sentbyte, 0)+coalesce(rcvdbyte, 0)) as bandwidth, sum(coalesce(rcvdbyte, 0)) as traffic_in, sum(coalesce(sentbyte, 0)) as traffic_out, count(distinct sessionid) as sessions, count($DAY_OF_MONTH) as days_active, count(distinct $DAY_OF_MONTH) as days_active_unique,  min($DAY_OF_MONTH) as first_day_seen, max($DAY_OF_MONTH) as last_day_seen from $log where devname='XXXXXX' AND policyid=Y group by source_ip, source_interface, dest_ip, dest_interface, protocol, dest_port, service, application, application_cat order by source_ip DESC

"

The issue is that on the report days_active_unique does not look the same if compared with the results it gives while testing the data. Basically, the "distinct" seems not to work properly on the "days_active_unique" field when constructing the report and I cannot understand why. While testing the dataset the results for that field look fine.

For example, when I test the dataset I get this:

sessionsdays_activedays_active_unique1081104

while on the report I have something like this:

sessionsdays_activedays_active_unique10811037

Anyone does have any idea on why this might happens?

Thank you very much for any input you have,

Giorgio

[GuillermoGN]

Hello,

I think that I haven't understood the rules of using inner queries ###( ..... )###.

This code is all right in "probe mode" when you are creating a sql query dataset in fortianalyzer, but it says:

"No matching log data for this report" when I execute report.

select dstip,        sum(sev_critical + sev_high + sev_medium) attacks from ###(/* t3 */ select dstip, /* this subquery run good in reports */      (case when severity='critical' then 1 else 0 end) as sev_critical,      (case when severity='high' then 1 else 0 end) as sev_high,      (case when severity='medium' then 1 else 0 end) as sev_medium from $log b where $filter       and severity in ('critical','high','medium')           and nullifna(attack) is not null       and dstip in (          select dstip from ( /* begin t2 */             select dstip, sum(num) as num             from ###(                select dstip, count(*) as num from $log where $filter                and severity in ('critical','high','medium')                    and nullifna(attack) is not null                group by dstip                order by num desc             )### t1             group by dstip             order by num desc             limit 25 /* only 25 top destip */          ) t2      ) /* end of dstip in */ )### t3 group by dstip order by attacks desc

Anyone can help me?

Thank you

[GuillermoGN]

Too nested sql levels for avoiding manipulation of initial sql code.

Too complex, no optimal.

Solution:

select dstip, sum(case when severity='critical' then 1 else 0 end) as critical_attacks, sum(case when severity='high' then 1 else 0 end) as high_attacks, sum(case when severity='medium' then 1 else 0 end) as medium_attacks, sum(case when severity in ('critical','high','medium') then 1 else 0 end) as attacks from $log b left join (select dstip as dstip2, count(*) as num from $log where $filter and severity in ('critical','high','medium') and nullifna(attack) is not null group by dstip2 order by num desc limit 25) b2 on b.dstip=b2.dstip2

where $filter and severity in ('critical','high','medium') and nullifna(attack) is not null

group by dstip order by attacks desc