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

FortiAnalyzer Query Traffic log missing data

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

 

luantruong_1-1676519493372.png

 

 

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

luantruong_0-1676519404553.png

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

Thank you so much.

 

1 Solution
Debbie_FTNT
Staff
Staff

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

+++ Divide by Cucumber Error. Please Reinstall Universe and Reboot +++

View solution in original post

2 REPLIES 2
Debbie_FTNT
Staff
Staff

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

+++ Divide by Cucumber Error. Please Reinstall Universe and Reboot +++
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.

 

Top Kudoed Authors