Hello,
I created customize report in FAZ (Firmware Version v7.2.1-build1215 220809 (GA))
But if I change a little in query, example I change condition at WHERE statement
where from_itime(timestamp) >= CURRENT_DATE - INTERVAL '14 days' and from_itime(timestamp) <= CURRENT_DATE - INTERVAL '8 days'
-> where from_itime(timestamp) between CURRENT_DATE - INTERVAL '14 days' and CURRENT_DATE - INTERVAL '8 days'
or reverse again
Table data in report appear number
But if I not change query, report automatic run at next week by schedule, table data in report disappear number (only 2nd and 3rd rows disappear number)
Please refer query as below:
select 'This week' as Types
, round(avg(bandwidth)*8/60/60/1000/1000, 2) as bandwidth_Mbps
, round(avg(traffic_out)*8/60/60/1000/1000,2) as traffic_out_Mbps
, round(avg(traffic_in)*8/60/60/1000/1000, 2) as traffic_in_Mbps
, case when round(avg(bandwidth)*8/60/60/1000/1000, 2) <= 100 then 'The bandwidth of Internet Leased Line is almost under control'
else 'Need to check why bandwidth increase' end as Notification
from (
select $flex_timescale(timestamp) as hodex, sum(traffic_out) as traffic_out, sum(traffic_in) as traffic_in, sum(bandwidth) as bandwidth
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 = 'Vlan55' or dstintf = 'Vlan55') and devname = 'Nuiphao_FG201F'
group by timestamp, dvid, srcip, dstip, epid, euid, user_src, service
--order by timestamp desc/*SkipEND*/
)base### base_query
where from_itime(timestamp) >= CURRENT_DATE - INTERVAL '7 days'
group by timestamp
)### t
where $filter-drilldown
group by hodex
having sum(traffic_out+traffic_in)>0
) x
UNION all
select 'Last week' as Types
, round(avg(bandwidth)*8/60/60/1000/1000, 2) as bandwidth_Mbps
, round(avg(traffic_out)*8/60/60/1000/1000,2) as traffic_out_Mbps
, round(avg(traffic_in)*8/60/60/1000/1000, 2) as traffic_in_Mbps
, null as Notification
from (
select $flex_timescale(timestamp) as hodex, sum(traffic_out) as traffic_out, sum(traffic_in) as traffic_in, sum(bandwidth) as bandwidth
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 = 'Vlan55' or dstintf = 'Vlan55') and devname = 'Nuiphao_FG201F'
group by timestamp, dvid, srcip, dstip, epid, euid, user_src, service
--order by timestamp desc/*SkipEND*/
)base### base_query
where from_itime(timestamp) >= CURRENT_DATE - INTERVAL '14 days' and from_itime(timestamp) <= CURRENT_DATE - INTERVAL '8 days'
group by timestamp
)### t
where $filter-drilldown
group by hodex
having sum(traffic_out+traffic_in)>0
) x
UNION all
select 'Change %' as Types
, round((a.bandwidth_Mbps - b.bandwidth_Mbps) / b.bandwidth_Mbps * 100, 1) as bandwidth_Mbps
, round((a.traffic_out_Mbps - b.traffic_out_Mbps) / b.traffic_out_Mbps * 100, 1) as traffic_out_Mbps
, round((a.traffic_in_Mbps - b.traffic_in_Mbps) / b.traffic_in_Mbps * 100, 1) as traffic_in_Mbps
, null as Notification
from (
select 'This week' as Types
, round(avg(bandwidth)*8/60/60/1000/1000, 2) as bandwidth_Mbps
, round(avg(traffic_out)*8/60/60/1000/1000,2) as traffic_out_Mbps
, round(avg(traffic_in)*8/60/60/1000/1000, 2) as traffic_in_Mbps
, case when round(avg(bandwidth)*8/60/60/1000/1000, 2) <= 100 then 'The bandwidth of Internet Leased Line is almost under control'
else 'Need to check why bandwidth increase' end as Notification
from (
select $flex_timescale(timestamp) as hodex, sum(traffic_out) as traffic_out, sum(traffic_in) as traffic_in, sum(bandwidth) as bandwidth
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 = 'Vlan55' or dstintf = 'Vlan55') and devname = 'Nuiphao_FG201F'
group by timestamp, dvid, srcip, dstip, epid, euid, user_src, service
--order by timestamp desc/*SkipEND*/
)base### base_query
where from_itime(timestamp) >= CURRENT_DATE - INTERVAL '7 days'
group by timestamp
)### t
where $filter-drilldown
group by hodex
having sum(traffic_out+traffic_in)>0
) x
) a
join (
select 'Last week' as Types
, round(avg(bandwidth)*8/60/60/1000/1000, 2) as bandwidth_Mbps
, round(avg(traffic_out)*8/60/60/1000/1000,2) as traffic_out_Mbps
, round(avg(traffic_in)*8/60/60/1000/1000, 2) as traffic_in_Mbps
, null as Notification
from (
select $flex_timescale(timestamp) as hodex, sum(traffic_out) as traffic_out, sum(traffic_in) as traffic_in, sum(bandwidth) as bandwidth
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 = 'Vlan55' or dstintf = 'Vlan55') and devname = 'Nuiphao_FG201F'
group by timestamp, dvid, srcip, dstip, epid, euid, user_src, service
--order by timestamp desc/*SkipEND*/
)base### base_query
where from_itime(timestamp) >= CURRENT_DATE - INTERVAL '14 days' and from_itime(timestamp) <= CURRENT_DATE - INTERVAL '8 days'
group by timestamp
)### t
where $filter-drilldown
group by hodex
having sum(traffic_out+traffic_in)>0
) x
) b on 1=1
Please check and give me your advise.
Thank you so much
1 Solution
