Follow

Using SFTP to automate imports

 

You can create/update/delete user data using the SFTP (Secure File Transfer Protocol) site. This saves you the task of logging in to Jostle to upload and allows you to automate/schedule a task in your server to run the imports/deletions into your Jostle intranet.

Table of contents

  1. Getting started
  2. SFTP server: Files and folder structure
  3. How to create the input file (CSV file)
  4. General setup
  5. When will Jostle process the upload?
  6. Scripting/Automation
  7. Attachments

1. Getting started

Overview

To use the SFTP site you will need to:

  • Generate public and private keys to access the SFTP folders
  • Contact Jostle so we can configure a SFTP account for you.
  • Send the public key via File transfer 
  • Allow time for Jostle to then setup your account
  • Complete setup after receiving an email confirmation from Jostle.
  • Access the SFTP server, update the contents of the setup.txt file and then begin testing.

NOTE—Jostle only supports OpenSSH formatted RSA keys.

For the purposes of this article we're going to use the WinSCP client, as it also contains PuTTYgen, which can be used to create the public and private keys. However, there are a number of similar tools that can provide these functions as well, so you are encouraged to research WinSCP along with some other tools before deciding which one to use.

 

Part 1 - Creating and sending keys 

1. Download and install WinSCP

2. Open WinSCP, click Tools and select “Run PuTTYgen”    

SF01b.png

 

3. On the PuTTY Key Generator, ensure the Parameters are set to RSA and 2048, then click Generate and follow the prompts to generate the public key.       

SF03b.png                    

4. Once it is generated, copy the public key and save it in Notepad or similar text file.

SF06b.png

 

5. Create and confirm a passphrase (password) and then click Save private key. This will save the private key on your hard drive (as a .ppk file).

SF07b.png

6. Next, send an email to support@jostle.us requesting an SFTP account. Indicate you will send the public key via File Transfer (do NOT send the public key via email or support ticket).

7. To do the File Transfer, fill out the form here and click Continue. Then select Recipient: support and upload the public key file you saved earlier in Notepad.

8. Jostle will then set up your SFTP account and folders and place a dummy of the setup.txt file in the config folder. When it's ready you'll receive a confirmation email along with a Username (this will most likely be the customer name, but all in lower case).

 

Part 2 - Completing setup

1. After receiving your confirmation email and Username, open WinSCP, and on the Login screen, verify the following settings: File protocol: SFTP and Port number:22.

2. In the Host name field enter: sftp.jostle.us. Then enter your Username and the password you created when you saved the private key. Then click “Advanced”.

SF08b.png

 

3. Select "Authentication" from the category tree on the left, then go to the Private key file field and use the browse button to locate and add the private key file (.ppk) you saved earlier. Click OK.

SF09b.png

 

4. Return to the Login screen and click the Login button to connect to your account on the SFTP server. Update the contents of the setup.txt file (in the config folder) and then begin testing.

 

2. SFTP server: Files and folder structure

Once you have access to the SFTP server you will see 3 folders that are dedicated to you. These folders are:

  • config – needs to contain configuration information (see Step 3)
  • input – this is where you will place your data files
  • results – this is where the log and the output files are placed

The name of the input file determines the type of upload as per the following formats:

  • Contributor import = jostleContributors<>.csv 
  • Contributor deletion = deleteContributors<>.csv
  • Teams import = uploadTeams<>.csv
  • Chart import = uploadChart_<category>_<chart>.csv
  • Mapped chart import = mappedChart_<category>_<chart>.csv
  • Contributor photo import = jostlePhotos<>.zip
  • Contributor export = downloadContributors<>
  • Flat team = flatTeam_<category>_<chart>.csv will generate a flat team from a simple list of usernames.
  • Reports = downloadReport<>.<> will download the report requested in the file.

A special utility is in place such that any file whose name contains with "mappings" or is exactly "setup.txt" will be copied from the input folder to the config folder.

NOTES

  • The ‘<>’ portion is optional (except for Chart imports). If this is included, then the suffix will be appended to the results files name. If it is not included then the results files will be appended with the date & time.
  • The actual file contents are identical to those used on the matching import pages in Jostle under the Administration settings (which also provides a quick way to test your files).
  • File names are case sensitive.

3. How to create the input file (CSV file)

File types

Contributor import

You will need to create a CSV file containing a list of the Contributors to import. Use our pre-formatted template to get started.  More info about the fields here.

Also, a mappings.csv file may be used. If re-mapping is not required then no file is needed, otherwise:

  • A mappings.csv file containing 2 columns is placed in the config folder.
  • The import will do an exact match between the data in the first column with the headings in your file and will map them to the normal Jostle heading found in the second column.
  • For a teams upload it will attempt to exact match and then it will proceed to match remaining columns to the normal Jostle headings.
  • If you do have a mappings.csv file then there must be an entry in every column in your import that you wish to have processed. Extra rows in the mappings file will have no affect. 
  • If you are using the mappings.csv file template then we recommend that you do not edit any of the entries in the second column.

NOTE—for a Contributor import, if a mappings file is used then only columns defined in the mappings file will be processed (so if you don't map the 4 required columns it will skip the lines in your input file). 

Contributor deletion

You will need to have a CSV file containing a list of the Contributors to delete.  It should have 1 column, with Username appearing at the top, and the list of Usernames (in email address format) following in the rows below.

Team import

You need to create a CSV file with 4 columns (username, supervisor username, role name, team name). Username and supervisor username are required. Use our pre-formatted template to get started.

Chart import

Unlike the Team import, the Chart import allows the creation or updating of any Teams chart. Key points to keep in mind:

  • The file name is used to specify both the Category and the Chart name. The Category must already exist.
  • If a chart with the specified name exists then it will be replaced, otherwise a new Chart will be created in the specified Category.
  • At present in the Teams view, the Chart name is defined as the top most team in a Chart so when creating a new Chart it will appear using the team name specified in the file, not the name provided in the file name, thus it is easiest if these are kept the same. 

NOTE—if you wish to update an existing team then you must have the <chart> section of the file name an exact match to the Chart name in Teams (which means it must also be an exact match to the team name of the topmost team in the file.)

You need to create a CSV file with 4 columns (username, supervisor username, role name, team name). Username and supervisor username are required. Refer to the "Importing a hierarchy into a Chart" section of this article for more details and then use our pre-formatted template to get started. 

Also, a teams_mappings.csv file may be used, which will function similarly to the mappings.csv for Contributor imports:

  • As with the mappings.csv, the teams_mappings.csv must be placed in the config folder.
  • On the teams_mappings.csv, use the column on the left to enter your user values (the column on the right is for Jostle’s use).

TIP: for Chart uploads you cannot add arbitrary text to the file name, as the "_" characters are used to define the Category and Chart Name (these elements can contain blanks).

Mapped chart import

The mappedChart variant allows either or both of the Username or Supervisor columns to contain a unique identifier which will be used to obtain the actual username from a separate employee_mappings.csv file that has been placed in the config folder.

The employee_mappings.csv file needs to contain 2 columns titled id and username, with the "id" column containing whatever unique identifier (e.g. employee number) you want to use in the mappedChart CSV file (any additional columns in the file will be ignored).

NOTES

  • You must have one row in the employee_mappings.csv file for each id you use in the mappedChart file.
  • If you are using a mapping for the username, the column in the mappedChart file needs to be titled "userid" instead of username
  • If you are mapping for the supervisor name then the title should be "supervisorId" instead of "supervisorName".

Contributor photos import

Assemble a zip file that contains a separate image for each Contributor photo. The images can be in .jpg, .png or .gif format and can be at any reasonable resolution.

To map the images to Contributors, the file name for each image needs to be either of the following:

  • The Contributor's Username.  (e.g. bob.smith@abc.com.jpg)
  • The Contributor's first name and last name separated by space (e.g. Bob Smith.jpg)

NOTE—some users may have a 500 MB limit on the maximum file size that can be uploaded, so a zip file under 500 MB is recommended.

Downloads

When a file whose name starts with downloadContributors is detected in the input folder the current contributor file will be placed in the results folder and and email will be sent to the designated email address(es).

When a file whose name starts with downloadReports is detected then the contents of the file is used to determine which report is being requested. The file content should be a simple piece of json content following this pattern:

{"type":<type>,"days":"int","encoding":"UTF-8"}

<type> is required  and is one of:

  • "Logins" , "NotLoggedIn", "LastLoggedIn", or "InvitedNotLoggedIn" which align to with the similarly named reports available at Admin Settings>Login Reports. e.g.: {"type":"LastLoggedIn","days":"30","encoding":"UTF-8"}
  • "EngagementMetrics" which aligns with the report available at Admin Settings>Download platform metrics. 
  • "PublishedNews" or "ArchivedNews" which correspond to the reports available in the Manage News tables. e.g.: {"type":"ArchivedNews"}
  • "ActivityMetrics" which corresponds to the report available from the gear in Activity. This supports two optional parameters startDate and endDate (mmm dd yyyy format) that match the date picker in the UI
    e.g.: {"type":"ActivityMetrics","startDate":"Jan 01 2019","endDate":"Jan 30 2019"}

Days is required for the Not Logged In Report and needs to be an integer >=1 (the UI allows 30, 60 or 90 to be selected), and is ignored for all other reports.

Encoding is optional. If set to "UTF-8" then it is the same as checking that box in the UI.

4. General setup

In ALL cases, the config folder needs to contain a file called setup.txt (Jostle creates a template of this file for you). It contains:

 {'datacenter':'<dc>','username':'yourusername','password':'yourpassword'}

You just need to replace yourusername with the username and yourpassword with the password for a Jostle account in your instance. (Jostle will have pre-populated the file with <dc> replaced by the name of your datacentre.)

NOTE—the username and password should be that of a Jostle account with System Administrator access. It cannot be a SSO-managed account or Google-managed account. (Recommended best practice is to setup a dedicated account for this task.)

Optional elements

Upload alerts

You can have an email sent upon failed uploads and/or on successful uploads. This is done by specifying the email addresses in the setup file as follows:

{'datacenter':'<dc>','username':'<name>','password': '<pwd>',
'failureEmail':'support@jostle.me', 'successEmail':'my.email@mycompany.com'}

You can specify one or both email addresses or none. In both cases you can specify more than one email by separating the addresses with a comma. (e.g. 'failureEmail':'support@jostle.me,my.email@mycompany.com'). The email will include the contents of the log.txt file. (To diagnose issues with email addresses you will need to review the contents of log.txt in the results folder.)

UTF-8 encoding

You can specify UTF-8 encoding by placing 'encoding':'UTF-8' in the file.

{'datacenter':'<dc>','username':'<name>','password': '<pwd>', 'encoding':'UTF-8'}

Date format

You can specify the date format  for contributor imports by placing 'encoding':'dateFormat' in the file. The default format is MM/DD/YYYY, other choices are DD/MM/YYYY and YYYY/MM/DD.

{'datacenter':'<dc>','username':'<name>','password': '<pwd>', 'dateFormat':'DD/MM/YY'}

Contributors import

For the contributors import case there are 2 additional optional elements that can be added to the setup file:

{'datacenter':'<dc>','username':'<name>','password':'<pwd>','method':'<method>','language':<language>,'blanksOverwriteValues':'true'} 

Where <method> is one of:

  • INVITE_NONE – no one
  • INVITE_NEW – new contributors
  • INVITE_NOT_ACTIVATED - new contributors and those not yet invited
  • INVITE_NOT_ACTIVATED_AND_EXPIRED - new contributors, those not yet invited
  • INVITE_CUSTOM - as specified in import file

In the case of organizations using SSO or Google, the INVITE_NEW, INVITE_NOT_ACTIVATED, and INVITE_NOT_ACTIVATED_AND_EXPIRED methods will default to SSO (or Google). If you wish to force the invitation to be for a Jostle login (assuming it is enabled), then the file name needs to be changed to jostleContributorsInvitepwd<>.csv

The <language> value is used to set the language used in the invitation email and must be one of the languages supported by Jostle, e.g. 'ENGLISH','FRENCH',or 'GERMAN'. (You can contact support@jostle.me to ask what languages are supported.) If this parameter is not included, English will be used.

The 'blanksOverwriteValues':'true' is used to overwrite/reset any previously entered data. Any blank field with a proper column heading will be overwritten with a blank value. Column headings missing from the CSV will be completely ignored - not "blanked" out. You can also include "NoBlanks" in a file name and blanksOverwriteValues will be set true for that one upload.

5. When will Jostle process the upload?

Each time a file is placed in the /input folder a program is initiated that will run the Jostle scripts that communicate the data to our servers. The output from the scripts will be placed in the /results folder, and the original file moved to the /processed folder when the processing completes. A log file (log_datetime.txt) that is used to contain the output from the job is placed in the /results folder. Output CSV files (like the ones that you would download in the UI) are saved in the /results folder, either post-pended with a date or the file suffix as described above. 

The time to process depends on the file size. We suggest waiting 10 minutes for large files prior to checking for the changes (you can gauge the uploading time by uploading a similar file in the UI). Do not modify or overwrite a file in the input folder while it is being processed.

Please note that the date-time stamps are all in UTC (GMT).

6. Scripting/Automation

WinSCP references:

To set the Host Key for your scripts or code:

  • When scripting you can specify the expected fingerprint using  -hostkey switch of an open command. 
  • With .NET assembly, use SessionOptions.SshHostKeyFingerprint property

NOTE—using “*” for hostkey is not best practice because your script would be trusting any key, which could make you susceptible to man-in-the-middle attack.

You can get Jostle's Host Key by connecting to our SFTP server manually with WinSCP and then navigating to Session > Server/protocol information 

Server and Protocol Information Dialog

 

7. Attachments

Was this article helpful?
1 out of 1 found this helpful
Have more questions? Submit a request

0 Comments

Please sign in to leave a comment.