This article describes how to write SQL queries that can be used in a report.
In FortiAnalyzer, under Reports -> Datasets, there is a big variety of predefined queries, which cover most use cases for the data available in the different log types.
The Dataset names generally give some idea about what results to expect, but of course it is possible to review the SQL syntax for more details, and test the output.
The Search box in the Dataset section is filtering not only based on the names, but looks also into the SQL queries.
This allows easy searching for specific log columns, log tables, functions, macros, variables, etc..
Fortinet recommends using primarily these predefined Datasets, mainly because they are:
If a Dataset returns more columns/data than required, or if the columns are not ordered as liked, there is no need to modify the Dataset itself.
It can be still used in a custom chart, which allows removing columns, different formatting, filters, changing the positions and names of the columns, overriding the Dataset’s ORDER BY, setting a Limit, etc..
If additional columns are needed, the best practice is to find a predefined Dataset, as close as possible to your requirement, and clone it.
The SQL query in the cloned Dataset can then be modified, which usually requires less effort and knowledge than creating new one form scratch.
If none of the predefined Datasets is close enough to the needs, review the 'Log View' tables.
Since the reports are based on the logs, exporting a table view using chart builder normally helps.
The resulting query can be then modified if additional columns or logic are needed.
The FortiView charts can also be exported as report chart, but the FortiView queries are usually quite complex and may be hard to understand and modify.
If none of the above options can be used, FortiAnalyzer allows creating custom Datasets from scratch.
This requires good SQL knowledge, as well as understanding of the log schema of the respective logging Device.
It is still a good idea to go through the predefined datasets, in order to understand the FortiAnalyzer specific SQL syntax.
Additional information regarding the FortiAnalyzer SQL syntax is available in the NSE 5 training documentation.
To create new custom dataset, go to Reports -> Datasets and select 'Create New'.
select from_itime(itime)::date as datestamp, sum(rcvdbyte) as traffic_in, sum(sentbyte) as traffic_out, policyid, devidApply the Dataset it in a Chart.
group by datestamp, policyid, devid