sftp

Allows you to browse the file system on the SFTP server or transmit the input records to the file.

Syntax

sftp PROFILE SUBCOMMAND [OPTIONS] PATH
Required Parameters
PROFILE
SFTP connection profile. You can configure the profile in the web console.
SUBCOMMAND
Command to be executed in the sftp session: ls, cat, put
  • ls: Lists the files in the path specified by PATH on the server.
  • cat: Loads the content of the file in the path specified by PATH from the server and assigns it in the line field.
  • put: Transmits the records ​​of the fields specified by the fields option to the SFTP server as a file. The file is created in the path specified by PATH.
PATH
Path to a directory or file. If you use a wildcard (*) in the file name, you can retrieve all files containing a specific string pattern in the file name (e.g. /var/log/httpd/* ).
  • When SUBCOMMAND is ls, you can enter either a directory or a file path.
  • When SUBCOMMAND is cat, you can enter only the file path.
  • When SUBCOMMAND is put, you can enter only the file path.
Optional Parameters

The options for each SUBCOMMAND are as follows:

Optionscatputls
appendO--
encodingOO-
fields-O-
formatOO-
limitO--
maxsession-O-
multisession-O-
offsetO--
overwrite-O-
partition-O-
append=BOOL
Option to enable appending data to the end of the file specified as PATH (default: f).
  • t: Appends the field records to the end of the file specified as PATH. If the file does not exist, the file is created. You cannot set this option to t when the overwrite=t.
  • f: NOT append the field records to the end of the file specified as PATH. The query fails if the file exists.
Caution
When 'append=t', always keep the list order of the 'fields' option the same so that data can be consistent.
encoding=CHARSET

Character set (default: utf-8). Use the preferred MIME name or aliases registered in the following document: https://www.iana.org/assignments/character-sets/character-sets.xhtml

fields=FIELD,...

Fields to be transmitted to the FTP server (default: line). Use comma (,) without any leading or trailing whitespaces as a separator. If there is no line field or the specified field is empty, it is replaced with a hyphen symbol (-) in the output to indicate the field is empty.

format=FORMAT

File format (csv, json, tsv, default: plain text).

  • csv or tsv
    • When SUBCOMMAND is cat, the first line is considered a regular record. Field name (column header) is assigned in the form columnN (N is a number starting from 0)
    • When SUBCOMMAND is put, field names (column header) are assigned with the field names specified by the fields option.
  • json
    • When SUBCOMMAND is cat, it parses the file into the records of key-value pairs line by line. The keys are used as field names, and the values are used as field values.
    • When SUBCOMMAND is put, it transmits the records consisting of the key-value pairs of the fields specified by the fields option. If the fields option is not specified, records consisting of all field values are transmitted.
  • Not specified (plain text)
    • When SUBCOMMAND is cat, it loades the values to the line field line by line.
    • When SUBCOMMAND is put, it transmits the file in a text format. Values are separated by tab characters in plain text, and empty values (nulls) are replaced with hyphens (-).
limit=INT

Number of records to be output when importing files from the SFTP server (default: unlimited).

maxsession=INT

Maximum number of sessions when multisession=t (default: 1). If you specify this option without checking whether to use multi-session, the query fails. No matter how large the number is, as many sessions are opened as the number of MaxSessions specified in the sshd_config file.

multisession=BOOL

Option to enable multi-session (default: f). Enable this option after testing because it takes longer to open additional sessions and may result in lower performance than not using it.

  • t: Enables multi-session.
  • f: Disables multi-session.
offset=INT

Number of rows you want to skip when importing files from the SFTP server (default: 0).

overwrite=BOOL

Option to enable overwriting the file specified as PATH, if it exists (default: f).

  • t: Overwrites the file specified as PATH, if it exists. You cannot set this option to t when the append=t.
  • f: NOT overwrite the file specified as PATH, if it exists. The query fails if the file exists.
partition=BOOL

Option to enable macro in the PATH (default: f).

  • t: Enables macro.
  • f: Disables macro.

You can specify PATH to change the directory and file path over time using a macro when partition=t. The available macros are {logtime:FMT} and {now:FMT}. For input examples, refer to Usage #6.

  • {logtime:FMT}: Names the directory or file based on the log occurrence time.
  • {now:FMT}: Names the directory or file based on the current time.

If you specify a partition option and do not use a macro on the path, the query fails.

Usage

  1. Retrieve remote directory files by accessing SSH with an srv profile.

    sftp srv ls /
    

    Each query result field has the following meanings:

    • type (string): dir when it is a directory, file when it is a file
    • is_link (boolean): Whether it is a symbolic link
    • name (string): File name
    • file_size (integer): File size, 0 when it is a directory
    • modified_at (date): Last modified time
    • uid (integer): Owner ID
    • gid (integer): Owned group ID
    • perms (string): File permission information
  2. Read the first 5 rows of the /logpresso.sh file by accessing the srv profile.

    sftp srv cat limit=5 /logpresso.sh
    
  3. Output only UnloadedClassCount of LoadedClassCount among the JMX class loading logs to the /tmp/class.txt file.

    table classloading 
    | sftp srv put
      fields=UnloadedClassCount,LoadedClassCount
      /tmp/class.txt
    
  4. Output the JMX class loading log to the /tmp/class.json file.

    table classloading | sftp srv put format=json /tmp/class.json
    
  5. Output LoadedClassCount, UnloadedClassCount, and TotalLoadedClassCount among the JMX class loading logs to the /tmp/class.csv file.

    table classloading 
    | sftp srv put
      format=csv
      fields=LoadedClassCount,UnloadedClassCount,TotalLoadedClassCount
      /tmp/class.csv
    
  6. Output the LoadedClassCount, UnloadedClassCount, and TotalLoadedClassCount items among the JMX class loading logs to a JSON file. As the file is stored, the year, month and day based on the log time are used as the name of the directory (yyyy/MM/dd) and the hour and minute based on the current time are used as the name of the file (HHmm)

    table classloading 
    | sftp srv put
      format=json
      partition=t
      fields=LoadedClassCount,UnloadedClassCount,TotalLoadedClassCount
      {logtime:/yyyy/MM/dd/}{now:HHmm}.txt