Help with Dataset/Report needed

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

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.

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 

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.