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-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 report-engine [set gen2]

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

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

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

diagnose sql hcache status {all | <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_host_ip> <ftp_directory> <ftp_user_name> <ftp_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} <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 (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 report-engine [set gen2]

Show or set the 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 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 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:

  • 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> <max-time-scale>

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

Show hcache time. Enter up to four timestamps.

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_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-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 report-engine [set gen2]

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

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

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

diagnose sql hcache status {all | <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_host_ip> <ftp_directory> <ftp_user_name> <ftp_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} <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 (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 report-engine [set gen2]

Show or set the 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 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 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:

  • 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> <max-time-scale>

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

Show hcache time. Enter up to four timestamps.

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

Upload sqlplugind messages / pgsvr logs via FTP.