Custom FortiAnalyzer Report To See Blocked URLs for a Category

Ian on Fortinet’s forums asked a good question. You can see the question below:

Hi

I want to create a report that allows me to select a category and then to list all the activity for users where the URLS match that category. For example a report that lists user name, source IP, date time, url for the “Explicit Violence” category.

I have had a look at the datasets but my SQL coding is not to good 🙁

Has anyone tried this or any tips on what I need to do.

Regards

Ian

This is an awesome question and would be an incredible report to have right? Well, CrisP replied with an incredible solution to this issue.

Hello Ian,
I hate unanswered questions on a forum like this…
So here you have my two pennies on this.

Create a new dataset using
==============================
select
from_dtime(dtime) as timestamp, user_src, catdesc, hostname as website,
action as status, sum(bandwidth) as bandwidth
from
###(
select coalesce(nullifna(`user`), ipstr(`srcip`)) as user_src,
dtime, catdesc, hostname, utmaction as action,
sum(coalesce(sentbyte, 0)+coalesce(rcvdbyte, 0)) as bandwidth
from $log-traffic
where $filter and
hostname is not null and
logid_to_int(logid) not in (4, 7, 14) and ((logver>=52 and countweb>0)
or
((logver is null) and utmevent in (‘webfilter’, ‘banned-word’, ‘web-content’,
‘command-block’, ‘script-filter’)))
group by user_src, dtime, catdesc, hostname, utmaction
order by dtime desc
)### t
group by user_src, dtime, catdesc, website, status
order by dtime desc
===================================
(slightly modified from default dataset called “web-Detailed-Website-Browsing-Log”)
then build a new chart using the dataset, and a report using that chart.

Use a filter specifying the categories you want to detail in the Advanced settings of the report.

Now, if you really need the URLs, the story becomes quite ugly. You could see the post called “URL field” in this forum.
Best regards and good luck

CategBrowsingDetail-AdvSettings

CategBrowsingDetail

This entry was posted in FortiAnalyzer, Questions, Tips and Tricks and tagged on by .

About Mike

Michael Pruett, CISSP has a wide range of cyber-security and network engineering expertise. The plethora of vendors that resell hardware but have zero engineering knowledge resulting in the wrong hardware or configuration being deployed is a major pet peeve of Michael's. This site was started in an effort to spread information while providing the option of quality consulting services at a much lower price than Fortinet Professional Services. Owns PacketLlama.Com (Fortinet Hardware Sales) and Office Of The CISO, LLC (Cybersecurity consulting firm).

2 thoughts on “Custom FortiAnalyzer Report To See Blocked URLs for a Category

    1. Mike Post author

      Are you the administrator of the FortiGate and you intend to allow videos? Or are you trying to bypass your network admins security settings?

      Reply

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.