How-to: Create a YouTube report for the FortiAnalyzer

One of the new features of FortiOS 5.2 was the introduction of Cloud Application logging which allows you to track web application traffic like Youtube videos, email address logins and files uploaded/downloaded via Dropbox.

This quick how-to guide goes through creating a small chart that will only show us the YouTube videos that have been watched and the users that watched them.

This config is done a FortiAnalyzer running 5.2.2.

Before we even begin with the FortiAnalyzer, we need to ensure that 'Deep Inspection of Cloud Applications' has been enabled under the Application Sensor. This is found under Security Profiles > Application Control > Deep Inspection of Cloud Applications and highlighted below:

The steps required to create the report are as follows:

1. Create dataset

2. Create chart

3. Create report

1. Create dataset:

To create a dataset goto the 'Reports' tab on your FortiAnalyzer then browse to Advanced > Dataset. From here click on 'Create New' and enter a name, select 'Application Control' for the 'Log Type'. 

Enter the following in the 'Query' section:

select app, appid, filename, `user`, sum(filesize) as filesize 
from $log 
where $filter and filesize is not null 
and clouduser is not null 
and filename is not null 
and app = 'YouTube_Video.Play' 
group by cloudaction, app, appid, `user`, filename 
order by user asc

Once this is done you can click on the 'Test' button to make sure it's working correctly then click 'Ok' to save. Your final dataset should look something like this:

2. Create chart:

Goto Reports > Chart Library and create a new chart (don't use the wizard to create the chart).

Enter a name, select the dataset you created in step 1 and change the 'Only Show First' value to the amount of interfaces you want to show on the chart (in my example I'm showing 50).

Next go through each column and change the header and the display values to the following:

Column 1:
Header: User
Data Binding: user
Display: Icon-User

Column 2:
Header: App
Data Binding: appid
Display: Icon-Application
Merge Next: 1 columns
Merge Header: Video Name

Column 3:
Header: Video Name
Data Binding: filename
Display: Text

Column 4:
Header: Filesize
DataBinding: filesize
Display: Bandwidth (KB/MB/GB)

Click 'Ok' to save. Your end chart should look like this:

3. Create report:

Right-click on the Report section and select 'Create New'

From here give your report a name and save.

Now click on 'Layout' to configure the chart to be run in the report.

Once here click on the 'FortiAnalyzer Chart' icon that's circled below.

On the Chart Properties screen select the Chart name that we created earlier (in this example youtube-videos-by-user) and give the chart a title like 'YouTube Videos'. Click OK to save.

Your final layout should look like this:

Click 'Save' icon in the top left corner to save your changes then go back to 'View Report'. Once here click on 'Run Now' to run the report. Once the report is finished click it to open and view!