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
Let me add this for context :
running below query in the dataset: you can see an output of the query correctly showing results here
SELECT
user_src,
srcip,
hostname AS website,
SUM(minutes) AS total_time_minutes,
SUM(bandwidth) AS total_bandwidth
FROM
(
SELECT
count(dtime) AS minutes,
COALESCE(
nullifna(`user`),
nullifna(`unauthuser`)
) AS user_src,
ipstr(`srcip`) AS srcip,
hostname,
sum(COALESCE(sentbyte, 0) + COALESCE(rcvdbyte, 0)) AS bandwidth
FROM
$log-traffic
WHERE
$filter
AND hostname IS NOT NULL
AND logid_to_int(logid) NOT IN (4, 7, 14)
AND (
countweb > 0
OR (
(
logver IS NULL
OR logver < 52
)
AND (
hostname IS NOT NULL
OR utmevent IN (
'webfilter',
'banned-word',
'web-content',
'command-block',
'script-filter'
)
)
)
)
GROUP BY
user_src,
srcip,
hostname
) t
GROUP BY
user_src,
srcip,
website
HAVING
SUM(bandwidth) > 20
ORDER BY
user_src,
total_time_minutes DESC
if i select the subnet in the report settings before running - it generates blank reports.
However if i run without specifying any subnet it returns full results - but its so large we want to only do it for specific subnets of users.
FROM
$log-traffic
WHERE
$filter
$Filter - i would assume would contain some variables parsed into the final query when we run the report. so my assumption is that maybe the way that's being interpreted is invalid or something wrong with the syntax maybe - if there was a way we could view what the final preview query running is - that could lead us into a direction to find what's going wrong?
Example 1: Report generated with subnet specified: returns empty report
Example 2: Report generated without specifying any subnet filter. works fine.
