SQL tables

SQL is the database language that FortiAnalyzer uses for logging and reporting. Log data is inserted into the SQL database for log view and report generation. FortiAnalyzer uses a PostgreSQL database.

In an SQL database all information is represented as tables, and each table consists of a set of rows and columns. There are two types of tables:

• User tables, which contain information that is in the database, and
• System tables, which contain the database description.

Once a log table is matured, FortiAnalyzer will add a timestamp to the table number, and then start running hcache queries. When the table matures depends on the platforms. Most tables will be rolled around five million rows. If the table size is too big, it will cause issues such as out of memory. There is one table called “table_ref” to store the log table info.

FortiAnalyzer has master tables and child tables. A child table has "ALLELSE" in its name, and its master table can be found in column "tbl_master" in the table "table_ref".

You can use information from SQL tables to create custom datasets for use in report charts.

For example, below are some of the available tables:

$ADOM_ENDPOINT and $ADOM_EPEU_DEVMAP	FortiAnalyzer will learn UEBA for logs, and try to identify endpoints, endusers, device mappings.
$ADOMTBL_PLHD_AUDIT_HST	Used for security rating data.
devtable_ext	Used for device id mapping in the interface table.
intfinfo	Used for FortiGate interface data from RESTAPI.