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

FortiAnalyzer Custom Report for Browsing Usage based on Subnet Filters

Hi , I'm trying to setup a custom Report to show all users browsing activity in a customers network. 
so for example user A - consumed maybe 5gb on whatsapp - 2gb office updates etc... just to break down what their doing on the network at a glance.  and filter based on the subnet their in so for guest users see what's going execs etc... 

based on subnets e.g.: 

VLAN1 - LAN  (10.214.11.0/24)
VLAN2 - CCTV (10.213.11.0/24)
VLAN3 - GUEST (10.212.11.0/24)
VLAN4 - CORP WIFI (10.211.11.0/24)

Essentially i want to be able to just generate a report and specify the subnet when running the report. 
I have tried doing this with a custom dataset but as soon as i specify the Subnet the report generates blank content. 
Running - FAZVM64 KVM - v7.4.6 build2588 (Mature)


 

 

 

SELECT
  user_src,
  hostname AS website,
  SUM(minutes) AS total_time_minutes,
  SUM(bandwidth) AS total_bandwidth
FROM
  (
    SELECT
      count(dtime) AS minutes,
      ipstr(srcip) AS user_src,
      hostname,
      SUM(COALESCE(sentbyte, 0) + COALESCE(rcvdbyte, 0)) AS bandwidth
    FROM
      $log-traffic
    WHERE
      $filter
      AND srcip::inet >= '10.214.11.0'::inet
      AND srcip::inet <= '10.214.11.255'::inet
    GROUP BY
      user_src,
      hostname
  ) t
GROUP BY
  user_src,
  website
ORDER BY
  user_src,
  total_time_minutes DESC

 

 

 



This is a sample SQL query i have tried to come up , but can only get it working when i specify inside the dataset the subnets like this. 
chart_execs.png

The Report shows this which is perfect - but noticing its also capturing some public IPs as sources. so assuming something wrong with my SQL query. 

report_execs.png

If anyone has some pointers on how i can do this effectively - and still have the ability to customize before executing the report choosing the subnet i want to run the report for - i haven't quite understood how i can pass that variable into the dataset. as seen below 
Subnets.png


FortiAnalyzer  

M1kes
M1kes
1 REPLY 1
Stephen_G
Moderator
Moderator

Hello,


Thank you for using the Community Forum. I will seek to get you an answer or help. We will reply to this thread with an update as soon as possible.


Thanks,

Stephen - Fortinet Community Team
Announcements
Check out our Community Chatter Blog! Click here to get involved
Labels
Top Kudoed Authors