Fortinet black logo

CLI Reference

sql

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] [string]

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

diagnose sql config hcache-agg-step [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 report-engine [set {gen1 | gen2}]

diagnose sql config topdev-log-thres [reset | set <integer>]

diagnose sql config topdev-num-max [reset | set <integer>]

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

diagnose sql hcache aggregate debug {on | off}

diagnose sql hcache aggregate debug-file {show | delete | upload <ftp host> <ftp dir> <ftp user name> <ftp password>

diagnose sql hcache aggregate status <adom> <query-hash/tag> <detail>

diagnose sql hcache dump-task <filter>

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

diagnose sql hcache show hcache <adom> <id>

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

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 status {all | <adom>}

diagnose sql process list [full]

diagnose sql process kill <pid>

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_host_ip> <ftp_directory> <ftp_user_name> <ftp_password>

Variable

Description

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

Show, set (in seconds), or reset the auto-cache delay.

config debug-filter {set | test} <string>

Set or test the SQL plugin debug filter.

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

View or set the time span for the deferred index.

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

Show, set, or reset the hcache aggregation step.

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

Show, set, or reset max row number for fortiview hcache.

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

Show, set, or reset max row number for report hcache.

config report-engine [set {gen1 | gen2}]

Show or set switch report-engine version.

config topdev-log-thres [reset | set <integer>]

Show, set, or reset log threshold of top devices.

config topdev-num-max [reset | set <integer>]

Show, set, or reset max number of top devices.

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

Add an hcache 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 aggregate debug {on | off}

Turn debug on or off.

hcache aggregate debug-file {show | delete | upload <ftp host> <ftp dir> <ftp user name> <ftp password>

Delete, show or upload the debug file. The following input is required when uploading the debug file:

  • ftp host: The FTP host IP address.
  • ftp dir: The FTP directory.
  • ftp user name: The FTP user name.
  • ftp password: The FTP password.

hcache aggregate status <adom> <query-hash/tag> <detail>

Show hcache aggregation info. The following input is required:

  • adom: The ADOM name, or all for all ADOMs.
  • query-hash/tag: The hash or tag filter query, or all for all queries.
  • detail: Show detailed information.

hcache dump-task <filter>

Dump hcache tasks. Enter the task filter.

hcache list <adom> <start-time> <end-time> <query-tag/norm-qry-hash/sql> <is-fortiview>

List hcaches.

The following input is required:

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

hcache show hcache <adom> <id>

Show hcache information. Enter the ADOM name and hcache ID.

hcache show time <time> <time> <time> <time>

Show hcache time. Enter up to four timestamps.

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. 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. Start and end times are in the format yyyy-mm-dd hh:mm:ss.

hcache status {all | <adom>}

Show detailed hcache information per ADOM or for all ADOMs.

process list [full]

List running query processes.

process kill <pid>

Kill a running query.

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

Remove the selected information. The following options are available:

  • hcache: Remove the hcache tables created for the SQL report.
  • 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. The following options are available:

  • 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.
  • policy-info: Show policy uuid and name map.

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

The following options are available:

  • 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_host_ip> <ftp_directory> <ftp_user_name> <ftp_password>

Upload sqlplugind messages / pgsvr logs via FTP.

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] [string]

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

diagnose sql config hcache-agg-step [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 report-engine [set {gen1 | gen2}]

diagnose sql config topdev-log-thres [reset | set <integer>]

diagnose sql config topdev-num-max [reset | set <integer>]

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

diagnose sql hcache aggregate debug {on | off}

diagnose sql hcache aggregate debug-file {show | delete | upload <ftp host> <ftp dir> <ftp user name> <ftp password>

diagnose sql hcache aggregate status <adom> <query-hash/tag> <detail>

diagnose sql hcache dump-task <filter>

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

diagnose sql hcache show hcache <adom> <id>

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

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 status {all | <adom>}

diagnose sql process list [full]

diagnose sql process kill <pid>

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_host_ip> <ftp_directory> <ftp_user_name> <ftp_password>

Variable

Description

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

Show, set (in seconds), or reset the auto-cache delay.

config debug-filter {set | test} <string>

Set or test the SQL plugin debug filter.

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

View or set the time span for the deferred index.

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

Show, set, or reset the hcache aggregation step.

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

Show, set, or reset max row number for fortiview hcache.

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

Show, set, or reset max row number for report hcache.

config report-engine [set {gen1 | gen2}]

Show or set switch report-engine version.

config topdev-log-thres [reset | set <integer>]

Show, set, or reset log threshold of top devices.

config topdev-num-max [reset | set <integer>]

Show, set, or reset max number of top devices.

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

Add an hcache 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 aggregate debug {on | off}

Turn debug on or off.

hcache aggregate debug-file {show | delete | upload <ftp host> <ftp dir> <ftp user name> <ftp password>

Delete, show or upload the debug file. The following input is required when uploading the debug file:

  • ftp host: The FTP host IP address.
  • ftp dir: The FTP directory.
  • ftp user name: The FTP user name.
  • ftp password: The FTP password.

hcache aggregate status <adom> <query-hash/tag> <detail>

Show hcache aggregation info. The following input is required:

  • adom: The ADOM name, or all for all ADOMs.
  • query-hash/tag: The hash or tag filter query, or all for all queries.
  • detail: Show detailed information.

hcache dump-task <filter>

Dump hcache tasks. Enter the task filter.

hcache list <adom> <start-time> <end-time> <query-tag/norm-qry-hash/sql> <is-fortiview>

List hcaches.

The following input is required:

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

hcache show hcache <adom> <id>

Show hcache information. Enter the ADOM name and hcache ID.

hcache show time <time> <time> <time> <time>

Show hcache time. Enter up to four timestamps.

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. 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. Start and end times are in the format yyyy-mm-dd hh:mm:ss.

hcache status {all | <adom>}

Show detailed hcache information per ADOM or for all ADOMs.

process list [full]

List running query processes.

process kill <pid>

Kill a running query.

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

Remove the selected information. The following options are available:

  • hcache: Remove the hcache tables created for the SQL report.
  • 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. The following options are available:

  • 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.
  • policy-info: Show policy uuid and name map.

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

The following options are available:

  • 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_host_ip> <ftp_directory> <ftp_user_name> <ftp_password>

Upload sqlplugind messages / pgsvr logs via FTP.