Skip to main content
ks_lim
ks_lim
New Member
December 11, 2017
Solved

DataSet Query for Weekday

  • December 11, 2017
  • 1 reply
  • 4098 views

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

 

    Best answer by hzhao_FTNT

    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

     

    1 reply

    hzhao_FTNT
    Staff
    hzhao_FTNTAnswer
    Staff
    December 11, 2017

    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
    ks_limAuthor
    New Member
    December 13, 2017

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

    I will work on the filter and test it

    Terms & ConditionsAccessibility statement