Need help for a custom report...

[corporatif]

Hi everyone,

We need a custom report for the fortianalyser.

My Customer need a report with pie by users with categories of their weekly internet usage.

Am I enough clear ? I'm not perfectly bilingual….

So all users, internet weekly usage by category in pie format...

Users Drill down details is too much information for them...

[chall_FTNT]

Before talking about the format (e.g., pie chart), I think you have decide the structure first.

You have mentioned: users, categories, bandwidth (internet usage).

Do you want? Top users by bandwidth & then for each of these users, break down their bandwidth by category?

As for format, pie chart only works well for 2 variables but you have talked about 3.  Table format is better for 3 variables.

Once you have figured out the structure, you can either clone & modify existing charts (and possible datasets).  Or use Chart Builder.

[corporatif]

Hi chall_FTNT !

We do not need bandwidth, only time spent by category

 ex : John Snow: Information technology / 10 hours, streaming / 2 hours… etc...

For all users

[chall_FTNT]

Time spent ... in FortiAnalyzer lingo, this is called "Browsing Time".   You can find sample datasets & charts involving "Browsing Time".

To get "all users", just change the Top X value to 0 (unlimited).

[AtiT]

Hi, I am currently working on a dataset to show the most visited domains (top 10 domains) for the users. Would it be great if someone could test it.

SELECT user_src, website, ebtr_value(ebtr_agg_flat(brtime), null, $timespan) AS brtime FROM ( SELECT user_src, website, ebtr_agg_flat(browsetime) AS brtime FROM ###( SELECT COALESCE(NULLIFNA(`user`), NULLIFNA(`unauthuser`), IPSTR(`srcip`)) AS user_src, (CASE SPLIT_PART(ROOT_DOMAIN(`hostname`),':',1) WHEN IPSTR(`dstip`) THEN IPSTR(`dstip`) ELSE SPLIT_PART(ROOT_DOMAIN(`hostname`),':',1) END) AS website, ebtr_agg_flat($browse_time) AS browsetime FROM $log WHERE $filter AND logid_to_int(`logid`) NOT IN (4, 7, 14) AND NULLIFNA(`hostname`) IS NOT NULL AND $browse_time IS NOT NULL AND `countweb`!=0 AND `utmaction`='allow' GROUP BY user_src, website )### t GROUP BY user_src, website ) a GROUP BY user_src, website ORDER BY brtime DESC

The chart settings and sapmle output: