I recently spent time building FortiAnalyzer reports to let management see which devices are spending the most time browsing non-work websites. I was really surprised how hard it was to find information on this topic. No default reports on the FortiAnalyzer gave the level of detail I wanted without running the User Detailed Browsing Log over and over for each device and scanning through thousands of logs. Ended up writing custom queries and doing it the hard way. My loss is your gain.

There are a few major caveats that I have to go through with you first:

FortiAnalyzer has no way of telling whether traffic logs are generated by a user or by a background process on the device. For example; if you see a device, let’s call it 192.168.100.28, making connections to a botnet in China, it is good odds that malware is doing the talking, not the user.
Without special agents configured, FortiAnalyzer has no way to tell which USER is logged on to a device. If you see 192.168.100.28 connecting to porn websites at night, you may want to verify who was actually sitting at the keyboard before going on a firing spree.
The “Requests” column really refers to the # of traffic logs generated. In my limited review, it seems like a new connect log is generated about once a minute during active browsing. So I use this to distinguish between a quick connect (for example, to download pictures or advertisements on a linked page) and a long browsing session. The custom reports are set to filter single requests, dramatically reducing the number of pages.
The “Bandwidth” column is exactly as it seems. If nothing else, goofing off on social media or YouTube does hog bandwidth from other legitimate users.
In my custom report, I filtered out categories that seem like normal work web browsing or data transmissions to/from vendors: Reference, Information Technology, Search Engines and Portals, Web Hosting, Business, Government and Legal Organizations, Information and Computer Security. I also filtered out Advertising because otherwise it is about half the report, and normally users don’t choose to view advertising on purpose.

Selfish plug time (sorry!)

I hope this article helps you (don’t worry, the next section has the FortiAnalyzer code you are seeking). If you have tips or feedback, please comment or send me an email so that others can benefit. I am a consultant in the Maryland/DC area in the USA. My specialties are Windows migrations (to 2016 and to Office 365 / Azure), VMware migrations, Netapp and SAN, and high availability / disaster recovery planning. If your business would like help with your complex project, or would like a architectural review to improve your availability, please reach out! More information and contact can be found on the About page. – Amira Armond

How to create the first custom FortiAnalyzer report “ALL USERS BY CATEGORY”:

Note: The code works well on FortiAnalyzer 5.4.3. If you have syntax problems on other versions, review the “Top Web Users by Allowed Requests” dataset to verify your table and column names.

Create a new dataset named “ALL USERS BY CATEGORY”

Log type = Traffic Query = select sum(minutes) as CountTimeStamps, user_src, catdesc, hostname as website, status, sum(bandwidth) as bandwidth from ###(select count(dtime) as minutes, coalesce(nullifna(`user`), nullifna(`unauthuser`), ipstr(`srcip`)) as user_src, catdesc, hostname, cast(utmaction as text) as status, 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 (countweb>0 or ((logver is null or logver<52) and (hostname is not null or utmevent in (‘webfilter’, ‘banned-word’, ‘web-content’, ‘command-block’, ‘script-filter’)))) group by user_src, catdesc, hostname, utmaction)### t group by user_src, catdesc, website, status having sum(minutes) > 1 order by catdesc, CountTimeStamps DESC Apply…

Create a new Chart named “ALL USERS BY CATEGORY”

Select dataset = ALL USERS BY CATEGORY
Resolve hostname = Inherit
Chart type = table

(The columns should auto-populate)

Change counttimestamps to “Requests (minutes”) and width = 5%
Change user_src to “User/Source” and width = 14%
Change catdesc to “Category” and width = 20%
Change website to “Website” and width = 0%
Change bandwidth to “Bandwidth” and width = 6% and change the binding for this field to “Bandwidth (KB/MB/GB”

Order by = unchecked
Show Top (0 for all results) = 0 **Double check this one**

Apply…

Create a new report:

Create from Blank, named “ALL USERS BY CATEGORY” Go to Layout tab > Insert Chart >

Select the ALL USERS BY CATEGORY chart.
Title = Default
Width = 700

Filters = (Click + to add a filter)

Log Field = Category Description (catdesc)
Match Criteria = Not Equal To
Value = type “Advertising” and press Enter. Now add the rest of the categories, pressing enter between each one.
- Advertising
- Reference
- Information Technology
- Search Engines and Portals
- Web Hosting
- Business
- Government and Legal Organizations
- Information and Computer Security

Apply and run the report using the last 10 hours or so. You should get something like the picture at the top of this blog. Note: If you have more than 10,000 lines in the report, it will cut off. Report across fewer hours if this happens.

How to create the second custom report “ALL USERS BY USER ACTIVITY”:

Note: The code works well on FortiAnalyzer 5.4.3. If you have syntax problems on other versions…

Pages: 1 2

17 thoughts on “FortiAnalyzer custom report – All users web browsing by category”

claude chouinard says:

hello,
I run your script but I have an error (ERROR: column “‘webfilter'” does not exist
LINE 1: … ver <52) and (hostname is not null or utmevent in ('webfilt …). Can you help me my version is FortiAnlyzer-1000c v5.4.3 build1187

January 17, 2018 at 7:16 pm

Amira Armond says:
Hi Claude,
Hmmm. Could be syntax (like missing a parenthesis), or the slight difference in version could have removed or renamed the ‘webfilter’ column in the underlying database.
I would try just removing ‘webfilter’, from the query, and seeing how it comes out (or if you get a new error).
The ultimate answer is that you can reverse engineer FortiAnalyzer by looking at existing, working queries such as “Top Web Users by Allowed Requests”. Compare the parts of the queries to each other (such as the (‘webfilter’, syntax and see if it is slightly changed in the working code.
Reply
January 17, 2018 at 7:35 pm
Stephen Santos says:
Check the quotes, copy and paste brings in the single quotes wrong on my setup. Once I corrected the single quotes, all worked fine.
Reply
February 6, 2018 at 11:47 am

Michael says:

Same as Claude I get an error when testing the query. Quotes come in as single quotes (same as what is in the source code above). I’m running version 5.4.4 … can anyone offer any insight?

February 23, 2018 at 2:12 pm

Amira Armond says:
If you are having trouble, my best suggestion is to look at the code for the canned dataset “Top Web Users by Allowed Requests”. It is very similar to this custom report (the custom report adds grouping and some filters so that you don’t have to look at 20,000 individual rows). Use this as your base code (copy into notepad etc) and modify it manually. This should prevent syntax or copy-paste issues.
Reply
March 8, 2018 at 8:20 am

Juan Font says:

Apparently the dataset query must use single quotes ‘ when doing the IN clause:

select sum(minutes) as CountTimeStamps, user_src, catdesc, hostname as website, status, sum(bandwidth) as bandwidth from ###(select count(dtime) as minutes, coalesce(nullifna(`user`), nullifna(`unauthuser`), ipstr(`srcip`)) as user_src, catdesc, hostname, cast(utmaction as text) as status, 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 (countweb>0 or ((logver is null or logver 1 order by catdesc, CountTimeStamps DESC

April 12, 2018 at 4:12 pm

Juan Font says:

My comment got cut of. Sorry about that.
To fix the error, just substitute the opening and closing quotes with a single quote( ‘ for ‘ ). Note that they look almost the same.
Original
in (‘webfilter’, ‘banned-word’, ‘web-content’, ‘command-block’, ‘script-filter’)
Fixed
in (‘webfilter’, ‘banned-word’, ‘web-content’, ‘command-block’, ‘script-filter’)

April 13, 2018 at 10:31 am

Juan Font says:

Ok I see. This website substitutes all single quotes with opening and closing quotes.

April 13, 2018 at 10:33 am

Amira Armond says:
Yep, between browsers and website and cut-and-paste to MSWord vs Notepad vs Wordpad etc, the unicode changes a bit. But I think your comments will lead everyone in the right direction. Thank you! Amira
Reply
April 13, 2018 at 10:38 am

J says:

I got the scrip running writing the quotes, but when I generated the report, but it end up with a pdf with just “No matching log data for this report”

May 28, 2018 at 12:57 pm

Amira Armond says:
Hi J,
Do you get data for other reports? Fortianalyzers default to collecting a very small (a few hours) amount of data. Check my other blog on this topic: https://www.kieri.com/fortianalyzer-disk-quota-problem/
Reply
June 11, 2018 at 2:11 pm

elyas says:

hi
when i add the filters the error message show is :
Duplicate log fields (catdesc) are not allowed in filters

September 15, 2018 at 2:07 am

Trakker says:
Elyas,
You can’t have multiple lines for the Log Field, but you can add multiple Values. Instead of a line for Advertising, a line for Reference, and a line for Information Technology, you’ll have one line for value “Advertising, Reference, Information Technology”. Hover over the ? behind “Value” to see instructions.
Hope that helps!
Reply
January 16, 2019 at 2:41 pm