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

[Mattias]

I did exactly what hzhao_FTNT described and it works like a charm!

Lots of thanks to both of you guys.

Cheers,

Mattias

[danilo_corain]

When i run this query....

ERROR:  column "utmaction" does not exist