FortiAnalyzer Query Traffic log missing data

[luantruong]

Hello,

I create a dataset in Report of FAZ with query as below:

select $flex_timescale(timestamp) as hodex, sum(traffic_out) as traffic_out, sum(traffic_in) as traffic_in
, sum(traffic_out)*8/60/60/1000 as traffic_out_kbps
, sum(traffic_in)*8/60/60/1000 as traffic_in_kbps
, 100000 as maxBW
from ###(
select timestamp, sum(bandwidth) as bandwidth, sum(traffic_out) as traffic_out, sum(traffic_in) as traffic_in
from ###base(
select $flex_timestamp as timestamp, dvid, srcip, dstip, epid, euid, coalesce(nullifna(`user`), nullifna(`unauthuser`), ipstr(`srcip`)) as user_src, service
, count(*) as sessions, sum(coalesce(sentbyte, 0)+coalesce(rcvdbyte, 0)) as bandwidth, sum(coalesce(sentbyte, 0)) as traffic_out, sum(coalesce(rcvdbyte, 0)) as traffic_in
from $log-traffic
where $filter and (logflag&1>0) and (srcintf = 'ILL_Viettel' or dstintf = 'ILL_Viettel') and devname = 'Nuiphao-TN_FG201F'
group by timestamp, dvid, srcip, dstip, epid, euid, user_src, service
--order by timestamp desc/*SkipEND*/
)base### base_query
group by timestamp
)### t
where $filter-drilldown
group by hodex
having sum(traffic_out+traffic_in)>0
order by hodex

But when show data in report, missing some hour data, which I recheck in Log View still show data.

Please help me check query and give your advise for this case.

Thank you so much.

[Debbie_FTNT · Accepted solution]

Hey luantruong,

I assume you modified an existing dataset to provide the bandwidth information?
I noticed a few things:

- you're querying for a few unnecessary details (user/service/source IP/dst IP/...)

-> those columns are not used in the outer queries, only in the inner most, and used only for grouping the resulting logs; only bandwidth and timestamp information are used further

-> it might be worth removing those fields from the query if they're irrelevant

- The fact that the static Maximum bandwdith value falls to 0 indicates that there's no set of values at all for the affected timestamp (essentially an empty row in the results of the query)

-> this would point to one of the filter conditions not being met, so the FortiAnalyzer could not find any logs in that time period, query returned no result in that time

-> Your filter conditions are:

$filter and (logflag&1>0) and (srcintf = 'ILL_Viettel' or dstintf = 'ILL_Viettel') and devname = 'Nuiphao-TN_FG201F'

and

where $filter-drilldown
group by hodex
having sum(traffic_out+traffic_in)>0

The logflag/interface/devname filters are fairly straightforward, but the $filter/$filter-drilldown placeholders reference to the report/chart filter settings you would have in your report; perhaps one of them is not matched and thus causes the row to be essentially empty?

As a simple workaround, you could adjust this:
having sum(traffic_out+traffic_in)>=0
-> that way, you would get a row of results (with 0 bandwidth) even if no bandwidth is calculated due to the other filters; that should prevent the maximum bandwidth line from dipping to 0 as well

[luantruong]

Hi Debbie_FTNT,

Thank you so much for your reply.

After I tried remove unnecessary fields as your advise. I fixed MaxBW line issue down to 0.

Additional: I also try remove all filter at WHERE and HAVING statement, but issue not fix.