DataSet Query for Weekday

[ks_lim]

Hi,

I am new in fortianalyzer, my company would like to have report in working hours.

How do I modified SQL select query to filter out weekend and lunch hour logs from the below predefined dataset:

Dataset Name: Top-Users-By-Bandwidth select     coalesce(         nullifna(`user`),         nullifna(`unauthuser`),         ipstr(`srcip`)     )as user_src,     sum(         coalesce(sentbyte,0)+ coalesce(rcvdbyte,0)     ) as bandwidth,     sum(         coalesce(rcvdbyte,0)     ) as traffic_in,     sum(         coalesce(sentbyte,0)     ) as traffic_out,     count(*) as sessions from     $log where     $filter     and logid_to_int(logid) not in (4,7,14) group by     user_src having     sum(         coalesce(sentbyte,0)+ coalesce(rcvdbyte,0)     )>0 order by     bandwidth desc

[hzhao_FTNT · Accepted solution]

Please try:

select coalesce( nullifna(`user`), nullifna(`unauthuser`), ipstr(`srcip`) )as user_src, sum( coalesce(sentbyte,0)+ coalesce(rcvdbyte,0) ) as bandwidth, sum( coalesce(rcvdbyte,0) ) as traffic_in, sum( coalesce(sentbyte,0) ) as traffic_out, count(*) as sessions from $log where $filter and logid_to_int(logid) not in (4,7,14) and date_part('dow', from_itime(itime)) in (1,2,3,4,5) and $hour_of_day in ('09:00','10:00','11:00','13:00','14:00','15:00','16:00','17:00') group by user_src having sum( coalesce(sentbyte,0)+ coalesce(rcvdbyte,0) )>0 order by bandwidth desc

[ks_lim]

Thanks for the quick response, all i need to know is the syntax.

I will work on the filter and test it