Help with Dataset/Report needed

[Mattias]

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

[AtiT · Accepted solution]

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 · Accepted solution]

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 · Accepted solution]

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.

[hzhao_FTNT · Accepted solution]

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

[AtiT · Accepted solution]

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.

[Mattias]

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

[AtiT · Accepted solution]

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]

OMG another mistake.

In the chart the column 3 (User) should be text not bar - but it worked for me :)

[Mattias]

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 

[AtiT · Accepted solution]

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.

[hzhao_FTNT · Accepted solution]

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

[AtiT · Accepted solution]

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

[Mattias]

Thank you guys for your help! :)

I will test both queries in a couple of minutes.