Technical Tip: How to create report to list Top Websites per User

Description

This article describes how to create a User Browsing report to show top 'N' websites per user.

Scope

FortiAnalyzer 7.6.

Solution

1. Go to Reports -> Report Definitions -> Datasets -> Create New -> give a name to the dataset, then select Log Type 'Traffic' and use the following dataset in the Query section.

SELECT

  coalesce(

    nullifna(`user`),

    nullifna(`unauthuser`),

    ipstr(`srcip`),

    'Unknown'

  ) as user_src,

  coalesce(nullifna(root_domain(hostname)), ipstr(`dstip`)) as domain,

  sum(coalesce(sentbyte, 0) + coalesce(rcvdbyte, 0)) as bandwidth,

  sum(coalesce(rcvdbyte, 0)) as traffic_in,

  sum(coalesce(sentbyte, 0)) as traffic_out

FROM

  $log

WHERE

  $filter

  AND (bitAnd(logflag, 1) > 0)

  AND coalesce(nullifna(root_domain(hostname)), ipstr(`dstip`)) is not null

GROUP BY

  user_src,

  domain

HAVING

  bandwidth > 0

ORDER BY

  user_src ASC,

  bandwidth DESC

2. Go to Reports -> Report Definitions -> Chart Library -> Create New -> Give a name and use the newly created dataset from the Dataset option.

• Set the Table Type to Drilldown.
• Under the Show Top option set a number of users required to show in report. Use '0' to include all users.
• Under the Drilldown Top option set the number of top websites to show per user.

For columns, set the following Data Bindings:

• Regular Column: 'user_src'.
• Drilldown Column: 'domain'.
• Aggregated Column: 'bandwidth' and set the format to 'Bandwidth (KB/MB/GB)'.

3. Go to Reports -> Report Definitions -> All Reports -> Report -> Create New.

• Give a name to the report and select Create From Blank, select 'Save to Folder' as well (All Reports by default), and select OK.
• This will open a window to edit the report settings. Set the desired settings and select Apply. For further details, see Reports Settings tab.
• Go to the Editor, insert the newly created chart using the Insert Chart option, and select Apply.

• Run the report from the Generated Reports tab.
• After the report runs successfully, there will be options to download the report in HTML, PDF, XML, CSV, and JSON formats.

Final report output:

Related documents:

• Technical Tip: How to create FortiAnalyzer reports using custom SQL queries
• Technical Tip: Create a custom report using Chart Builder Tool from Log View
• Creating a Custom report from FortiView (Export to Report Chart)
• Technical Tip: How to create a VPN report for users' connection and disconnection time