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.
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.
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
FortiAnalyzer
