Fortinet black logo

CLI Reference

sql

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>

next

end

config custom-skipidx

edit <id>

set device-type <device>

set index-field <string>

set log-type <log type>

next

end

config ts-index-field

edit <category>

set <value> <string>

next

end

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

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>

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

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>

next

end

config custom-skipidx

edit <id>

set device-type <device>

set index-field <string>

set log-type <log type>

next

end

config ts-index-field

edit <category>

set <value> <string>

next

end

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

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>

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

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.