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
Solved! Go to Solution.
Nominating a forum post submits a request to create a new Knowledge Article based on the forum post topic. Please ensure your nomination includes a solution within the reply.
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
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
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
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
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
Hi hz
Thanks again. That's now looking perfect.
Rgds, Simon
Select Forum Responses to become Knowledge Articles!
Select the “Nominate to Knowledge Base” button to recommend a forum post to become a knowledge article.
User | Count |
---|---|
1705 | |
1093 | |
752 | |
446 | |
230 |
The Fortinet Security Fabric brings together the concepts of convergence and consolidation to provide comprehensive cybersecurity protection for all users, devices, and applications and across all network edges.
Copyright 2024 Fortinet, Inc. All Rights Reserved.