FortiAnalyzer – FortiOS 6.2.3 – Datasets


Use the Datasets pane to create, edit, and manage your datasets.

Creating datasets

FortiAnalyzer datasets are collections of data from logs for monitored devices. Charts and macros reference datasets. When you generate a report, the datasets populate the charts and macros to provide data for the report.

FortiAnalyzer has many predefined datasets that you can use right away. You can also create your own custom datasets.

To create a new dataset:

  1. If using ADOMs, ensure that you are in the correct ADOM.
  2. Go to Reports > Report Definitions > Datasets, and click Create New.
  3. Provide the required information for the new dataset.
Name                                       Enter a name for the dataset.
Log Type                                 Select a log type from the dropdown list.

l  The following log types are available for FortiGate: Application Control,

Intrusion Prevention, Content Log, Data Leak Prevention, Email Filter,

Event, Traffic, Virus, VoIP, Web Filter, Vulnerability Scan, FortiClient Event, FortiClient Traffic, FortiClient Vulnerability Scan, Web Application Firewall, GTP, DNS, SSH, and Local Event.

l  The following log types are available for FortiMail: Email Filter, Event, History, and Virus.

l  The following log types are available for FortiWeb: Intrusion Prevention, Event, and Traffic.

Query Enter the SQL query used for the dataset. An easy way to build a custom query is to copy and modify a predefined dataset’s query.
Variables                                Click the Add button to add variable, expression, and description information.
Test query with specified devices and time period
Time Period             Use the dropdown list to select a time period. When selecting Custom, enter the start date and time, and the end date and time.
Devices       Select All Devices or Specify to select specific devices to run the SQL query against. Click the Select Device button to add multiple devices to the query.
                     Test                         Click to test the SQL query before saving the dataset configuration.
  1. Click Test.

The query results are displayed. If the query is not successful, an error message appears in the Test Result pane.

  1. Click OK.

Viewing the SQL query of an existing dataset

You can view the SQL query for a dataset, and test the query against specific devices or all devices.

To view the SQL query for an existing dataset:

  1. If using ADOMs, ensure that you are in the correct ADOM.
  2. Go to Reports > Report Definitions > Datasets.
  3. Hover the mouse cursor over the dataset on the dataset list. The SQL query is displayed as a tooltip. You can also open the dataset to view the Query

SQL query functions

In addition to standard SQL queries, the following are some SQL functions specific to FortiAnalyzer. These are based on standard SQL functions.

root_domain(hostname) The root domain of the FQDN. An example of using this function is:

select devid, root_domain(hostname) as website FROM $log WHERE’user’=’USER01′ GROUP BY devid, hostname ORDER BY hostname LIMIT 7

nullifna(expression) This is the inverse operation of coalesce that you can use to filter out n/a values. This function takes an expression as an argument. The actual SQL syntax this is base on is select nullif(nullif(expression, ‘N/A’), ‘n/a’).

In the following example, if the user is n/a, the source IP is returned, otherwise the username is returned.

select coalesce(nullifna(‘user’), nullifna(‘srcip’)) as user_ src, coalesce(nullifna(root_domain(hostname)),’unknown’) as domain FROM $log WHERE dstport=’80’ GROUP BY user_src, domain ORDER BY user_src LIMIT 7

email_domain email_user email_domain returns the text after the @ symbol in an email address. email_user returns the text before the @ symbol in an email address. An example of using this function is:

select ‘from’ as source, email_user(‘from’) as e_user, email_ domain(‘from’) as e_domain FROM $log LIMIT 5 OFFSET 10

from_dtime from_itime from_dtime(bigint) returns the device timestamp without time zone. from_itime(bigint) returns FortiAnalyzer’s timestamp without time zone. An example of using this function is:

select itime, from_itime(itime) as faz_local_time, dtime, from_ dtime(dtime) as dev_local_time FROM $log LIMIT 3

Managing datasets

You can manage datasets by going to Reports > Report Definitions > Datasets. Some options are available as buttons on the toolbar. Some options are available in the right-click menu. Right-click a dataset to display the menu.

Option Description
Create New Creates a new dataset.
Edit Edits the selected dataset. You can edit datasets that you created. You cannot edit predefined datasets.
View Displays the settings for the selected dataset. You cannot edit predefined datasets.
Delete Deletes the selected dataset. You can delete datasets that you create. You cannot delete predefined datasets.
Clone Clones the selected dataset. You can edit cloned datasets.
Validate Validate selected datasets.
Validate All Custom Validates all custom datasets.
Search Lets you search for a dataset name.

Having trouble configuring your Fortinet hardware or have some questions you need answered? Check Out The Fortinet Guru Youtube Channel! Want someone else to deal with it for you? Get some consulting from Fortinet GURU!

This entry was posted in Administration Guides, FortiAnalyzer, Fortinet, FortiOS 6.2 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).

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.