Fortinet Document Library

Version:

Version:

Version:

Version:

Version:

Version:

Version:

Version:

Version:

Version:

Version:

Version:

Version:

Version:

Version:

Version:

Version:


Table of Contents

CLI Reference

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 | remote}

set text-search-index {disable | enable}

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 {FortiCache | FortiGate | FortiMail | FortiSandbox | FortiWeb}

set index-field <Field-Name>

set log-type <Log-Enter>

end

config ts-index-field

edit <category>

set <value> <string>

end

end

Variable

Description

background-rebuild {enable | disable}

Disable or enable rebuilding the SQL database in the background.

database-name <string>

Remote SQL database name. Character limit: 64

Command only available when status is set to remote.

database-type <postgres>

Database type. Command only available when status is set to local or remote.

device-count-high {enable | disable}

You must set to enable if the count of registered devices is 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 in minutes for event logs. Range: 0 to 525600 (minutes). Enter 0 for unlimited

fct-table-partition-time <integer>

Maximum SQL database table partitioning time range, in minutes, for FortiClient logs: 0 to 525600 (minutes), or 0 for unlimited.

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

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

rebuild-event {enable | disable}

Enable/disable a rebuild event during SQL database rebuilding. The following options are available: 

  • disable: Do not rebuild event during SQL database rebuilding.
  • enable: Rebuild event during SQL database rebuilding.

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

The rebuild event starting date and time.

server <string>

Set the database ip or hostname.

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

The date and time that logs will start to be inserted. Command only available when status is set to local or remote.

status {disable | local | remote}

SQL database status. The following options are available: 

  • disable: Disable SQL database.
  • local: Enable local database.
  • remote: Enable remote database.

text-search-index {disable | enable}

Disable or enable the text search index. The following options are available: 

  • disable: Do not create text search index.
  • enable: Create text search index.

traffic-table-partition-time <integer>

Maximum SQL database table partitioning time range for traffic logs. Range: 0 to 525 600 (minutes). Enter 0 for unlimited

utm-table-partition-time <integer>

Maximum SQL database table partitioning time range in minutes for UTM logs. Range: 0 to 525600 (minutes). Enter 0 for unlimited

username <string>

The user name that the Fortinet unit will use to authenticate with the remote database. Character limit: 64

Command only available when status is set to remote.

Variables forconfig custom-indexsubcommand:

case-sensitive {enable | disable}

Enable/disable case sensitivity.

device-type {FortiCache | FortiGate | FortiMail | FortiSandbox | FortiWeb}

Set the device type. The following options are available:  

  • FortiCache: Set device type to FortiCache
  • FortiGate: Set device type to FortiGate.
  • FortiMail: Set device type to FortiMail.
  • FortiSandbox: Set device type to FortiSandbox
  • FortiWeb: Set device type to FortiWeb.

index-field <Field-Name>

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

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

  • FortiCache: N/A
  • FortiGate: app-ctrl, content, dlp, emailfilter, event, netscan, traffic, virus, voip, webfilter
  • FortiMail: emailfilter, event, history, virus
  • FortiSandbox: N/A
  • FortiWeb: attack, event, traffic

Variables forconfig ts-index-fieldsubcommand:

<category>

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

  • FGT-app-ctrl: user, group, srcip, dstip, dstport, service, app, action, status, hostname
  • FGT-attack: severity, srcip, proto, user, attackname
  • FGT-content: from, to, subject, action, srcip, dstip, hostname, status
  • FGT-dlp: user, srcip, service, action, file
  • FGT-emailfilter: user, srcip, from, to, subject
  • FGT-event: subtype, ui, action, msg
  • FGT-traffic: user, srcip, dstip, service, app, utmaction, utmevent
  • FGT-virus: service, srcip, file, virus, user
  • FGT-voip: action, user, src, dst, from, to
  • FGT-webfilter: user, srcip, status, catdesc
  • FGT-netscan: user, dstip, vuln, severity, os
  • 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>

Fields of the text search filter.

<string>

Select one or more field names separated with a comma. The available field names is dependent on the category selected.

Use the show command to display the current configuration if it has been changed from its default value:

show system 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 | remote}

set text-search-index {disable | enable}

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 {FortiCache | FortiGate | FortiMail | FortiSandbox | FortiWeb}

set index-field <Field-Name>

set log-type <Log-Enter>

end

config ts-index-field

edit <category>

set <value> <string>

end

end

Variable

Description

background-rebuild {enable | disable}

Disable or enable rebuilding the SQL database in the background.

database-name <string>

Remote SQL database name. Character limit: 64

Command only available when status is set to remote.

database-type <postgres>

Database type. Command only available when status is set to local or remote.

device-count-high {enable | disable}

You must set to enable if the count of registered devices is 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 in minutes for event logs. Range: 0 to 525600 (minutes). Enter 0 for unlimited

fct-table-partition-time <integer>

Maximum SQL database table partitioning time range, in minutes, for FortiClient logs: 0 to 525600 (minutes), or 0 for unlimited.

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

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

rebuild-event {enable | disable}

Enable/disable a rebuild event during SQL database rebuilding. The following options are available: 

  • disable: Do not rebuild event during SQL database rebuilding.
  • enable: Rebuild event during SQL database rebuilding.

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

The rebuild event starting date and time.

server <string>

Set the database ip or hostname.

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

The date and time that logs will start to be inserted. Command only available when status is set to local or remote.

status {disable | local | remote}

SQL database status. The following options are available: 

  • disable: Disable SQL database.
  • local: Enable local database.
  • remote: Enable remote database.

text-search-index {disable | enable}

Disable or enable the text search index. The following options are available: 

  • disable: Do not create text search index.
  • enable: Create text search index.

traffic-table-partition-time <integer>

Maximum SQL database table partitioning time range for traffic logs. Range: 0 to 525 600 (minutes). Enter 0 for unlimited

utm-table-partition-time <integer>

Maximum SQL database table partitioning time range in minutes for UTM logs. Range: 0 to 525600 (minutes). Enter 0 for unlimited

username <string>

The user name that the Fortinet unit will use to authenticate with the remote database. Character limit: 64

Command only available when status is set to remote.

Variables forconfig custom-indexsubcommand:

case-sensitive {enable | disable}

Enable/disable case sensitivity.

device-type {FortiCache | FortiGate | FortiMail | FortiSandbox | FortiWeb}

Set the device type. The following options are available:  

  • FortiCache: Set device type to FortiCache
  • FortiGate: Set device type to FortiGate.
  • FortiMail: Set device type to FortiMail.
  • FortiSandbox: Set device type to FortiSandbox
  • FortiWeb: Set device type to FortiWeb.

index-field <Field-Name>

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

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

  • FortiCache: N/A
  • FortiGate: app-ctrl, content, dlp, emailfilter, event, netscan, traffic, virus, voip, webfilter
  • FortiMail: emailfilter, event, history, virus
  • FortiSandbox: N/A
  • FortiWeb: attack, event, traffic

Variables forconfig ts-index-fieldsubcommand:

<category>

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

  • FGT-app-ctrl: user, group, srcip, dstip, dstport, service, app, action, status, hostname
  • FGT-attack: severity, srcip, proto, user, attackname
  • FGT-content: from, to, subject, action, srcip, dstip, hostname, status
  • FGT-dlp: user, srcip, service, action, file
  • FGT-emailfilter: user, srcip, from, to, subject
  • FGT-event: subtype, ui, action, msg
  • FGT-traffic: user, srcip, dstip, service, app, utmaction, utmevent
  • FGT-virus: service, srcip, file, virus, user
  • FGT-voip: action, user, src, dst, from, to
  • FGT-webfilter: user, srcip, status, catdesc
  • FGT-netscan: user, dstip, vuln, severity, os
  • 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>

Fields of the text search filter.

<string>

Select one or more field names separated with a comma. The available field names is dependent on the category selected.

Use the show command to display the current configuration if it has been changed from its default value:

show system sql