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
Solved! Go to Solution.
Nominating a forum post submits a request to create a new Knowledge Article based on the forum post topic. Please ensure your nomination includes a solution within the reply.
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
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
Thanks for the quick response, all i need to know is the syntax.
I will work on the filter and test it
Select Forum Responses to become Knowledge Articles!
Select the “Nominate to Knowledge Base” button to recommend a forum post to become a knowledge article.
User | Count |
---|---|
1519 | |
1019 | |
749 | |
443 | |
209 |
The Fortinet Security Fabric brings together the concepts of convergence and consolidation to provide comprehensive cybersecurity protection for all users, devices, and applications and across all network edges.
Copyright 2024 Fortinet, Inc. All Rights Reserved.