Reporting |
SQL Variables for Advanced Reporting
When creating your advanced report, you may utilize the SQL variables listed below. These variables also appear within Console's built-in, SQL script text help.
SQL Variable | Description |
---|---|
$TBL_FILES | Files table. One record in this table represents one file. At run time, this variable gets replaced with the SQL name of the table containing the file data (currently 'rpt_transfer_files'). Please note the following distinction:
|
$TBL_TRANSFER_SESSIONS | Transfer sessions table. One record in this table represents one attempt to transfer data. If you start a transfer and it fails, then automatically retries and succeeds, there will be two records in this table, one for the initial attempt and one for the automatic retry. For hot folder transfers, each session represents one attempt to transfer a batch of files that are currently available. If new files become available while the first batch is in progress, these may be transferred in a subsequent session, resulting in an additional record in this table. At run time, this variable gets replaced with the SQL name of the table containing the transfer session data (currently 'rpt_transfer_sessions'). Please note the following distinction:
|
$TBL_TRANSFER_SESSION_FILES | Files within a transfer session. One record in this table represents one attempt to transfer a file. At run time, this variable gets replaced with the SQL name of the table containing the file session data (currently 'rpt_transfer_session_files'). Please note the following distinction:
|
$TBL_NODES | A table containing one record for each node, whether managed or unmanaged. At run time, this variable gets replaced with the SQL name of the table containing the node data (currently 'rpt_transfer_nodes'). |
$TBL_TRANSFERS | A TRANSFER groups together TRANSFER SESSIONS to tie together retry attempts and hot folder file batches. Related TRANSFER SESSIONS are grouped together so that no matter how many times the session was interrupted and retried, only a single record will be present in this table. At run time, this variable gets replaced with the SQL name of the table containing the transfer data (currently 'rpt_transfers'). Please note the following distinction:
|
$FINAL_RESULT_TABLE | This is the table where you place your final results. The data displayed on reports comes directly from this table. At run time, this variable gets replaced with a name based on an auto-generated numeric id (e.g. 'report_100_results'). |
$TMP_TABLENAME | If you need any temporary tables for intermediate record processing, give them names starting with "$TMP_" (for example, $TMP_UNIQUE_IP_ADDRESSES). At run time, these variables get replaced with a name based on an auto-generated numeric id (e.g. 'report_100_temp_unique_ip_addresses'). |
$USER_ID | This is the login id of user requesting report. At run time, this variable gets replaced with the numeric id of the user requesting the report. |
$REPORT_PERIOD_START | Report period start. The user running this report will be prompted for a value at request time. (Value is converted to UTC before substitution). |
$REPORT_PERIOD_END | Report period end. The user running this report will be prompted for a value at request time. (Value is converted to UTC before substitution). |
$ANYTHING_ELSE | Any $NAME that does not match one of the variables is presumed to be a custom variable whose value will be provided by the report requester. |