Fortinet white logo
Fortinet white logo

CLI Reference

sql

sql

Use this command 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-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-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 | <adom> | all-summary}

diagnose sql process kill <pid>

diagnose sql process list [full]

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

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 (3600 - 2147483647, default = 172800).

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

Show, set, or reset max row number for base hcache (1000 - 1500000, default = 1000000).

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

Show, set, or reset max row number for fortiview hcache (1000 - 400000, default = 50000).

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

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

Show, set, or reset max row number for report hcache (1000 - 400000, default = 18000).

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

Show, set, or reset max row number for sampling (1000 - 10000000, 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 (0 - 1, default = 1).

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

Show, set, or reset the type of sampling (0 - 1, 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. The following input is required:

  • 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. The following input is required:

  • adom: The ADOM name.
  • hid: The hcache agg ID.

hcache dump-task <filter>

Dump hcache tasks. Enter the task filter.

hcache list <adom> <queryhash/

tag> <filter> <detail>

List hcaches:

  • adom: The ADOM name.

  • query-hash/tag: The hash or tag filter query, or all for all queries.

  • 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 | <adom> | all-summary}

Show detailed hcache information per ADOM, for all ADOMs, or display the summary.

process kill <pid>

Kill a running query.

process list [full]

List running query processes.

remove {hcache <adom> [mode] | 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

sql

Use this command 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-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-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 | <adom> | all-summary}

diagnose sql process kill <pid>

diagnose sql process list [full]

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

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 (3600 - 2147483647, default = 172800).

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

Show, set, or reset max row number for base hcache (1000 - 1500000, default = 1000000).

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

Show, set, or reset max row number for fortiview hcache (1000 - 400000, default = 50000).

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

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

Show, set, or reset max row number for report hcache (1000 - 400000, default = 18000).

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

Show, set, or reset max row number for sampling (1000 - 10000000, 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 (0 - 1, default = 1).

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

Show, set, or reset the type of sampling (0 - 1, 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. The following input is required:

  • 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. The following input is required:

  • adom: The ADOM name.
  • hid: The hcache agg ID.

hcache dump-task <filter>

Dump hcache tasks. Enter the task filter.

hcache list <adom> <queryhash/

tag> <filter> <detail>

List hcaches:

  • adom: The ADOM name.

  • query-hash/tag: The hash or tag filter query, or all for all queries.

  • 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 | <adom> | all-summary}

Show detailed hcache information per ADOM, for all ADOMs, or display the summary.

process kill <pid>

Kill a running query.

process list [full]

List running query processes.

remove {hcache <adom> [mode] | 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.