Fortinet black logo

CLI Reference

sql

sql

Configure Structured Query Language (SQL) settings.

Syntax

config system sql

set background-rebuild {enable | disable}

set database-name <string>

set database-type <postgres>

set device-count-high {enable | disable}

set event-table-partition-time <integer>

set fct-table-partition-time <integer>

set logtype {none | app-ctrl | attack | content | dlp | emailfilter | event | generic | history | traffic | virus | voip | webfilter | netscan}

set password <passwd>

set prompt-sql-upgrade {enable | disable}

set rebuild-event {enable | disable}

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

set server <string>

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>

set username <string>

config custom-index

edit <id>

set case-sensitive {enable | disable}

set device-type <device>

set index-field <Field-Name>

set log-type <Log-Enter>

next

end

config custom-skipidx

edit <id>

set device-type <device>

set index-field <Field-Name>

set log-type <Log-Enter>

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

database-name <string>

Remote SQL database name (character limit = 64).

Command only available when status is set to remote.

database-type <postgres>

Database type (default = postgres).

This command only available when status is set to local or remote.

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 (0 - 525600, 0 = unlimited, default = 0).

fct-table-partition-time <integer>

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

logtype {none | app-ctrl | attack | content | dlp | emailfilter | event | generic | history | traffic | virus | voip | webfilter | netscan}

Log type.

This command only available when status is set to local or remote.

password <passwd>

The password that the Fortinet unit will use to authenticate with the remote database.

This command only available when status is set to remote.

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

server <string>

Set the database ip or hostname.

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

Start date and time <hh:mm yyyy/mm/dd>. Command only available when status is set to local or remote.

status {disable | local}

SQL database status:

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

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 (0 - 525600, 0 = unlimited, default = 0).

utm-table-partition-time <integer>

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

username <string>

The user name that the unit will use to authenticate with the remote database (character limit = 64).

config custom-index subcommand:

List of SQL index fields.

case-sensitive {enable | disable}

Enable/disable case sensitivity.

device-type <device>

Set the device type (default = FortiGate).

index-field <Field-Name>

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

log-type <Log-Enter>

Enter the log type (default = traffic). The available options depend on the device-type.

config custom-skipidx subcommand:

List of aditional SQL skip index fields.

device-type <device>

Set the device type (default = FortiGate).

index-field <Field-Name>

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

log-type <Log-Enter>

Enter the log type (default = traffic). The available options depend on the device-type.

config ts-index-field subcommand:

List of SQL text search index fields.

<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
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 database-name <string>

set database-type <postgres>

set device-count-high {enable | disable}

set event-table-partition-time <integer>

set fct-table-partition-time <integer>

set logtype {none | app-ctrl | attack | content | dlp | emailfilter | event | generic | history | traffic | virus | voip | webfilter | netscan}

set password <passwd>

set prompt-sql-upgrade {enable | disable}

set rebuild-event {enable | disable}

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

set server <string>

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>

set username <string>

config custom-index

edit <id>

set case-sensitive {enable | disable}

set device-type <device>

set index-field <Field-Name>

set log-type <Log-Enter>

next

end

config custom-skipidx

edit <id>

set device-type <device>

set index-field <Field-Name>

set log-type <Log-Enter>

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

database-name <string>

Remote SQL database name (character limit = 64).

Command only available when status is set to remote.

database-type <postgres>

Database type (default = postgres).

This command only available when status is set to local or remote.

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 (0 - 525600, 0 = unlimited, default = 0).

fct-table-partition-time <integer>

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

logtype {none | app-ctrl | attack | content | dlp | emailfilter | event | generic | history | traffic | virus | voip | webfilter | netscan}

Log type.

This command only available when status is set to local or remote.

password <passwd>

The password that the Fortinet unit will use to authenticate with the remote database.

This command only available when status is set to remote.

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

server <string>

Set the database ip or hostname.

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

Start date and time <hh:mm yyyy/mm/dd>. Command only available when status is set to local or remote.

status {disable | local}

SQL database status:

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

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 (0 - 525600, 0 = unlimited, default = 0).

utm-table-partition-time <integer>

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

username <string>

The user name that the unit will use to authenticate with the remote database (character limit = 64).

config custom-index subcommand:

List of SQL index fields.

case-sensitive {enable | disable}

Enable/disable case sensitivity.

device-type <device>

Set the device type (default = FortiGate).

index-field <Field-Name>

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

log-type <Log-Enter>

Enter the log type (default = traffic). The available options depend on the device-type.

config custom-skipidx subcommand:

List of aditional SQL skip index fields.

device-type <device>

Set the device type (default = FortiGate).

index-field <Field-Name>

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

log-type <Log-Enter>

Enter the log type (default = traffic). The available options depend on the device-type.

config ts-index-field subcommand:

List of SQL text search index fields.

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