Advanced Report Example: Transfer Sessions with High Packet Loss

The following example demonstrates the process of creating a new, advanced report (following the instructions described in the topic Creating an Advanced Report) for transfers with high packet loss. In our example, we will generate a report that displays a list of all transfers that have high packet loss, where high loss is user specified. The report includes transfers that started before the report period start, as well as ones that ended after the report period end, as long as part of the transfer fell within the reporting period. Note that the data is not prorated, meaning that the "bytes transferred," "files complete" and other values show totals for the entire transfer, even if part of the transfer took place outside the reporting period.
  1. Go to the Manage Report Types page.
    Select Reports from the Console menu, and then click the Manage Report Types button. On the Manage Report Types screen, click the New Advanced button.
  2. Input your advanced report's name and description.

    On the Create New Advanced Report Type page, enter the following information:

    Field Description
    Name Transfer Sessions with High Packet loss
    Description Displays a list of all transfers that have high packet loss.
  3. Create your SQL script.
    Important: For assistance on SQL variables and a fields reference guide, please click the Help link.
    
CREATE TABLE $FINAL_RESULT_TABLE

SELECT DISTINCT -- prevents duplicate rows (that is, overlapping permissions)
  ts.name
  , ts.contact
  , ts.bytes_transferred
  , ts.bytes_lost
  , TRUNCATE((ts.bytes_lost)*100/(ts.bytes_transferred + ts.bytes_lost), 1) AS `packet loss %`
  , ts.source_ip AS `from`
  , ts.dest_ip AS `to`
  , ts.started_at
  , ts.stopped_at
  , ts.status
  , ts.files_complete
  , ts.files_failed
  , ts.files_skipped

FROM
  $TBL_TRANSFER_SESSIONS ts

WHERE
 ((ts.bytes_lost * 100) /(ts.bytes_lost + ts.bytes_transferred)) >= $PACKET_LOSS /* Custom/configurable variable */
  AND
   ts.started_at < '$REPORT_PERIOD_END'
   AND (
    ts.stopped_at >= '$REPORT_PERIOD_START'
    OR ts.stopped_at IS NULL
  )

ORDER BY
  5 DESC
  , 8
;
    Important: For demonstration purposes, we have created a configurable/custom variable called $PACKET_LOSS in the SQL script text above. You may, alternatively, utilize the built-in SQL database field avg_loss_pct, to display the average packet loss over the network (as a percentage). Please see the Help link in the application for details.
  4. Save, finalize run settings and run your report.

    Next, click the Create and Run button. Confirm the following settings on next page:

    • Title is as described above.
    • Report is scheduled to Run Now.
    • Report period is Last 24 hours and time zone is Pacific.
    Once confirmed, click the Run Report button.
  5. View your Web report.
    After clicking the Run Report button, the page will update to display the report queuing and then running. Once generated, the Web version of your basic report will appear as shown below.