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

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

set index-field <Field-Name>

set log-type <Log-Enter>

end

config ts-index-field

edit <category>

set <value> <string>

end

Variable	Description
background-rebuild {enable \| disable}	Disable or enable rebuilding the SQL database in the background.
database-name <string>	Database name. 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. The following options are available: `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, in minutes, for event logs. 0 to 525600 (minutes), or Enter 0 for unlimited.
fct-table-partition-time <integer>	Maximum SQL database table partitioning time range, in minute, for FortiClient logs. 0 to 525600 (minutes), or Enter 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.
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>	Start date and time <hh:mm yyyy/mm/dd>. 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 creation of a 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>	User name for login remote database.
Variables for `config custom-index`subcommand:
case-sensitive
device-type {FortiCache \| FortiGate \| FortiMail \| FortiManager \| 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. `FortiManager`: Set device type to FortiManager. `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` FortiManager: `N/A` FortiSandbox: `N/A` FortiWeb: `attack`, `event`, `traffic`
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. 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` `FGT-fct-event` `FGT-fct-traffic` `FGT-fct-netscan` `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.

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

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

set index-field <Field-Name>

set log-type <Log-Enter>

end

config ts-index-field

edit <category>

set <value> <string>

end

Variable	Description
background-rebuild {enable \| disable}	Disable or enable rebuilding the SQL database in the background.
database-name <string>	Database name. 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. The following options are available: `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, in minutes, for event logs. 0 to 525600 (minutes), or Enter 0 for unlimited.
fct-table-partition-time <integer>	Maximum SQL database table partitioning time range, in minute, for FortiClient logs. 0 to 525600 (minutes), or Enter 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.
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>	Start date and time <hh:mm yyyy/mm/dd>. 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 creation of a 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>	User name for login remote database.
Variables for `config custom-index`subcommand:
case-sensitive
device-type {FortiCache \| FortiGate \| FortiMail \| FortiManager \| 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. `FortiManager`: Set device type to FortiManager. `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` FortiManager: `N/A` FortiSandbox: `N/A` FortiWeb: `attack`, `event`, `traffic`
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. 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` `FGT-fct-event` `FGT-fct-traffic` `FGT-fct-netscan` `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.

CLI Reference

sql

sql

Syntax

sql

Syntax