Support Forum
The Forums are a place to find answers on a range of Fortinet products from peers and product experts.
simonpt
New Contributor III

Help with reporting Top Allowed Categories for Most Active Users

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

2 Solutions
hzhao_FTNT
Staff
Staff

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

 

 

View solution in original post

hzhao_FTNT

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

View solution in original post

6 REPLIES 6
hzhao_FTNT
Staff
Staff

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
New Contributor III

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
New Contributor III

Screenshot2 attached.

hzhao_FTNT

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
New Contributor III

Hi hz

 

Thanks again. That's now looking perfect.

 

Rgds, Simon

Announcements

Select Forum Responses to become Knowledge Articles!

Select the “Nominate to Knowledge Base” button to recommend a forum post to become a knowledge article.

Labels
Top Kudoed Authors