sql

Use this command to diagnose the SQL database.

sql config

Use this command to show, set, or reset the SQL database configuration.

Syntax

diagnose sql config auto-cache-delay [set <seconds>| reset]

diagnose sql config debug-filter [set | test] <daemon> <string>

diagnose sql config deferred-index-timespan [set <value>]

diagnose sql config hcache-agg-step [reset | set <integer>]

diagnose sql config hcache-auto-rebuild-status [reset | set <integer>]

diagnose sql config hcache-auto-rebuild-task-priority [reset | set <integer>]

diagnose sql config hcache-base-trim-interval [reset | set <integer>]

diagnose sql config hcache-max-base-row [reset | set <integer>]

diagnose sql config hcache-max-fv-row [reset | set <integer>]

diagnose sql config hcache-max-fv-row-per-timescale [reset | set <integer>]

diagnose sql config hcache-max-high-accu-row [reset | set <integer>]

diagnose sql config hcache-max-rpt-row [reset | set <integer>]

diagnose sql config sampling-max-row [reset | set <integer>]

diagnose sql config sampling-status [reset | set <integer>]

diagnose sql config sampling-type [reset | set <integer>]

Variable	Description
auto-cache-delay [set <seconds>\| reset]	Show, set, or reset the auto-cache delay, in seconds (default = 300).
debug-filter {set \| test} <daemon> <string>	Show sqlplugind and sqlreportd debug filter. Enter `sqlplugind`, `sqlreportd` or `both` as the `<daemon>`. Enter the filter string.
deferred-index-timespan [set <value>]	View or set the time span for the deferred index (default = 10000).
hcache-agg-step [reset \| set <integer>]	Show, set, or reset the hcache aggregation step (default = 10).
hcache-auto-rebuild-status [reset \| set <integer>]	Show, set, or reset the status of hcache auto rebuild task (0 - 1, default = 1). `0` = disable `1` = enable
hcache-auto-rebuild-task-priority [reset \| set <integer>]	Show, set, or reset the priority of hcache auto rebuild task (0 - 2, default = 1). `0` = low `1` = medium `2` = high
hcache-base-trim-interval [reset \| set <integer>]	Show, set, or reset the hcache base trim interval (3600 - 2147483647, default = 172800).
hcache-max-base-row [reset \| set <integer>]	Show, set, or reset the max row number for base cache (1000 - 1500000, default = 1000000).
hcache-max-fv-row [reset \| set <integer>]	Show, set, or reset max row number for FortiView hcache (1000 - 400000, default = 50000).
hcache-max-fv-row-per-timescale [reset \| set <integer>]	Show, set, or reset max row number per timescale for FortiView hcache (0 - 40000, default = 0).
hcache-max-high-accu-row [reset \| set <integer>]	Show, set, or reset max row number for high-accuracy hcache (1000 - 1000000, default = 400000).
hcache-max-rpt-row [reset \| set <integer>]	Show, set, or reset max row number for report hcache (1000 - 400000, default = 18000).
sampling-max-row [reset \| set <integer>]	Show, set, or reset max row number for sampling (1000 - 10000000, default = 1000000).
sampling-status [reset \| set <integer>]	Show, set, or reset the sampling status. Enter 0 for disabling and 1 for enabling the sample status (0 - 1, default = 1).
sampling-type [reset \| set <integer>]	Show, set, or reset the type of sampling (0 - 1, default = 0).

sql debug

Use this command to show or update the SQL debug statuses.

Syntax

diagnose sql debug hcache-agg dbgoff

diagnose sql debug hcache-agg dbgon

diagnose sql debug hcache-agg delete

diagnose sql debug hcache-agg show [<filter>][<NUM>]

diagnose sql debug hcache-agg upload {ftp | sftp} <host> <dir> <user name> <password>

diagnose sql debug imexport dbgoff

diagnose sql debug imexport dbgon

diagnose sql debug imexport delete

diagnose sql debug imexport show [<filter>] [<NUM>]

diagnose sql debug imexport upload {ftp | sftp} <host> <dir> <user name> <password>

diagnose sql debug logview dbgoff

diagnose sql debug logview dbgon

diagnose sql debug logview delete

diagnose sql debug logview show [<filter>] [<NUM>]

diagnose sql debug logview upload {ftp | sftp} <host> <dir> <user name> <password>

diagnose sql debug pglog show [<filter>] [<NUM>]

diagnose sql debug pglog upload {ftp | sftp} <host> <dir> <user name> <password>

diagnose sql debug sqlqry dbgoff

diagnose sql debug sqlqry dbgon <level value>

diagnose sql debug sqlqry delete

diagnose sql debug sqlqry show [<filter>][<NUM>]

diagnose sql debug sqlqry upload {ftp | sftp} <host> <dir> <user name> <password>

Variable	Description
hcache-agg dbgoff	Disable hcache-agg debug output.
hcache-agg dbgon	Enable hcache-agg debug output.
hcache-agg delete	Delete hcache-agg debug file.
hcache-agg show [<filter>] [<NUM>]	Show the last 10 lines of the hcache-agg debug file. Set filter for the debug file, and show the last NUM lines of the debug file. The filter and NUM variables optional.
hcache-agg upload {ftp \| sftp} <host> <dir> <user name> <password>	Upload hcache-agg debug file to FTP or SFTP server. Enter host IP address, directory, user name, and password.
imexport dbgoff	Disable Report import/export debug output.
imexport dbgon	Enable Report import/export debug output.
imexport delete	Delete Report import/export debug file.
imexport show [<filter>] [<NUM>]	Show the last 10 lines of the Report import/export debug file. Set filter for debug file, and show last NUM lines of the debug file. The filter and NUM variables are optional.
imexport upload {ftp \| sftp} <host> <dir> <user name> <password>	Upload Report import/export debug file to FTP or SFTP server. Enter host IP address, directory, user name, and password.
logview dbgoff	Disable log view debug output.
logview dbgon	Enable log view debug output.
logview delete	Delete log view debug file.
logview show [<filter>] [<NUM>]	Show the last 10 lines of the log view debug file. Set filter for debug file, and show last NUM lines of the debug file. The filter and NUM variables are optional.
logview upload {ftp \| sftp} <host> <dir> <user name> <password>	Upload log view debug file to FTP or SFTP server. Enter host IP address, directory, user name, and password.
pglog show [<filter>] [<NUM>]	Show the last 10 lines of the Postgres log debug file. Set filter for debug file, and show last NUM lines of the debug file. The filter and NUM variables are optional.
pglog upload {ftp \| sftp} <host> <dir> <user name> <password>	Upload Postgres log debug file to FTP or SFTP server. Enter host IP address, directory, user name, and password.
sqlqry dbgoff	Disable SQL query debug output.
sqlqry dbgon <level value>	Enable SQL query debug output. Set SQL query debug level (1-5). The default level is 1. Note: When the debug level is `5`, the final SQL running in sqlreportd will show in the debug output as well.
sqlqry delete	Delete the SQL query debug file.
sqlqry show [<filter>] [<NUM>]	Show the last 10 lines of the SQL query debug file. Set filter for the debug file, and show the last NUM lines of the debug file. The filter and NUM variables are optional.
sqlqry upload {ftp \| sftp} <host> <dir> <user name> <password>	Upload SQL query debug file to FTP or SFTP server. Enter host IP address, directory, user name, and password.

sql hcache

Use this command to show or update the SQL hcache.

Syntax

diagnose sql hcache add-task agg <adom> <norm-query-hash> <agg-level> <timestamp> <num-of-days>

diagnose sql hcache add-task agg-update <adom> <hid>

diagnose sql hcache dump-task <filter>

diagnose sql hcache list <adom> <query-hash/tag> <filter> <detail>

diagnose sql hcache plan <adom> <start-time> <end-time> <query-tag/norm-qry-hash/sql> <is-fortiview> <max-time-scale>

diagnose sql hcache rebuild-both <adom> <start-time> <end-time> <reset>

diagnose sql hcache rebuild-fortiview <adom> <start-time> <end-time> <reset>

diagnose sql hcache rebuild-report <adom> <start-time> <end-time> <reset>

diagnose sql hcache rebuild-status

diagnose sql hcache show hcache <adom> <id>

diagnose sql hcache show hcache-query <adom> <norm-qry-hash>

diagnose sql hcache show hcache-res-tbl <adom> <res-tbl-id>

diagnose sql hcache show time <time> <time> <time> <time>

diagnose sql hcache status {all | all-summary | <adom>}

Variable	Description
add-task agg <adom> <norm-query-hash> <agg-level> <timestamp> <num-of-days>	Add an hcache agg task: `adom`: The ADOM name. `norm-query-hash`: The normalized query hash. `agg-level`: The aggregation level. `timestamp`: The timestamp (format = yyyy-mm-dd hh:mm:ss). `num-of-days`: The number of days (1, 3, or 30).
add-task agg-update <adom> <hid>	Add an hcache agg update task: `adom`: The ADOM name. `hid`: The hcache agg ID.
dump-task <filter>	Dump hcache tasks. Enter the task filter.
list <adom> <query-hash/tag> <filter> <detail>	List hcaches: `adom`: The ADOM name. `query-hash/tag`: The hash or tag filter query, or `all` for all hcaches. `filter`: Narrow down the hcache list search result by using a filter. The filter keywords include: `status`: The hcache status. `0`(Ready), `1`(Ready-Loss), `2`(In-Building), `3`(Error), `4`(Invalid-SQL ), `5`(No-Data), `6`(Not-Ready). `fv_flag`: List FortiView/report only. `1`(fortiview), `0`(report). `sql`: The SQL query match. `''` for wildcard, e.g. select. `time_start`: Start of the log time. format: `yyyy-mm-dd hh:MM:ss`. `time_end`: End of the log time. format: `yyyy-mm-dd hh:MM:ss`. The following shows an example of the variable `<filter>`: `"status=0,1,5 sql=\"srcip, dstip*\" time_start>=\"2020-11-01 00:00:00\" time_end<=\"2020-11-30 23:59:59\""`. Enter `""` for no filter. `detail`: Show detailed information.
plan <adom> <start-time> <end-time> <query-tag/norm-qry-hash/sql> <is-fortiview> <max-time-scale>	Plan hcaches: `adom`: The ADOM name. `start-time`: The start time (format: yyyy-mm-dd hh:mm:ss). `end-time`: The end time (format: yyyy-mm-dd hh:mm:ss). `query-tag/norm-qry-hash/sql`: The query tag, normalized query hash, or sql statement. `is-fortiview`: Enter `1` for FortiView, or `0` for report. `max-time-scale`: Maximum timescale.
rebuild-both <adom> <start-time> <end-time> <reset>	Rebuild hcache for both report and FortiView. `adom`: The ADOM name or `all` for all ADOMs. `start-time`: The start time (format: yyyy-mm-dd hh:mm:ss). `end-time`: The end time (format: yyyy-mm-dd hh:mm:ss). `reset`: Clean up all existing hcache tasks.
rebuild-fortiview <adom> <start-time> <end-time> <reset>	Rebuild hcache for FortiView only. `adom`: The ADOM name or `all` for all ADOMs. `start-time`: The start time (format: yyyy-mm-dd hh:mm:ss). `end-time`: The end time (format: yyyy-mm-dd hh:mm:ss). `reset`: Clean up all existing hcache tasks.
rebuild-report <adom> <start-time> <end-time> <reset>	Rebuild hcache for report only. `adom`: The ADOM name or `all` for all ADOMs. `start-time`: The start time (format: yyyy-mm-dd hh:mm:ss). `end-time`: The end time (format: yyyy-mm-dd hh:mm:ss). `reset`: Clean up all existing hcache tasks.
rebuild-status	Show report hcache rebuild/check status.
show hcache <adom> <id>	Show hcache information. Enter the ADOM name and hcache ID.
show hcache-query <adom> <norm-qry-hash>	Show hcache query information. Enter the ADOM name and the normalized query hash.
show hcache-res-tbl <adom> <res-tbl-id>	Show hcache result table information. Enter the ADOM name and the result table ID.
show time <time> <time> <time> <time>	Show hcache time. Enter up to four timestamps.
status {all \| all-summary \| <adom>}	Show detailed hcache information. Enter the ADOM name, `all-summary` for the summary, or `all` for all ADOMs.

sql process

Use this command to kill or list query processes in the the SQL database.

Syntax

diagnose sql process kill <pid>

diagnose sql process list [full]

Variable	Description
kill <pid>	Kill a running query.
list [full]	List running query processes.

sql remove

Use this command to remove from the SQL database.

Syntax

diagnose sql remove {hcache <adom> <start-time> <end-time> | query-cache | rebuild-db-flag | tmp-tabe}

Variable	Description
{hcache <adom> <start-time> <end-time> \| query-cache \| rebuild-db-flag \| tmp-table}	Remove the selected information: `hcache`: Remove the hcache tables created for the SQL report. `adom`: The ADOM name. `start-time`: The start time (format: yyyy-mm-dd hh:mm:ss). `end-time`: The end time (format: yyyy-mm-dd hh:mm:ss). `query-cache`: Remove the SQL query cache for log search. `rebuild-db-flag`: Remove the rebuild database flag. The system will exit the rebuild database state. `tmp-table`: Remove the SQL database temporary tables.

Variable

Description

{hcache <adom> <start-time> <end-time> | query-cache | rebuild-db-flag | tmp-table}

Remove the selected information:

hcache: Remove the hcache tables created for the SQL report.
- adom: The ADOM name.
- start-time: The start time (format: yyyy-mm-dd hh:mm:ss).
- end-time: The end time (format: yyyy-mm-dd hh:mm:ss).
query-cache: Remove the SQL query cache for log search.
rebuild-db-flag: Remove the rebuild database flag. The system will exit the rebuild database state.
tmp-table: Remove the SQL database temporary tables.

sql show

Use this command to show SQL database information.

Syntax

diagnose sql show {db-size | hcache-size | log-filters | log-stfile <device-id> <vdom> | policy info <adom> }

Variable	Description
{db-size \| hcache-size \| log-filters \| log-stfile <device-id> <vdom> \| policy-info <adom>}	Show the database, hcache size, log filters, or log status file: `db-size`: Show database size. `hcache-size`: Show hcache size. `log-filters`: Show log view searching filters. `log-stfile`: Show logstatus file for the specified device (for HA cluster, input the member's serial number) and VDOM. `policy-info`: Show policy uuid and name map.

Variable

Description

{db-size | hcache-size | log-filters | log-stfile <device-id> <vdom> | policy-info <adom>}

Show the database, hcache size, log filters, or log status file:

db-size: Show database size.
hcache-size: Show hcache size.
log-filters: Show log view searching filters.
log-stfile: Show logstatus file for the specified device (for HA cluster, input the member's serial number) and VDOM.
policy-info: Show policy uuid and name map.

sql status

Use this command to show statuses of the SQL database.

Syntax

diagnose sql status {rebuild-adom <adom> | rebuild-db | run_sql_rpt | sqlplugind | sqlreportd | upgrade-db}

Variable	Description
{rebuild-adom <adom> \| rebuild-db \| run_sql_rpt \| sqlplugind \| sqlreportd}	Show the status: `rebuild-adom <adom>`: Show SQL log database rebuild status of ADOMs. `rebuild-db`: Show SQL log database rebuild status. `run-sql-rpt`: Show run_sql_rpt status. `sqlplugind`: Show sqlplugind status. `sqlreportd`: Show sqlreportd status. `upgrade-db`: Show log SQL database upgrade status.

Variable

Description

{rebuild-adom <adom> | rebuild-db | run_sql_rpt | sqlplugind | sqlreportd}

Show the status:

rebuild-adom <adom>: Show SQL log database rebuild status of ADOMs.
rebuild-db: Show SQL log database rebuild status.
run-sql-rpt: Show run_sql_rpt status.
sqlplugind: Show sqlplugind status.
sqlreportd: Show sqlreportd status.
upgrade-db: Show log SQL database upgrade status.

sql upload

Use this command toupload sqlplugind messages / pgsvr logs via FTP or SFTP.

Syntax

diagnose sql upload {ftp | sftp} <host> <directory> <user_name> <password>

Variable	Description
{ftp \| sftp} <host> <directory> <user_name> <password>	Upload sqlplugind messages / pgsvr logs with FTP or SFTP.