Hi,
I' m not sure if there is any relevant dataset for you - I didn' t find one, but you can create some. It depends whether you want data directly from traffic log (destionation IP) or from the webfilter log (hostname).
For hostname you can use something like this:
SELECT COALESCE(NULLIFNA(`user`),`srcip`) AS user_src,`hostname`, SUM(COALESCE(`sentbyte`,0)+COALESCE(`rcvdbyte`,0)) AS bandwidth
FROM $log
WHERE $filter
AND `type`=' webfilter'
AND `status`=' passthrough'
AND NULLIFNA(`hostname`) IS NOT NULL
GROUP BY user_src, `hostname`
HAVING SUM(COALESCE(`sentbyte`,0)+COALESCE(`rcvdbyte`,0))>0
ORDER BY bandwidth DESC
For destination IP:
SELECT COALESCE(NULLIFNA(`user`),`srcip`) AS user_src,`dstip`, SUM(COALESCE(`sentbyte`,0)+COALESCE(`rcvdbyte`,0)) AS bandwidth
FROM $log
WHERE $filter
AND `type`=' traffic'
AND NULLIFNA(`dstip`) IS NOT NULL
GROUP BY user_src, `dstip`
HAVING SUM(COALESCE(`sentbyte`,0)+COALESCE(`rcvdbyte`,0))>0
ORDER BY bandwidth DESC
After that just set a user filter in the report settings. In chart you can enable the user_src filed to check if the report is working correctly for the defined user and for the final report just delete it from the chart (not dataset).
Some results are attached - see the image.
Hi Ebenezer,
I' m a little bit confused or not understand what do you mean with: " I only get one entry per user whereas in ranked I can get 5 hostnames per user."
Can you send some dataset/chart what you are using?
I like the raw format because I can get more columns, in ranked format only 3.
But I also use ranked format see the attached image below.
In the dataset I set for the bandwidh bandwith/1024 for kB and bandwidh/1048576 for MB (1024*1024) etc.
And in the chart I write the column title: Bandwidth [MB].
I know that it is not the best solution but I didn' t figure out how to do it automaticaly in ranked format - is it possible?
My dataset:
SELECT `dstcountry`, SUM(COALESCE(`sentbyte`,0)+COALESCE(`rcvdbyte`,0))/1048576 AS bandwidth
FROM $log
WHERE `status`=' accept'
GROUP BY `dstcountry`
HAVING SUM(COALESCE(`sentbyte`,0)+COALESCE(`rcvdbyte`,0))>0
ORDER BY bandwidth DESC
