Use efficient queries when you embed SQL in custom reports. Use clear and obvious syntax. If you extract report data for a large-scale system, avoid inelegant joins and unnecessary field synthesis or data expansion. In-line syntax that initiates data expansion, sorting, and value transformation can cause unintended report output. Heavy data processing within an SQL query can cause reports to timeout. Data processing done in the wrong sequence can result inaccurate or lost data.

Important: If you perform custom queries on a large or scaled out MOVEit Transfer deployment, it is best practice to perform data field normalization (deduplication, for example), sorting, and other sorts of curation after results are returned. Return results as XML or CSV, and then filter data with a suitable application or script.

The following table provides guidelines for running custom reports.

Report Parameter

Tips

Start Date/End Date

Avoid long date ranges (such as range greater than two weeks), and be especially careful with:

  • Long date ranges on the LOGS table.
  • Wildcard criteria combined with deep tables.

Fields

Column Fields. List column fields needed individually.

Tables

Use Join and (inner join). Cross Joins are very inefficient.

  • Do not use syntax that results in cross join, such as: WHERE TableA.MyField = TableB.MyOtherField (effectively a cross join)
  • Cross join syntax expands into an expensive cross-correlation.

Criteria (works like WHERE)

Avoid using LIKE for one or more wildcard expansions.

  • Multiple wildcard expansions can yield unexpected results.
  • Pattern matching requires extra processing.

Limit

Use of Limit (for example, let's say Limit=10) is helpful for:

  • Trying out a query syntax.
  • Getting a rough feel how the query performs.

Using Field and Table-Scoped SQL Statements in Custom Reports

Re-configure and Re-enable a Timed-out Report

If you generate (run) a report that exceeds the site efficiency rules, MOVEit Transfer halts report processing and returns an error message.

Report Disabled Due to Timeout (Reports view in WebUI shown)

To re-enable your report

  1. Click the edit button () for the report.
  2. Make one or more changes to the current report configuration, such as:
    • Add a records limit (Limit)
    • Shorten date range.
    • Reduce, remove wildcard symbols.
    • Use JOIN (inner join) rather than WHERE (cross join) in Criteria

  3. Click the Enable Report button.