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, review the “Top Web Users by Allowed Requests” report to verify your table and column names.
Create a new dataset named “ALL USERS BY USER ACTIVITY”
Log type = Traffic
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 user_src, CountTimeStamps DESC
Create a new Chart named “ALL USERS BY USER ACTIVITY”
- Select dataset = ALL USERS BY USER REQUESTS
- 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 to “Bandwidth (KB/MB/GB)”
- Order by = unchecked
- Show Top (0 for all results) = 0 **Double check this one**
Create a new FortiAnalyzer report:
Create from Blank, named “ALL USERS BY USER ACTIVITY”
Go to Layout tab >
Insert Chart >
- Select the ALL USERS BY USER ACTIVITY 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.
- Information Technology
- Search Engines and Portals
- Web Hosting
- Government and Legal Organizations
- Information and Computer Security
Apply and run the report using the last 10 hours or so. This report is first ordered by User, then by number of requests (indicating the most browsed sites).
Note: If you have more than 10,000 lines in the report, it will cut off. Report across fewer hours if this happens.
Hope you found this blog useful! Kieri Solutions can create other custom reports for you as well. We would be glad to send you a free estimate. Thanks!