sql

Configure Structured Query Language (SQL) settings.

Syntax

config system sql

set background-rebuild {enable | disable}

set compress-table-min-age <integer>

set database-type <postgres>

set device-count-high {enable | disable}

set event-table-partition-time <integer>

set fct-table-partition-time <integer>

set prompt-sql-upgrade {enable | disable}

set start-time <hh>:<mm> <yyyy>/<mm>/<dd>

set status {disable | local}

set text-search-index {enable | disable}

set traffic-table-partition-time <integer>

set utm-table-partition-time <integer>

config custom-index

edit <id>

set device-type <device>

set index-field <string>

set log-type <log type>

end

config custom-skipidx

edit <id>

set device-type <device>

set index-field <string>

set log-type <log type>

end

config ts-index-field

edit <category>

set <value> <string>

end

Variable

Description

background-rebuild {enable | disable}

Disable/enable rebuilding the SQL database in the background (default = enable).

compress-table-min-age <integer>

Minimum age in days for SQL tables to be compressed (0 - 10000, default = 7).

Note: 0-day allows you to compress SQL tables with less than one-day of age.

database-type <postgres>

Database type (default = postgres).

device-count-high {enable | disable}

Enable/disable a high device count (default = disable).

You must set to enable if the count of registered devices is greater than 8000:

disable: Set to disable if device count is less than 8000.
enable: Set to enable if device count is equal to or greater than 8000.

Enabling or disabling this command will result in an SQL database rebuild. The time required to rebuild the database is dependent on the size of the database. Please plan a maintenance window to complete the database rebuild. This operation will also result in a device reboot.

event-table-partition-time <integer>

Maximum SQL database table partitioning time range for event logs, in minutes (3 - 1440, 0 = unlimited, default = 0).

fct-table-partition-time <integer>

Maximum SQL database table partitioning time range for FortiClient logs, in minutes (6 - 1440, 0 = unlimited, default = 360).

prompt-sql-upgrade {enable | disable}

Prompt to convert log database into SQL database at start time on GUI (default = enable).

start-time <hh>:<mm> <yyyy>/<mm>/<dd>

The date and time that logs will start to be inserted.

status {disable | local}

SQL database status:

disable: Disable SQL database.
local: Enable local database (default).

text-search-index {enable | disable}

Enable/disable the creation of a text search index (default = disable).

traffic-table-partition-time <integer>

Maximum SQL database table partitioning time range for traffic logs (1 - 1440, 0 = unlimited, default = 0).

utm-table-partition-time <integer>

Maximum SQL database table partitioning time range in minutes for UTM logs (1 - 1440, 0 = unlimited, default = 0).

Variables for config custom-index subcommand:

device-type <device type>

Set the device type.

index-field <string>

Enter a valid field name. Select one of the available field names. The available options for index-field is dependent on the device-type entry.

log-type <log type>

Enter the log type. The available options for log-type is dependent on the device-type entry.

Variables for config custom-skipidx subcommand:

List of aditional SQL skip index fields.

device-type <device type>

Set the device type.

index-field <string>

Enter a valid field name. Select one of the available field names. The available options depend on the device-type.

log-type <log type>

Enter the log type. The available options depend on the device-type.

Variables for config ts-index-field subcommand:

Category of the text search index fields. The following is the list of categories and their default fields.

Category	Value
FGT-app-ctrl	user,group,srcip,dstip,dstport,service,app,action,hostname
FGT-attack	severity,srcip,dstip,action,user,attack
FGT-content	from,to,subject,action,srcip,dstip,hostname,status
FGT-dlp	user,srcip,service,action,filename
FGT-emailfilter	user,srcip,from,to,subject
FGT-event	subtype,ui,action,msg
FGT-traffic	user,srcip,dstip,service,app,utmaction
FGT-virus	service,srcip,dstip,action,filename,virus,user
FGT-voip	action,user,src,dst,from,to
FGT-webfilter	user,srcip,dstip,service,action,catdesc,hostname
FGT-netscan	user,dstip,vuln,severity,os
FGT-fct-event	(null)
FGT-fct-traffic	(null)
FGT-fct-netscan	(null)
FGT-waf	user,srcip,dstip,service,action
FGT-gtp	msisdn,from,to,status
FGT-dns	(null)
FGT-ssh	login,srcip,dstip,direction,action
FGT-ssl	srcip,dstip,eventtype,service,action,reason
FGT-file-filter	srcip,dstip,service,proto,group,eventtype,filtertype,direction,filetype,matchfiletype,action
FGT-protocol	srcip,dstip,service,proto,action
FGT-security	srcip,dstip,service,proto
FML-emailfilter	client_name,dst_ip,from,to,subject
FML-event	subtype,msg
FML-history	classifier,disposition,from,to,client_name,direction,domain,virus
FML-virus	src,msg,from,to
FWB-attack	http_host,http_url,src,dst,msg,action
FWB-event	ui,action,msg
FWB-traffic	src,dst,service,http_method,msg

value <string>

Fields of the text search filter. Enter one or more field names separated with a comma.

sql

Configure Structured Query Language (SQL) settings.

Syntax

config system sql

set background-rebuild {enable | disable}

set compress-table-min-age <integer>

set database-type <postgres>

set device-count-high {enable | disable}

set event-table-partition-time <integer>

set fct-table-partition-time <integer>

set prompt-sql-upgrade {enable | disable}

set start-time <hh>:<mm> <yyyy>/<mm>/<dd>

set status {disable | local}

set text-search-index {enable | disable}

set traffic-table-partition-time <integer>

set utm-table-partition-time <integer>

config custom-index

edit <id>

set device-type <device>

set index-field <string>

set log-type <log type>

end

config custom-skipidx

edit <id>

set device-type <device>

set index-field <string>

set log-type <log type>

end

config ts-index-field

edit <category>

set <value> <string>

end

Variable

Description

background-rebuild {enable | disable}

Disable/enable rebuilding the SQL database in the background (default = enable).

compress-table-min-age <integer>

Minimum age in days for SQL tables to be compressed (0 - 10000, default = 7).

Note: 0-day allows you to compress SQL tables with less than one-day of age.

database-type <postgres>

Database type (default = postgres).

device-count-high {enable | disable}

Enable/disable a high device count (default = disable).

You must set to enable if the count of registered devices is greater than 8000:

disable: Set to disable if device count is less than 8000.
enable: Set to enable if device count is equal to or greater than 8000.

event-table-partition-time <integer>

Maximum SQL database table partitioning time range for event logs, in minutes (3 - 1440, 0 = unlimited, default = 0).

fct-table-partition-time <integer>

Maximum SQL database table partitioning time range for FortiClient logs, in minutes (6 - 1440, 0 = unlimited, default = 360).

prompt-sql-upgrade {enable | disable}

Prompt to convert log database into SQL database at start time on GUI (default = enable).

start-time <hh>:<mm> <yyyy>/<mm>/<dd>

The date and time that logs will start to be inserted.

status {disable | local}

SQL database status:

disable: Disable SQL database.
local: Enable local database (default).

text-search-index {enable | disable}

Enable/disable the creation of a text search index (default = disable).

traffic-table-partition-time <integer>

Maximum SQL database table partitioning time range for traffic logs (1 - 1440, 0 = unlimited, default = 0).

utm-table-partition-time <integer>

Maximum SQL database table partitioning time range in minutes for UTM logs (1 - 1440, 0 = unlimited, default = 0).

Variables for config custom-index subcommand:

device-type <device type>

Set the device type.

index-field <string>

Enter a valid field name. Select one of the available field names. The available options for index-field is dependent on the device-type entry.

log-type <log type>

Enter the log type. The available options for log-type is dependent on the device-type entry.

Variables for config custom-skipidx subcommand:

List of aditional SQL skip index fields.

device-type <device type>

Set the device type.

index-field <string>

Enter a valid field name. Select one of the available field names. The available options depend on the device-type.

log-type <log type>

Enter the log type. The available options depend on the device-type.

Variables for config ts-index-field subcommand:

Category of the text search index fields. The following is the list of categories and their default fields.

Category	Value
FGT-app-ctrl	user,group,srcip,dstip,dstport,service,app,action,hostname
FGT-attack	severity,srcip,dstip,action,user,attack
FGT-content	from,to,subject,action,srcip,dstip,hostname,status
FGT-dlp	user,srcip,service,action,filename
FGT-emailfilter	user,srcip,from,to,subject
FGT-event	subtype,ui,action,msg
FGT-traffic	user,srcip,dstip,service,app,utmaction
FGT-virus	service,srcip,dstip,action,filename,virus,user
FGT-voip	action,user,src,dst,from,to
FGT-webfilter	user,srcip,dstip,service,action,catdesc,hostname
FGT-netscan	user,dstip,vuln,severity,os
FGT-fct-event	(null)
FGT-fct-traffic	(null)
FGT-fct-netscan	(null)
FGT-waf	user,srcip,dstip,service,action
FGT-gtp	msisdn,from,to,status
FGT-dns	(null)
FGT-ssh	login,srcip,dstip,direction,action
FGT-ssl	srcip,dstip,eventtype,service,action,reason
FGT-file-filter	srcip,dstip,service,proto,group,eventtype,filtertype,direction,filetype,matchfiletype,action
FGT-protocol	srcip,dstip,service,proto,action
FGT-security	srcip,dstip,service,proto
FML-emailfilter	client_name,dst_ip,from,to,subject
FML-event	subtype,msg
FML-history	classifier,disposition,from,to,client_name,direction,domain,virus
FML-virus	src,msg,from,to
FWB-attack	http_host,http_url,src,dst,msg,action
FWB-event	ui,action,msg
FWB-traffic	src,dst,service,http_method,msg

value <string>

Fields of the text search filter. Enter one or more field names separated with a comma.

Secure Networking

Hybrid Mesh Firewall

NOC Management

LAN

WAN

Unified SASE

Single Vendor SASE

Cloud Network Security

Secure Endpoint Connectivity

Web Application / API Protection

Security Operations

Security Operations Automation

Identity

Early Detection & Prevention

Secure Networking

Hybrid Mesh Firewall

NOC Management

LAN

WAN

Communication & Surveillance

Unified SASE

Single Vendor SASE

Secure Endpoint Connectivity

Cloud Network Security

Cloud-Native Security

Web Application / API Protection

Security Operations

Security Operations Automation

Endpoint

Data Protection

Identity

Email

Early Detection & Prevention

Expert Services

Edge Firewall

Orchestration & management

SD Branch

Application Delivery

Single Vendor SASE

Secure Endpoint Connectivity

Secure Private Access

Thin Edge

Identity

Application Gateway

Enterprise Asset Management

Endpoint Agent

Agentless Security Posture

Identity

Wireless

Switching

Identity

Privilege Acccess Management

Next Generation Firewall

Orchestration & management

Expert Services

All

All

Account Management

SAAS Management

SAAS Application Security

Managed Services

Platform as a service (PAAS)

Other SAAS Services

4D Pillars

Cloud

Popular Solutions

CLI Reference

sql

sql

Syntax

sql

sql

Syntax