I have missing information about creating datasets, so I have a hard struggle with select query. I have a fortigate with vdoms, and I would like to try creating a simple custom dataset (in reports / report definitions / create dataset). In the gui I select the device, time period, log_type selection is intrusion_prevention and the query as follows: select * from attack_log limit 5
The validates shows: ERROR: relation "attack_log" does not exist LINE 1: select * from (select * from attack_log limit 5) t limit 0
In "FortiAnalyzer and FortiGate Version 4.0 MR2 SQL Log Database Query Technical Note" there is a lot of example which uses these table names: Table 8: Log types and table names Log Type | SQL table | name Description
Attack log | attack_log | The attack log records attacks that are detected and prevented by the FortiGate unit.
and a cli example from the doc: config report datatset edit "attack.Top10.last24h" set query "select attack_id, count(*) as totalnum from attack_log where timestamp >= F_TIMESTAMP('now','hour','-23') and attack_id is not null group by attack_id order by totalnum desc limit 10" next
so I can not understand how can I use these table names....
The examples you posted are VERY old, the FortiAnalyzer database (and table names) are very different now.
As a rule of thumb, you can structure a query as follows:
select [...] from $log where $filter and [...] group by [...] order by [...]
-> $log is a placeholder and the dataset will use the log type defined (Traffic for example)
-> $filter is a placeholder for external filter settings (in chart/report), like device, timerange and other filters configured in chart/report
That dataset runs on Event logs (wireless event logs to be specific). Results from testing dataset:
For experimenting with datasets, I would suggest you clone existing ones, or at least look at existing ones to get an understanding of the structure, variables and placeholders you can use. In general, fields you have in raw logs (like srcip, action, policyid, itime, ....) will exist as columns in the corresponding database table; you can get that actual table via $log and setting the dataset to the correct log type. In newer FortiAnalyzer versions, you also have the formatting and validator options when creating a dataset; that should aid in correcting any errors.
Another option you could look into is the Chart Builder tool in FortiAnalyzer (it creates charts and datasets based on your current Log View filters), to have datasets to start modifying.
As Jean-Philippe mentioned, support for custom SQL is very limited (I can only tell you that info because I dabble in my free time), and primarily handled via our professional services.
I hope this helps :)
+++ Divide by Cucumber Error. Please Reinstall Universe and Reboot +++
The Fortinet Security Fabric brings together the concepts of convergence and consolidation to provide comprehensive cybersecurity protection for all users, devices, and applications and across all network edges.