Help with reporting Top Allowed Categories for Most Active Users

[simonpt]

Hi

Back in FAZ v4.2, there was a built-in report called "Top Allowed Categories for Most Active Users" (see attachment).  You could see at a glance who the top offenders were and where they were spending their time.  It was based on web page requests, whereas FortiView now uses sessions, which surely isn't the same (ie. you could potentially have one session open that is generating millions of requests).  There was also a filter called "Include Web Clicks Only", which removed all the other embedded links to advertising, etc.  This was probably the most valuable report the FAZ produced.  Our managers loved it because they knew who to speak to about their web browsing behaviour.

Fast forward to v5.2, and while it has many good individual reports on users, categories, etc., it doesn't have a single report that combines these together in a powerful way like the above report did.

I've tried to work out how to create my own dataset, but as others have found, it isn't particularly easy, especially for a more complex query like this.

Can someone with good custom dataset skills please help me reproduce this report?

Thanks - Simon

[hzhao_FTNT · Accepted solution]

Hi, Please check below dataset and screenshot for chart setting:

Log-Type: Web filter select user_src, catdesc, sum(requests) as requests from (###(select coalesce(nullifna(`user`), ipstr(`srcip`)) as user_src, catdesc, count(*) as requests from $log-traffic where $filter and logid_to_int(logid) not in (4, 7, 14) and utmevent in ('webfilter', 'banned-word', 'web-content', 'command-block', 'script-filter') and catdesc is not null and utmaction!='blocked' group by user_src, catdesc order by requests desc)### union all ###(select coalesce(nullifna(`user`), ipstr(`srcip`)) as user_src, catdesc, count(*) as requests from $log-webfilter where $filter and (eventtype is null or logver>=52) and catdesc is not null and action!='blocked' group by user_src, catdesc order by requests desc)###) t group by user_src, catdesc order by requests desc

regards,

hz

[hzhao_FTNT · Accepted solution]

Hi, Please check below dataset and screenshot for chart setting:

Log-Type: Web filter select user_src, catdesc, sum(requests) as requests from (###(select coalesce(nullifna(`user`), ipstr(`srcip`)) as user_src, catdesc, count(*) as requests from $log-traffic where $filter and logid_to_int(logid) not in (4, 7, 14) and utmevent in ('webfilter', 'banned-word', 'web-content', 'command-block', 'script-filter') and catdesc is not null and utmaction!='blocked' group by user_src, catdesc order by requests desc)### union all ###(select coalesce(nullifna(`user`), ipstr(`srcip`)) as user_src, catdesc, count(*) as requests from $log-webfilter where $filter and (eventtype is null or logver>=52) and catdesc is not null and action!='blocked' group by user_src, catdesc order by requests desc)###) t group by user_src, catdesc order by requests desc

regards,

hz

[hzhao_FTNT]

bar chart setting is also attached

[simonpt]

Hi hz

Thanks very much for this.  That's exactly what we need.  I don't think I could've ever worked that out myself.

I've run some test reports and am seeing a large number of 'Unknown' category events for many users (see screenshot1).  I've examined the web filter logs, filtered out all the known categories and are left with log entries that have a category description of 'N/A' and seem to represent GETs of .js, .css and .crl files (see screenshot2).

Would it be because the FGT is running FOS v4.3?  Is there a way to filter or recategorise these events so that we don't get so many 'Unknowns'?

Also, in the last line of the dataset SQL query where it says '... t group by ...', is that 't' meant to be there or is it a typo?

Simon

[simonpt]

Screenshot2 attached.

[hzhao_FTNT · Accepted solution]

Hi Simon,

It is good to know you can see the drilldown chart again. You are right, "N/A"  catdesc was sent by FGT 4.3. To filter it out, please replace the 2nd "catdesc is not null" to "nullifna(catdesc) is not null". By the way, the "t" you mentioned is just an alias of the subquery, no specific meaning.

Regards,

Huai Zhao

[simonpt]

Hi hz

Thanks again. That's now looking perfect.

Rgds, Simon