How to create datasets (advanced)
Creating a dataset requires SQL language knowledge. The following is a short high-level summary of how to create a dataset. It assumes that you have SQL language knowledge, and therefore is for advanced users only.
1. Log in to the CLI and use the config report dataset command to start creating the dataset.
You can name the dataset any name; however, it should be a name that describes what information is contained in the dataset. For example, wanopt.traffic.bandwidth.24h dataset name indicates that the dataset contains only WAN Optimization traffic and the amount of bandwidth that has been used for the past 24 hours.
2. Set the various variables within the dataset as you need for your report. Refer to the FortiGate CLI Reference to see the complete list of commands and variables you will need. The following table shows a possible method for customizing a dataset to output a list of the top ten bandwidth consuming applications within an hour.
config report dataset | |
edit appctrl.Count.Bandwidth.Top10.Apps | This calculates an “hourstamp” to indicate the bandwidth per hour. |
set query “select (timestamp-timestamp%3600) as hourstamp” |
(Case WHEN app!=\’N/A\’ and app!=\’\’ then app ELSE service END) as appname | Uses the application name, appname, or if it is undefined, uses service instead. |
as bandwidth from traffic_log where ###timestamp_to_oid(traffic_log)### | This states to retrieve the information from the traffic log file. |
and (appname in (select (CASE WHEN app!=\ ‘N/A\’ and app!=\’\’ then app ELSE service END) as appname from traffic_log where ###timestamp_to_oid(traffic_log)### group by appname order by sum(sent+rcvd) desc limit 10)) group by hourstamp, appname order by hourstamp desc” | From this information, the FortiGate unit selects the application name from within the specific traffic log message and groups these names in order by the top ten, as well as by the “hourstamp” in descending order. |
3. Create a corresponding chart using the config report chart command, making sure to apply the dataset that you just configured to the chart.
The custom dataset is now available for use in a report.
If you want to modify an existing dataset that you’ve created, the process is the same. Enter config report dataset in the CLI followed by the name of the existing dataset.
To see the list of created datasets, enter the following in the CLI:
config report dataset
show