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

DataSet Query for Weekday

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

 

1 Solution
hzhao_FTNT
Staff
Staff

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

 

View solution in original post

2 REPLIES 2
hzhao_FTNT
Staff
Staff

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

Labels
Top Kudoed Authors