Export Reports to SFTP

Use SFTP schedules to set up reports/dashboards to be sent to your SFTP folders every day. SFTP Schedules allow admins/analysts to schedule a report/dashboard to be sent to an SFTP destination.

Some common use cases:

• Backup report data periodically to an SFTP server
• Deliver data regularly to your partners' servers

Data Freshness

Check out Data Freshness for more details.

Set up SFTP Schedules

Configure SFTP Connection

To manage your SFTP connections, go to Integrations Page:

To add a new SFTP connection, click Add SFTP Connection and fill in the required information below

• Display Name: Add integration title to manage your different connections
• Host, Port: Address of the SFTP server
• Root Path: Destination files will be preceded by this path
• Username, Password: Credentials to access the SFTP server
• Holistics Public Key: If you are using public key authentication instead of username/password, please add Holistics public key to your authorized keys

Best Practice

For security, restrict the SFTP user's permissions to only read and write files within a specific directory. We recommend creating a dedicated user for SFTP purposes rather than using an existing account. Check out this guide for step-by-step instructions

Set up scheduled export options

To set an SFTP schedule for a dashboard, in the dashboard view page, click on Export > Send to SFTP:

You'll see the following form:

• Title: Provide a name for your schedule
• Controls (optional): Customize dashboard controls before sending each export. You can adjust Filters, Period Comparison, and Date Drill.
  • Changes made here will not affect the main dashboard.
  • You can only modify existing controls. New controls cannot be created.
• Exported Widget: Choose the widget you want to create Export Schedule
• SFTP Connection: Choose the connection you want to create the Export Schedule, you can manage it at Integrations Page
• File Name: The path of the destination file. Holistics will create folders/files if they do not exist. Otherwise, that file will be overwritten (CSV/Excel files are supported). You are able to include/exclude headers and choose the suitable separator.
• Frequency: Set the schedule interval (e.g., every X hours, daily, weekly, monthly, etc.)
• Failure Recipient Email (optional): A comma-separated list of email addresses to receive notifications when the SFTP schedule fails to deliver, in addition to the schedule creator and the report/dashboard creator. Learn more in Notify Delivery Failures.

SFTP Schedule Execution Flow

An SFTP Schedule execution follows these steps:

1. Execute the Query Report to get the result data
2. Write the result data to a temporary file in $REMOTE_PATH/.holistics_tmp/ ($REMOTE_PATH is configured in the SFTP connection)
3. Prepare the destination path: create the non-existing directories in the destination path
4. Move the temporary file to the destination path

Export Sequential Files based on Timestamp

Sometimes when writing to the destination, you want the system to add new files (based on date/timestamp) instead of overwriting existing files.

{{$today}}/{{$source_title}}_{{$timestamp}}.xlsx

The above will produce filenames like:

• 2017-05-06/sales_pivo_t_table_1494106200.xlsx
• 2017-05-07/sales_pivo_t_table_1614106200.xlsx

The variables you can use are:

• $today, $yesterday: Today or yesterday in YYYY-MM-DD format
• $today_underscore, $yesterday_underscore: Today or Yesterday in YYYY_MM_DD format
• $today_flatten, $yesterday_flatten: Today or Yesterday in YYYYMMDD format
• $timestamp: Unix timestamp
• $source_title: Title of the report in lowercase with special characters replaced by underscores (e.g., 'Sales@Pivo^*?t Table' -> 'sales_pivo_t_table').
• $source_title_uppercase: Similiar to $source_title but in uppercase.

Set up a dedicated SFTP User

This section will give you a step-by-step instruction on setting up a new user on your SFTP server that is dedicated to SFTP purposes. The new user will be restricted from accessing a specific directory and use SFTP commands only.

Run the following commands as root.

• Create a new user

adduser holistics_sftp

• Create a new directory. Later, we will make sure the SFTP user can only access this directory by configuring ChrootDirectory. It needs to be owned by the user root for the ChrootDirectory configuration to work

mkdir -p /holistics
chown root: /holistics

• Since /holistics is owned by root, new user holistics_sftp should not be allowed to write to that directory. Thus, we will create an inner directory that holistics_sftp can write to. In this example, we are creating a new folder named exported. Later when you fill in the settings of the SFTP Connection in Holistics, you should input exported in Root Path, so that all output files will reside within this directory.

mkdir -p /holistics/exported
chown holistics_sftp: /holistics/exported
chmod 0755 /holistics/exported

Restrict the access of new users by editing /etc/ssh/sshd_config

# override default of no subsystems
#Subsystem	sftp	/usr/lib/openssh/sftp-server
Subsystem sftp internal-sftp

Match User holistics_sftp
  X11Forwarding no
  AllowTcpForwarding no
  ChrootDirectory /holistics
  ForceCommand internal-sftp

The above config makes sure user holistics_sftp can only read the directory /holistics and can only use SFTP commands.

Restart sshd service

service sshd restart

FAQs

Could I use FTP instead of SFTP for scheduled deliveries?

No. To ensure your data security, Holistics only supports SFTP instead of FTP.