fortianalyzer simple select

[marypoppins]

Dear All,

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....

Thank you

[Jean-Philippe_P]

Hello marypoppins!

Thank you for using the Community Forum.

I will seek to get you an answer or help. We will reply to this thread with an update as soon as possible.

Kindest regards,

[marypoppins]

thank you

[Jean-Philippe_P]

Hello marypoppins !

Thank you for your patience! Sorry but our TAC support team haven't the ability to answer about SQL matter.

I invite you to contact the Professional Services for this issue and they will help you.

Kindest regards,

[Debbie_FTNT]

Hey marypoppins,

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

An example:

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 :)