sql

Use these commands to diagnose the SQL database.

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-base-trim-interval [reset | set <integer>]

diagnose sql config hcache-max-fv-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>]

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 logview dbgoff

diagnose sql debug logview dbgon <level value>

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

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 <start-time> <end-time>

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

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

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

diagnose sql process kill <pid>

diagnose sql process list [full]

diagnose sql remove {hcache <adom> [fast] | query-cache | rebuild-db-flag | tmp-tabe}

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

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

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

Variable	Description
config auto-cache-delay [set <seconds>\| reset]	Show, set, or reset the auto-cache delay, in seconds (default = 300).
config debug-filter {set \| test} <daemon> <string>	Show sqlplugind and sqlreportd debug filter. Enter `sqlplugind`, `sqlreportd` or `both` as the `<daemon>`. Enter the filter string.
config deferred-index-timespan [set <value>]	View or set the time span for the deferred index (default = 10000).
config hcache-agg-step [reset \| set <integer>]	Show, set, or reset the hcache aggregation step (default = 10).
config hcache-base-trim-interval [reset \| set <integer>]	Show, set, or reset the hcache base trim interval (default = 172800).
config hcache-max-fv-row [reset \| set <integer>]	Show, set, or reset max row number for fortiview hcache (default = 100000).
config hcache-max-rpt-row [reset \| set <integer>]	Show, set, or reset max row number for report hcache (default = 18000).
config sampling-max-row [reset \| set <integer>]	Show, set, or reset max row number for sampling (default = 1000000).
config sampling-status [reset \| set <integer>]	Show, set, or reset the sampling status. Enter 0 for disabling and 1 for enabling the sample status (default = 1).
config sampling-type [reset \| set <integer>]	Show, set, or reset the type of sampling (default = 0).
debug hcache-agg dbgoff	Disable hcache-agg debug output.
debug hcache-agg dbgon	Enable hcache-agg debug output.
debug hcache-agg delete	Delete hcache-agg debug file.
debug 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 are optional.
debug 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.
debug logview dbgoff	Disable Log view debug output.
debug logview dbgon <level value>	Enable log view debug output. Set log view debug level (1-5). Default level is 1.
debug logview delete	Delete log view debug file.
debug 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.
debug 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.
debug sqlqry dbgoff	Disable SQL query debug output.
debug 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.
debug sqlqry delete	Delete the SQL query debug file.
debug 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.
debug 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.
hcache 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).
hcache add-task agg-update <adom> <hid>	Add an hcache agg update task: `adom`: The ADOM name. `hid`: The hcache agg ID.
hcache dump-task <filter>	Dump hcache tasks. Enter the task filter.
hcache 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.
hcache 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.
hcache rebuild-both <start-time> <end-time>	Rebuild hcache for both report and FortiView. Start and end times are in the format yyyy-mm-dd hh:mm:ss.
hcache rebuild-fortiview <start-time> <end-time>	Rebuild hcache for FortiView only. Start and end times are in the format yyyy-mm-dd hh:mm:ss.
hcache rebuild-report <start-time> <end-time>	Rebuild hcache for report only. Start and end times are in the format yyyy-mm-dd hh:mm:ss.
hcache rebuild-status	Show report hcache rebuild/check status.
hcache show hcache <adom> <id>	Show hcache information. Enter the ADOM name and hcache ID.
hcache show hcache-query <adom> <norm-qry-hash>	Show hcache query information. Enter the ADOM name and the normalized query hash.
hcache show hcache-res-tbl <adom> <res-tbl-id>	Show hcache result table information. Enter the ADOM name and the result table ID.
hcache show time <time> <time> <time> <time>	Show hcache time. Enter up to four timestamps.
hcache status {all \| all-summary \| <adom>}	Show detailed hcache information. Enter the ADOM name, `all-summary` for the summary, or `all` for all ADOMs.
process kill <pid>	Kill a running query.
process list [full]	List running query processes.
remove {hcache <adom> [fast] \| query-cache \| rebuild-db-flag \| tmp-table}	Remove the selected information: `hcache`: Remove the hcache tables created for the SQL report. Enter `fast` to not remove the hcache result tables. `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.
show {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.
status {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.
upload {ftp \| tftp} <host> <directory> <user_name> <password>	Upload sqlplugind messages / pgsvr logs with FTP or TFTP.

sql

Use these commands to diagnose the SQL database.

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-base-trim-interval [reset | set <integer>]

diagnose sql config hcache-max-fv-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>]

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 logview dbgoff

diagnose sql debug logview dbgon <level value>

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

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 <start-time> <end-time>

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

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

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

diagnose sql process kill <pid>

diagnose sql process list [full]

diagnose sql remove {hcache <adom> [fast] | query-cache | rebuild-db-flag | tmp-tabe}

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

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

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

Variable	Description
config auto-cache-delay [set <seconds>\| reset]	Show, set, or reset the auto-cache delay, in seconds (default = 300).
config debug-filter {set \| test} <daemon> <string>	Show sqlplugind and sqlreportd debug filter. Enter `sqlplugind`, `sqlreportd` or `both` as the `<daemon>`. Enter the filter string.
config deferred-index-timespan [set <value>]	View or set the time span for the deferred index (default = 10000).
config hcache-agg-step [reset \| set <integer>]	Show, set, or reset the hcache aggregation step (default = 10).
config hcache-base-trim-interval [reset \| set <integer>]	Show, set, or reset the hcache base trim interval (default = 172800).
config hcache-max-fv-row [reset \| set <integer>]	Show, set, or reset max row number for fortiview hcache (default = 100000).
config hcache-max-rpt-row [reset \| set <integer>]	Show, set, or reset max row number for report hcache (default = 18000).
config sampling-max-row [reset \| set <integer>]	Show, set, or reset max row number for sampling (default = 1000000).
config sampling-status [reset \| set <integer>]	Show, set, or reset the sampling status. Enter 0 for disabling and 1 for enabling the sample status (default = 1).
config sampling-type [reset \| set <integer>]	Show, set, or reset the type of sampling (default = 0).
debug hcache-agg dbgoff	Disable hcache-agg debug output.
debug hcache-agg dbgon	Enable hcache-agg debug output.
debug hcache-agg delete	Delete hcache-agg debug file.
debug 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 are optional.
debug 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.
debug logview dbgoff	Disable Log view debug output.
debug logview dbgon <level value>	Enable log view debug output. Set log view debug level (1-5). Default level is 1.
debug logview delete	Delete log view debug file.
debug 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.
debug 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.
debug sqlqry dbgoff	Disable SQL query debug output.
debug 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.
debug sqlqry delete	Delete the SQL query debug file.
debug 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.
debug 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.
hcache 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).
hcache add-task agg-update <adom> <hid>	Add an hcache agg update task: `adom`: The ADOM name. `hid`: The hcache agg ID.
hcache dump-task <filter>	Dump hcache tasks. Enter the task filter.
hcache 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.
hcache 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.
hcache rebuild-both <start-time> <end-time>	Rebuild hcache for both report and FortiView. Start and end times are in the format yyyy-mm-dd hh:mm:ss.
hcache rebuild-fortiview <start-time> <end-time>	Rebuild hcache for FortiView only. Start and end times are in the format yyyy-mm-dd hh:mm:ss.
hcache rebuild-report <start-time> <end-time>	Rebuild hcache for report only. Start and end times are in the format yyyy-mm-dd hh:mm:ss.
hcache rebuild-status	Show report hcache rebuild/check status.
hcache show hcache <adom> <id>	Show hcache information. Enter the ADOM name and hcache ID.
hcache show hcache-query <adom> <norm-qry-hash>	Show hcache query information. Enter the ADOM name and the normalized query hash.
hcache show hcache-res-tbl <adom> <res-tbl-id>	Show hcache result table information. Enter the ADOM name and the result table ID.
hcache show time <time> <time> <time> <time>	Show hcache time. Enter up to four timestamps.
hcache status {all \| all-summary \| <adom>}	Show detailed hcache information. Enter the ADOM name, `all-summary` for the summary, or `all` for all ADOMs.
process kill <pid>	Kill a running query.
process list [full]	List running query processes.
remove {hcache <adom> [fast] \| query-cache \| rebuild-db-flag \| tmp-table}	Remove the selected information: `hcache`: Remove the hcache tables created for the SQL report. Enter `fast` to not remove the hcache result tables. `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.
show {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.
status {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.
upload {ftp \| tftp} <host> <directory> <user_name> <password>	Upload sqlplugind messages / pgsvr logs with FTP or TFTP.

Secure Networking

Hybrid Mesh Firewall

NOC Management

LAN

WAN

Unified SASE

Single Vendor SASE

Cloud Network Security

Secure Endpoint Connectivity

Web Application / API Protection

Security Operations

Security Operations Automation

Identity

Early Detection & Prevention

Secure Networking

Hybrid Mesh Firewall

NOC Management

LAN

WAN

Communication & Surveillance

Unified SASE

Single Vendor SASE

Secure Endpoint Connectivity

Cloud Network Security

Cloud-Native Security

Web Application / API Protection

Security Operations

Security Operations Automation

Endpoint

Data Protection

Identity

Email

Early Detection & Prevention

Expert Services

Edge Firewall

Orchestration & management

SD Branch

Application Delivery

Single Vendor SASE

Secure Endpoint Connectivity

Secure Private Access

Thin Edge

Identity

Application Gateway

Enterprise Asset Management

Endpoint Agent

Agentless Security Posture

Identity

Wireless

Switching

Identity

Privilege Acccess Management

Next Generation Firewall

Orchestration & management

Expert Services

All

All

Account Management

SAAS Management

SAAS Application Security

Managed Services

Platform as a service (PAAS)

Other SAAS Services

4D Pillars

Cloud

Popular Solutions

CLI Reference

sql

sql

Syntax

sql

sql

Syntax