Creating datasets

The following procedure describes how to create datasets in FortiAnalyzer.

Datasets define what data is extracted from the database and represented in a report’s chart. While FortiAnalyzer does provide pre-defined datasets that address the most common queries, you need to understand Structured Query Language, also known as SQL, in order to modify those datasets or create your own.

For additional details, see the FortiAnalyzer Administration Guide and the FortiAnalyzer CLI Reference in the Fortinet Docs Library.

To create a custom dataset:

1. Go to Reports > Report Definitions > Datasets.
2. Click Create New.
3. Configure the dataset.
   

   Name		Enter a name for the dataset.
   Log Type		Select the log type to be used in the dataset. $log is used in the SQL query to represent the log type you select, and it is run against all tables of this type.
   Query		Enter the SQL query used for the dataset. An easy way to build a custom query is to clone and edit a predefined dataset. While entering the SQL query in this field, automatic suggestions display a list of possible commands, table names, log fields, and more to use in your query where applicable. You can also mouse over related areas in the Query field to view the log fields available in the table. For example, you can mouse over from $log to view the log fields in that table.
   Validate		Click to validate the entered SQL query. If any errors are present in the query, the details of the error are displayed below. If there are no errors in the query, the message will display OK.
   Analyze Query		Click to perform a detailed analysis on the SQL query. Analyze Query displays the original SQL query, the transformed SQL query (if applicable), and the SQL validation results. This function also allows users to view the hcache query that is used when a report using this dataset has enabled the auto-cache option for faster report generation. For more information on hcache, see the FortiAnalyzer Administration Guide.
   Format		Click to automatically format the entered SQL query, making it easier to read, update, and detect errors.
   Variables		Click the Add button to add variable, expression, and description information. If added, the expression for the variable will be used when configuring filters for reports that use this dataset. For example, if Variable = User (user) and Expression = coalesce(nullifna(`user`), ipstr(`srcip`)), then the expression will be used when User (user) is selected as the Log Field in a report's filter. See Filtering report output in the FortiAnalyzer Administration Guide.
   Go		Click to test the SQL query before saving the dataset configuration. Click Stop to end a test in progress.
   	Time Period	From the dropdown, select a time period to run the SQL query against. When selecting Custom, enter the start date and time, and the end date and time.
   	Devices	From the dropdown, select devices to run the SQL query against.

4. Click Go.

   The query results are displayed. If the query is not successful, an error message is displayed instead.

5. Click OK.

   The SQL dataset test function (Go) can be used to determine if any errors are present in the SQL format. It should not be used to test returned values as those may be different than the ones used in reports.

To add a dataset to a chart:

1. Go to Reports > Report Definitions > Chart Library, and click Create New or edit an existing chart.
2. From the Dataset dropdown, select your custom dataset.
3. Configure the remaining chart details, and click OK.
   
   The chart based on your custom dataset is now available for use in reports.|
   

To use a chart in reports:

1. Go to Reports > Report Definitions > All Reports.
2. From the Report dropdown, click Create New.

   You can also edit an existing report to add the chart.

3. In the Create Report dialog, configure the options and click OK.

4. Name		Enter a name for the report.
   Create from		Select Blank or Template. If Template is selected, select a template for the report from the Select Template dropdown.
   Save to Folder		From the dropdown, select a folder to save the report in. You can click the Add button to save the report to multiple locations.

5. Go to the Editor tab for the report, and click Insert Chart.
6. From the Chart dropdown, select the chart that you created.
7. Click OK.
   
8. Configure the remaining report settings, and save your changes.
   

For more information, about creating charts and creating reports, see the FortiAnalyzer Administration Guide.