Reference: SQL Variables for Advanced Reports

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:
  • A FILE record can have multiple associated TRANSFER SESSION FILE records (if a file took more than one attempt to transfer).
  • A FILE record has one and only one associated TRANSFER record ($TBL_TRANSFER_FILES.transfer_id = $TBL_TRANSFERS.id).
$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:
  • A TRANSFER SESSION record can have multiple TRANSFER SESSION FILE records (if the session attempted to transfer more than one file).
  • A TRANSFER SESSION record has one and only one associated TRANSFER record ($TBL_TRANSFER_SESSIONS.transfer_id = $TBL_TRANSFERS.id).
$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:
  • A TRANSFER SESSION FILE record has one and only one associated FILE RECORD ($TBL_TRANSFER_SESSION_FILES.transfer_file_id = $TBL_FILES.id).
  • A TRANSFER SESSION FILE record has one and only one associated TRANSFER SESSION record ($TBL_TRANSFER_SESSION_FILES.transfer_session_id = $TBL_TRANSFER_SESSIONS.id).
$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:
  • A TRANSFER record can have multiple TRANSFER SESSION records (if multiple attempts or batches were required to transfer all the data).
  • A TRANSFER record can have multiple FILE records (if the transfer consisted of more than one file).
$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 (for example, '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 (for example, '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. See Editing Custom Variables for instructions on how to create and configure a custom variable.