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 rebuild-event {enable | disable}

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

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

rebuild-event {enable | disable}

Enable/disable a rebuild event during SQL database rebuilding (default = enable).

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

The rebuild event starting date and time (default = 00:00 2000/01/01).

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-siem	(null)
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 rebuild-event {enable | disable}

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

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

rebuild-event {enable | disable}

Enable/disable a rebuild event during SQL database rebuilding (default = enable).

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

The rebuild event starting date and time (default = 00:00 2000/01/01).

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-siem	(null)
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.

CLI Reference

sql

sql

Syntax

sql

Syntax