- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Help with Dataset/Report needed
Hi there,
I need help with a dataset/report on FortiAnalyzer.
What I need:
Detailled Report about Application Category "Video/Audio"
- See which Applications (In appcat=Video/Audio) consumed which bandwith
- Which users connected to which application (in appcat=Audio/Video)
- Top 30 users for every Application with Bandwith usage (in appcat=Video/Audio)
Is there anybody who can help me?
Thanks and best regards
Mattias
Solved! Go to Solution.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Hi,
try the datasets below. All data is generated from the traffic log.
- See which Applications (In appcat=Video/Audio) consumed which bandwith
SELECT `appid`, COALESCE(NULLIFNA(`app`),'Unknown Application') AS app_name, `appcat`, SUM(COALESCE(`sentbyte`,0)+COALESCE(`rcvdbyte`,0)) AS bandwidth FROM $log WHERE $filter AND LOGID_TO_INT(`logid`) NOT IN (4, 7, 14) AND `appcat`='Video/Audio' AND `action` IN ('accept','close') AND `utmaction`='allow' AND NULLIFNA(`app`) IS NOT NULL GROUP BY `appid`, app_name, `appcat` HAVING SUM(COALESCE(`sentbyte`,0)+COALESCE(`rcvdbyte`,0))>0 ORDER BY bandwidth DESC
- Which users connected to which application (in appcat=Audio/Video)
SELECT COALESCE(NULLIFNA(UPPER(`user`)), IPSTR(`srcip`)) AS user_src, `appid`, COALESCE(NULLIFNA(`app`),'Unknown Application') AS app_name, `appcat`, SUM(COALESCE(`sentbyte`,0)+COALESCE(`rcvdbyte`,0)) AS bandwidth FROM $log WHERE $filter AND LOGID_TO_INT(`logid`) NOT IN (4, 7, 14) AND `appcat`='Video/Audio' AND `action` IN ('accept','close') AND `utmaction`='allow' AND NULLIFNA(`app`) IS NOT NULL GROUP BY user_src, `appid`, app_name, `appcat` HAVING SUM(COALESCE(`sentbyte`,0)+COALESCE(`rcvdbyte`,0))>0 ORDER BY user_src ASC, bandwidth DESC
- Top 30 users for every Application with Bandwith usage (in appcat=Video/Audio)
SELECT * FROM ( SELECT RANK() OVER (PARTITION BY app_name ORDER BY bandwidth DESC) AS ranked, app_name, `appcat`, user_src, bandwidth FROM ( SELECT `appid`, COALESCE(NULLIFNA(`app`),'Unknown Application') AS app_name, `appcat`, COALESCE(NULLIFNA(UPPER(`user`)), IPSTR(`srcip`)) AS user_src, SUM(COALESCE(`sentbyte`,0)+COALESCE(`rcvdbyte`,0)) AS bandwidth FROM $log WHERE $filter AND LOGID_TO_INT(`logid`) NOT IN (4, 7, 14) AND `appcat`='Video/Audio' AND `action` IN ('accept','close') AND `utmaction`='allow' AND NULLIFNA(`app`) IS NOT NULL GROUP BY `appid`, app_name, `appcat`, user_src HAVING SUM(COALESCE(`sentbyte`,0)+COALESCE(`rcvdbyte`,0))>0 ORDER BY app_name ASC, bandwidth DESC ) t ) t WHERE ranked<=30
The datasets were tested on FAZ VM 5.0.10 and the FGT is running on 5.2.
If you have FGT on 5.0 probably you will need to set the utmaction`='allow' to 'passthrough' and maybe some other value.
AtiT
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Sorry it is my fault. I just run the dataset I did not created a report.
The problem is wiht the "*" in the first select. I also forgot the "appid" to choose a Icon-Application in the chart.
So the correct dataset should be:
SELECT `appid`, app_name, `appcat`, user_src, bandwidth FROM ( SELECT RANK() OVER (PARTITION BY app_name ORDER BY bandwidth DESC) AS ranked, `appid`, app_name, `appcat`, user_src, bandwidth FROM ( SELECT `appid`, COALESCE(NULLIFNA(`app`),'Unknown Application') AS app_name, `appcat`, COALESCE(NULLIFNA(UPPER(`user`)), IPSTR(`srcip`)) AS user_src, SUM(COALESCE(`sentbyte`,0)+COALESCE(`rcvdbyte`,0)) AS bandwidth FROM $log WHERE $filter AND LOGID_TO_INT(`logid`) NOT IN (4, 7, 14) AND `appcat`='Video/Audio' AND `action` IN ('accept','close') AND `utmaction`='allow' AND NULLIFNA(`app`) IS NOT NULL GROUP BY `appid`, app_name, `appcat`, user_src HAVING SUM(COALESCE(`sentbyte`,0)+COALESCE(`rcvdbyte`,0))>0 ORDER BY app_name ASC, bandwidth DESC ) t ) t WHERE ranked<=30
I also attach the chart settings I tested.
AtiT
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Hi,
I have a new dataset. Only the first 5 application is shown. The "others" is not added - I need to find out how to do it.
DROP TABLE IF EXISTS users_tmp; DROP TABLE IF EXISTS usrappbw_tmp; DROP SEQUENCE IF EXISTS temp_seq; CREATE TEMP SEQUENCE temp_seq; CREATE TEMPORARY TABLE users_tmp AS ( SELECT uid, user_src, bandwidth FROM ( SELECT NEXTVAL('temp_seq') AS uid, user_src, bandwidth FROM ( SELECT COALESCE(NULLIFNA(UPPER(`user`)), IPSTR(`srcip`)) AS user_src, SUM(COALESCE(`sentbyte`,0)+COALESCE(`rcvdbyte`,0)) AS bandwidth FROM $log WHERE $filter AND LOGID_TO_INT(`logid`) NOT IN (4, 7, 14) AND `appcat`='Video/Audio' AND `action` IN ('accept','close') AND `utmaction`='allow' AND NULLIFNA(`app`) IS NOT NULL GROUP BY user_src HAVING SUM(COALESCE(`sentbyte`,0)+COALESCE(`rcvdbyte`,0))>0 ORDER BY bandwidth DESC LIMIT 30 ) t ) s ); CREATE TEMPORARY TABLE usrappbw_tmp AS ( SELECT COALESCE(NULLIFNA(UPPER(`user`)), IPSTR(`srcip`)) AS user_src, `appid`, COALESCE(NULLIFNA(`app`),'Unknown Application') AS app_name, `appcat`, SUM(COALESCE(`sentbyte`,0)+COALESCE(`rcvdbyte`,0)) AS bandwidth FROM $log WHERE $filter AND LOGID_TO_INT(`logid`) NOT IN (4, 7, 14) AND `appcat`='Video/Audio' AND `action` IN ('accept','close') AND `utmaction`='allow' AND NULLIFNA(`app`) IS NOT NULL GROUP BY `appid`, app_name, `appcat`, user_src HAVING SUM(COALESCE(`sentbyte`,0)+COALESCE(`rcvdbyte`,0))>0 ORDER BY user_src ASC, bandwidth DESC ); SELECT ranked, uid, CASE WHEN ranked>1 THEN '' ELSE usr END AS usr, aid, aname, bw FROM ( SELECT RANK() OVER (PARTITION BY uid ORDER BY bw DESC) AS ranked, uid, usr, aid, aname, bw FROM ( SELECT users_tmp.uid AS uid, usrappbw_tmp.user_src AS usr, usrappbw_tmp.appid AS aid, usrappbw_tmp.app_name AS aname, usrappbw_tmp.bandwidth AS bw FROM usrappbw_tmp, users_tmp WHERE usrappbw_tmp.user_src=users_tmp.user_src GROUP BY uid, usr, aid, aname, bw ORDER BY uid ASC, bw DESC ) t ) s WHERE ranked<=5
It sould look like in the attached picture.
AtiT
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Hi guys,
For this kind of chart, I do suggest to write a simple query like:
SELECT COALESCE(NULLIFNA(UPPER(`user`)), IPSTR(`srcip`)) AS user_src, app AS app_name, SUM(COALESCE(`sentbyte`,0)+COALESCE(`rcvdbyte`,0)) AS bandwidth FROM $log WHERE $filter AND LOGID_TO_INT(`logid`) NOT IN (4, 7, 14) AND `appcat`='Video/Audio' AND `action` IN ('accept','close') AND `utmaction`='allow' AND NULLIFNA(`app`) IS NOT NULL GROUP BY user_src, app_name HAVING SUM(COALESCE(`sentbyte`,0)+COALESCE(`rcvdbyte`,0))>0 ORDER BY bandwidth DESC
Then create a drilldown table chart base on this query, please check attached screen shot for chart setting (base on 5.2.2/5.2.3)
Regards,
hz
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Hi,
I added the Others to the dataset. If someone could test it it will be great.
DROP TABLE IF EXISTS users_tmp; DROP TABLE IF EXISTS usrappbw_tmp; DROP SEQUENCE IF EXISTS temp_seq; CREATE TEMP SEQUENCE temp_seq; CREATE TEMPORARY TABLE users_tmp AS ( SELECT uid, user_src, bandwidth FROM ( SELECT NEXTVAL('temp_seq') AS uid, user_src, bandwidth FROM ( SELECT COALESCE(NULLIFNA(UPPER(`user`)), IPSTR(`srcip`)) AS user_src, SUM(COALESCE(`sentbyte`,0)+COALESCE(`rcvdbyte`,0)) AS bandwidth FROM $log WHERE $filter AND LOGID_TO_INT(`logid`) NOT IN (4, 7, 14) AND `appcat`='Video/Audio' AND `action` IN ('accept','close') AND `utmaction`='allow' AND NULLIFNA(`app`) IS NOT NULL GROUP BY user_src HAVING SUM(COALESCE(`sentbyte`,0)+COALESCE(`rcvdbyte`,0))>0 ORDER BY bandwidth DESC LIMIT 30 ) t ) s ); CREATE TEMPORARY TABLE usrappbw_tmp AS ( SELECT COALESCE(NULLIFNA(UPPER(`user`)), IPSTR(`srcip`)) AS user_src, `appid`, COALESCE(NULLIFNA(`app`),'Unknown Application') AS app_name, `appcat`, SUM(COALESCE(`sentbyte`,0)+COALESCE(`rcvdbyte`,0)) AS bandwidth FROM $log WHERE $filter AND LOGID_TO_INT(`logid`) NOT IN (4, 7, 14) AND `appcat`='Video/Audio' AND `action` IN ('accept','close') AND `utmaction`='allow' AND NULLIFNA(`app`) IS NOT NULL GROUP BY `appid`, app_name, `appcat`, user_src HAVING SUM(COALESCE(`sentbyte`,0)+COALESCE(`rcvdbyte`,0))>0 ORDER BY user_src ASC, bandwidth DESC ); SELECT ranked, uid, usr, aid, aname, SUM(bw) AS bw FROM ( SELECT CASE WHEN ranked>5 THEN 6 ELSE ranked END AS ranked, uid, CASE WHEN ranked>1 THEN '' ELSE usr END AS usr, CASE WHEN ranked>5 THEN 0 ELSE aid END AS aid, CASE WHEN ranked>5 THEN 'Others' ELSE aname END AS aname, bw FROM ( SELECT RANK() OVER (PARTITION BY uid ORDER BY bw DESC) AS ranked, uid, usr, aid, aname, bw FROM ( SELECT users_tmp.uid AS uid, usrappbw_tmp.user_src AS usr, usrappbw_tmp.appid AS aid, usrappbw_tmp.app_name AS aname, usrappbw_tmp.bandwidth AS bw FROM usrappbw_tmp, users_tmp WHERE usrappbw_tmp.user_src=users_tmp.user_src GROUP BY uid, usr, aid, aname, bw ORDER BY uid ASC, bw DESC ) t ) s GROUP BY ranked, uid, usr, aid, aname, bw ORDER BY uid ASC, ranked ASC ) u GROUP BY ranked, uid, usr, aid, aname ORDER BY uid ASC, ranked ASC
AtiT
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Hi,
try the datasets below. All data is generated from the traffic log.
- See which Applications (In appcat=Video/Audio) consumed which bandwith
SELECT `appid`, COALESCE(NULLIFNA(`app`),'Unknown Application') AS app_name, `appcat`, SUM(COALESCE(`sentbyte`,0)+COALESCE(`rcvdbyte`,0)) AS bandwidth FROM $log WHERE $filter AND LOGID_TO_INT(`logid`) NOT IN (4, 7, 14) AND `appcat`='Video/Audio' AND `action` IN ('accept','close') AND `utmaction`='allow' AND NULLIFNA(`app`) IS NOT NULL GROUP BY `appid`, app_name, `appcat` HAVING SUM(COALESCE(`sentbyte`,0)+COALESCE(`rcvdbyte`,0))>0 ORDER BY bandwidth DESC
- Which users connected to which application (in appcat=Audio/Video)
SELECT COALESCE(NULLIFNA(UPPER(`user`)), IPSTR(`srcip`)) AS user_src, `appid`, COALESCE(NULLIFNA(`app`),'Unknown Application') AS app_name, `appcat`, SUM(COALESCE(`sentbyte`,0)+COALESCE(`rcvdbyte`,0)) AS bandwidth FROM $log WHERE $filter AND LOGID_TO_INT(`logid`) NOT IN (4, 7, 14) AND `appcat`='Video/Audio' AND `action` IN ('accept','close') AND `utmaction`='allow' AND NULLIFNA(`app`) IS NOT NULL GROUP BY user_src, `appid`, app_name, `appcat` HAVING SUM(COALESCE(`sentbyte`,0)+COALESCE(`rcvdbyte`,0))>0 ORDER BY user_src ASC, bandwidth DESC
- Top 30 users for every Application with Bandwith usage (in appcat=Video/Audio)
SELECT * FROM ( SELECT RANK() OVER (PARTITION BY app_name ORDER BY bandwidth DESC) AS ranked, app_name, `appcat`, user_src, bandwidth FROM ( SELECT `appid`, COALESCE(NULLIFNA(`app`),'Unknown Application') AS app_name, `appcat`, COALESCE(NULLIFNA(UPPER(`user`)), IPSTR(`srcip`)) AS user_src, SUM(COALESCE(`sentbyte`,0)+COALESCE(`rcvdbyte`,0)) AS bandwidth FROM $log WHERE $filter AND LOGID_TO_INT(`logid`) NOT IN (4, 7, 14) AND `appcat`='Video/Audio' AND `action` IN ('accept','close') AND `utmaction`='allow' AND NULLIFNA(`app`) IS NOT NULL GROUP BY `appid`, app_name, `appcat`, user_src HAVING SUM(COALESCE(`sentbyte`,0)+COALESCE(`rcvdbyte`,0))>0 ORDER BY app_name ASC, bandwidth DESC ) t ) t WHERE ranked<=30
The datasets were tested on FAZ VM 5.0.10 and the FGT is running on 5.2.
If you have FGT on 5.0 probably you will need to set the utmaction`='allow' to 'passthrough' and maybe some other value.
AtiT
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Hi there,
lots of thanks! :) Got almost everything working with your queries.
Except for the Top30 query. If I create the dataset and test the query, it is working fine and correct results come back. If I then create a chart and put that chart to a report, the report says: "No machting log data for this report"
The chart is configured like you can see in the report. I don't get what I did wrong?
Thanks and best regards
Mattias
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Sorry it is my fault. I just run the dataset I did not created a report.
The problem is wiht the "*" in the first select. I also forgot the "appid" to choose a Icon-Application in the chart.
So the correct dataset should be:
SELECT `appid`, app_name, `appcat`, user_src, bandwidth FROM ( SELECT RANK() OVER (PARTITION BY app_name ORDER BY bandwidth DESC) AS ranked, `appid`, app_name, `appcat`, user_src, bandwidth FROM ( SELECT `appid`, COALESCE(NULLIFNA(`app`),'Unknown Application') AS app_name, `appcat`, COALESCE(NULLIFNA(UPPER(`user`)), IPSTR(`srcip`)) AS user_src, SUM(COALESCE(`sentbyte`,0)+COALESCE(`rcvdbyte`,0)) AS bandwidth FROM $log WHERE $filter AND LOGID_TO_INT(`logid`) NOT IN (4, 7, 14) AND `appcat`='Video/Audio' AND `action` IN ('accept','close') AND `utmaction`='allow' AND NULLIFNA(`app`) IS NOT NULL GROUP BY `appid`, app_name, `appcat`, user_src HAVING SUM(COALESCE(`sentbyte`,0)+COALESCE(`rcvdbyte`,0))>0 ORDER BY app_name ASC, bandwidth DESC ) t ) t WHERE ranked<=30
I also attach the chart settings I tested.
AtiT
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
OMG another mistake.
In the chart the column 3 (User) should be text not bar - but it worked for me :)
AtiT
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Hi AtiT,
you're the man! Thanks a lot!
Unfortunately the query works not as expected. If I test the query, the output looks like attached. There is no sorting by bandwidth or user even if I change the query to : "ORDER BY user_src ASC, bandwidth DESC"
It would be great if the output/report could be something like this.
Top 30 users for appcat=Video/Audio
Output :
user1 total traffic in Video/Audio 5GB
youtube 2GB
vimeo 1GB
Media.Player 0,5GB
YouTube_Video.Embedded 0,4GB
BlaBla_Video 0,1GB
Others 1GB ("Others" should be displayed after the 5 highest consuming applications. So the report won't be too unreadable)
user2 total traffic in Video/Audio 4GB
youtube 1,2GB
vimeo 0,8GB
Media.Player 0,5GB
YouTube_Video.Embedded 0,4GB
BlaBla_Video 0,1GB
Others 1GB ("Others" should be displayed after the 5 highest consuming applications. So the report won't be too unreadable)
Is this possible and would you mind helping me?
Again, thanks a lot!
Mattias
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Hi,
I have a new dataset. Only the first 5 application is shown. The "others" is not added - I need to find out how to do it.
DROP TABLE IF EXISTS users_tmp; DROP TABLE IF EXISTS usrappbw_tmp; DROP SEQUENCE IF EXISTS temp_seq; CREATE TEMP SEQUENCE temp_seq; CREATE TEMPORARY TABLE users_tmp AS ( SELECT uid, user_src, bandwidth FROM ( SELECT NEXTVAL('temp_seq') AS uid, user_src, bandwidth FROM ( SELECT COALESCE(NULLIFNA(UPPER(`user`)), IPSTR(`srcip`)) AS user_src, SUM(COALESCE(`sentbyte`,0)+COALESCE(`rcvdbyte`,0)) AS bandwidth FROM $log WHERE $filter AND LOGID_TO_INT(`logid`) NOT IN (4, 7, 14) AND `appcat`='Video/Audio' AND `action` IN ('accept','close') AND `utmaction`='allow' AND NULLIFNA(`app`) IS NOT NULL GROUP BY user_src HAVING SUM(COALESCE(`sentbyte`,0)+COALESCE(`rcvdbyte`,0))>0 ORDER BY bandwidth DESC LIMIT 30 ) t ) s ); CREATE TEMPORARY TABLE usrappbw_tmp AS ( SELECT COALESCE(NULLIFNA(UPPER(`user`)), IPSTR(`srcip`)) AS user_src, `appid`, COALESCE(NULLIFNA(`app`),'Unknown Application') AS app_name, `appcat`, SUM(COALESCE(`sentbyte`,0)+COALESCE(`rcvdbyte`,0)) AS bandwidth FROM $log WHERE $filter AND LOGID_TO_INT(`logid`) NOT IN (4, 7, 14) AND `appcat`='Video/Audio' AND `action` IN ('accept','close') AND `utmaction`='allow' AND NULLIFNA(`app`) IS NOT NULL GROUP BY `appid`, app_name, `appcat`, user_src HAVING SUM(COALESCE(`sentbyte`,0)+COALESCE(`rcvdbyte`,0))>0 ORDER BY user_src ASC, bandwidth DESC ); SELECT ranked, uid, CASE WHEN ranked>1 THEN '' ELSE usr END AS usr, aid, aname, bw FROM ( SELECT RANK() OVER (PARTITION BY uid ORDER BY bw DESC) AS ranked, uid, usr, aid, aname, bw FROM ( SELECT users_tmp.uid AS uid, usrappbw_tmp.user_src AS usr, usrappbw_tmp.appid AS aid, usrappbw_tmp.app_name AS aname, usrappbw_tmp.bandwidth AS bw FROM usrappbw_tmp, users_tmp WHERE usrappbw_tmp.user_src=users_tmp.user_src GROUP BY uid, usr, aid, aname, bw ORDER BY uid ASC, bw DESC ) t ) s WHERE ranked<=5
It sould look like in the attached picture.
AtiT
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Hi guys,
For this kind of chart, I do suggest to write a simple query like:
SELECT COALESCE(NULLIFNA(UPPER(`user`)), IPSTR(`srcip`)) AS user_src, app AS app_name, SUM(COALESCE(`sentbyte`,0)+COALESCE(`rcvdbyte`,0)) AS bandwidth FROM $log WHERE $filter AND LOGID_TO_INT(`logid`) NOT IN (4, 7, 14) AND `appcat`='Video/Audio' AND `action` IN ('accept','close') AND `utmaction`='allow' AND NULLIFNA(`app`) IS NOT NULL GROUP BY user_src, app_name HAVING SUM(COALESCE(`sentbyte`,0)+COALESCE(`rcvdbyte`,0))>0 ORDER BY bandwidth DESC
Then create a drilldown table chart base on this query, please check attached screen shot for chart setting (base on 5.2.2/5.2.3)
Regards,
hz
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Hi,
I added the Others to the dataset. If someone could test it it will be great.
DROP TABLE IF EXISTS users_tmp; DROP TABLE IF EXISTS usrappbw_tmp; DROP SEQUENCE IF EXISTS temp_seq; CREATE TEMP SEQUENCE temp_seq; CREATE TEMPORARY TABLE users_tmp AS ( SELECT uid, user_src, bandwidth FROM ( SELECT NEXTVAL('temp_seq') AS uid, user_src, bandwidth FROM ( SELECT COALESCE(NULLIFNA(UPPER(`user`)), IPSTR(`srcip`)) AS user_src, SUM(COALESCE(`sentbyte`,0)+COALESCE(`rcvdbyte`,0)) AS bandwidth FROM $log WHERE $filter AND LOGID_TO_INT(`logid`) NOT IN (4, 7, 14) AND `appcat`='Video/Audio' AND `action` IN ('accept','close') AND `utmaction`='allow' AND NULLIFNA(`app`) IS NOT NULL GROUP BY user_src HAVING SUM(COALESCE(`sentbyte`,0)+COALESCE(`rcvdbyte`,0))>0 ORDER BY bandwidth DESC LIMIT 30 ) t ) s ); CREATE TEMPORARY TABLE usrappbw_tmp AS ( SELECT COALESCE(NULLIFNA(UPPER(`user`)), IPSTR(`srcip`)) AS user_src, `appid`, COALESCE(NULLIFNA(`app`),'Unknown Application') AS app_name, `appcat`, SUM(COALESCE(`sentbyte`,0)+COALESCE(`rcvdbyte`,0)) AS bandwidth FROM $log WHERE $filter AND LOGID_TO_INT(`logid`) NOT IN (4, 7, 14) AND `appcat`='Video/Audio' AND `action` IN ('accept','close') AND `utmaction`='allow' AND NULLIFNA(`app`) IS NOT NULL GROUP BY `appid`, app_name, `appcat`, user_src HAVING SUM(COALESCE(`sentbyte`,0)+COALESCE(`rcvdbyte`,0))>0 ORDER BY user_src ASC, bandwidth DESC ); SELECT ranked, uid, usr, aid, aname, SUM(bw) AS bw FROM ( SELECT CASE WHEN ranked>5 THEN 6 ELSE ranked END AS ranked, uid, CASE WHEN ranked>1 THEN '' ELSE usr END AS usr, CASE WHEN ranked>5 THEN 0 ELSE aid END AS aid, CASE WHEN ranked>5 THEN 'Others' ELSE aname END AS aname, bw FROM ( SELECT RANK() OVER (PARTITION BY uid ORDER BY bw DESC) AS ranked, uid, usr, aid, aname, bw FROM ( SELECT users_tmp.uid AS uid, usrappbw_tmp.user_src AS usr, usrappbw_tmp.appid AS aid, usrappbw_tmp.app_name AS aname, usrappbw_tmp.bandwidth AS bw FROM usrappbw_tmp, users_tmp WHERE usrappbw_tmp.user_src=users_tmp.user_src GROUP BY uid, usr, aid, aname, bw ORDER BY uid ASC, bw DESC ) t ) s GROUP BY ranked, uid, usr, aid, aname, bw ORDER BY uid ASC, ranked ASC ) u GROUP BY ranked, uid, usr, aid, aname ORDER BY uid ASC, ranked ASC
AtiT
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Thank you guys for your help! :)
I will test both queries in a couple of minutes.