quick links
support
|
Transfer database logging schema
for Aspera Enterprise Server on all platforms – version 2.5.2
1. Introduction
Aspera Enterprise Server can log transfers to a MySQL server with a database containing the proper tables. The default name for this database is aspera_console, although if desired this can be changed by modifying the <DATABASE> section of the aspera.conf file.
Aspera Management Console creates a database upon installation that the Enterprise Server's database logger can use. This database contains additional tables that are used by the Console application that are not needed just for database logging.
This document covers the tables used by the database logger. The database logger requires MySQL version 4.1.20-1 or higher. Default format for the database should be UTF-8 to allow recording of file names with foreign characters.
2. Database Overview
The following lists this a list of tables used by the database logger. You can jump to the fields section by clicking the table name in the overview section.
|
Database Logger Tables
|
|
fasp_files
|
A single file transferred in a fasp™ session. If a transfer is occurring between two nodes that are both logging to the same database, there will be two records in this table for each file being transferred, one for each side being logged.
|
|
fasp_nodes
|
A node is a single server with Aspera software installed. It must have the database logger configured (in the aspera.conf file) in order to work with the Console
|
|
fasp_rates
|
Initial rates and subsequent rate changes for a single fasp™ session. If a transfer is occurring between two nodes that are both logging to the same database, there may be two records in this table for each rate set/change event, one for each side being logged.
|
|
fasp_sessions
|
One fasp™ transfer session recorded by one node. If a transfer is occurring between two nodes that are both logging to the same database, there will be two records in this table for the transfer, one for each side being logged.
|
3. fasp_files
|
Name
|
MySQL Type
|
Null Allowed?
|
Description
|
|
id
|
int(11)
|
No
|
Sequential integer, unique record id, assigned automatically by MySQL
|
|
logged_from
|
varchar(255)
|
No
|
MySQL user name used by the logger, plus the IP address of the node from MySQL server's point of view. Example: 'logger'@'192.168.1.101'. Although this field is populated and updated by a query from the database logger, the actual value is determined by MySQL, since the logger may not know what its IP address looks like to the database server. This is done by using MySQL's USER() function in the SQL statement.
|
|
node_uuid
|
varchar(36)
|
No
|
Foreign key referring to record in fasp_nodes for the node that logged this session
|
|
session_id
|
varchar(36)
|
No
|
fasp™ session id - for fasp™ 1.x, a 32 bit unsigned integer. Forfasp™ 2.x, a UUID string of format 12345678-1234-1234-1234-123456789abc. In fasp™ 1.x, each side of a single transfer has its own session id. In fasp™ 2.x, both sides (endpoints) of a single transfer use the same session id.
|
|
status
|
varchar(16)
|
No
|
One of the following values (case sensitive): started, completed, paused, error, orphaned, cancelled, willretry.
|
|
created_at
|
datetime
|
No
|
Time/date record created - maintained by Database Logger, not populated automatically by MySQL, but the time is from the database server's point of view - done by using MySQL's NOW() function in SQL statements
|
|
started_at
|
datetime
|
Yes
|
Time/date file started transferring. Might be null if the database server was inaccessible to the logger at the start of the file transfer. Time is from the database server's point of view - done by using MySQL's NOW() function in SQL statements
|
|
stopped_at
|
datetime
|
Yes
|
Time/date file stopped transferring, either successfully or with an error. Null if file is still transferring, or if database server was inaccessible to logger at time of file termination. Time is from the database server's point of view - done by using MySQL's NOW() function in SQL statements
|
|
file_fullpath
|
text
|
No
|
Full, expanded pathname (including file name) from Central's perspective.
|
|
file_index
|
varchar(32)
|
No
|
A unique identifier for each file in a single transfer. A sequential, numerical identifier is recommended, but any unique identifier will do. This field only needs to be unique within the context of a single node and transfer. If both sides of a transfer are being logged, the file_index only has to be unique to each side of the transfer, which is why node_uuid is part of the unique index. The file_index for a file on one side of the transfer might not be the same as the file_index for the same file on the other side of the transfer (the receiving node might number the files differently than the sending node).
|
|
file_basename
|
text
|
No
|
Filename only, no path. Not currently implemented
|
|
source_item
|
text
|
Yes
|
The source item (file or directory) chosen to transfer that caused this particular file to be transferred. If a directory containing more than one file was chosen, then there will be multiple records (one per file in the directory) that all have the same value for this field.
|
|
size
|
bigint(20)
|
No
|
Size of the file in bytes
|
|
start_byte
|
bigint(20)
|
No
|
For resumed transfers, the point in the file at which transfer started. Only applies to files that were previously incompletely transferred
|
|
bytes_written
|
bigint(20)
|
No
|
Number of file bytes written to disk on the receiving machine
|
|
bytes_contig
|
bigint(20)
|
No
|
Number of contiguous file bytes transferred
|
|
bytes_lost
|
bigint(20)
|
No
|
Number of file bytes lost (and retransmitted) due to network packet loss
|
|
usecs
|
bigint(20)
|
No
|
Number of microseconds elapsed since start of file transfer
|
|
checksum
|
varchar(255)
|
Yes
|
Optional checksum for file. Not supplied automatically by fasp™.
|
|
checksum_type
|
varchar(16)
|
Yes
|
String description of checksum method
|
|
err_code
|
int(11)
|
No
|
fasp™ error code for file, 0 if no error.
|
|
err_desc
|
varchar(255)
|
Yes
|
fasp™ error description for file, null if no error.
|
4. fasp_nodes
|
Name
|
MySQL Type
|
Null Allowed?
|
Description
|
|
id
|
int(11)
|
No
|
Sequential integer, unique record id, assigned automatically by MySQL
|
|
created_at
|
datetime
|
No
|
Time/date record created - maintained by Console and Database Logger, not populated automatically by MySQL, but the time is from the database server's point of view - done by using MySQL's NOW() function in SQL statements.
|
|
updated_at
|
datetime
|
No
|
Time/date record last updated - maintained by Console and Database Logger, not populated automatically by MySQL, but the time is from the database server's point of view - done by using MySQL's NOW() function in SQL statements
|
|
node_address
|
varchar(128)
|
No
|
IP address of node. Used to grant permission to the node to access the database.
|
|
node_name
|
varchar(255)
|
No
|
Human-friendly descriptive name for node
|
|
node_uuid
|
varchar(36)
|
Yes
|
Unique id for each node, a randomly generated UUID string of 12345678-1234-1234-1234-123456789abc format, generated by the database logger on first startup. Preserves identity of node even if its IP address is changed. When a node is added using the Console web interface, this field is blank until the database logger contacts it. If the database is on the same machine as the database logger it is not necessary to insert a row for the node first, as long as the logger has been configured with the root user and password for MySQL
|
|
node_bandwidth_cap_in
|
int(11)
|
Yes
|
Inbound virtual link cap for node, in kilobits/second.Not currently implemented.
|
|
node_bandwidth_cap_out
|
int(11)
|
Yes
|
Outbound virtual link cap for node, in kilobits/second.Not currently implemented.
|
|
cluster_id
|
int(11)
|
Yes
|
Cluster that this node is assigned to. May be null, nodes do not have to be assigned to a cluster.
|
|
reported_hostname
|
varchar(255)
|
Yes
|
Hostname of the node. This field is populated and maintained by the database logger. Initially null if node record was added through the Console web interface.
|
|
reported_central_uri
|
varchar(255)
|
Yes
|
URI protocol, address and port that Aspera Central is listening on for SOAP calls. This field is populated and maintained by the database logger. Example: http://0.0.0.0:40001. This URI reflects values specified in the node's aspera.conf file.
|
|
reported_active_sessions
|
int(11)
|
Yes
|
Number of active fasp™ transfer sessions for the node.Not currently implemented
|
|
reported_logger_version
|
varchar(255)
|
Yes
|
Internal version number of the node's database logger software.Not currently implemented
|
|
reported_os
|
varchar(16)
|
Yes
|
Operating system reported by database logger. One of the following values (case sensitive): windows, solaris, mac, linux, freebsd, isilon. Null if node record was added using the Console web interface and the logger has not contacted the database yet.
|
|
reported_os_version
|
varchar(255)
|
Yes
|
Additional os version / platform information reported by database logger. Null if node record was added using the Console web interface and the logger has not contacted the database yet.
|
|
reported_from
|
varchar(255)
|
Yes
|
MySQL user name used by the logger, plus the IP address of the node from MySQL server's point of view. Example: 'logger'@'192.168.1.101'. Although this field is populated and updated by a query from the database logger, the actual value is determined by MySQL, since the logger may not know what its IP address looks like to the database server. This is done by using MySQL's USER() function in the SQL statement.
|
|
reported_at
|
datetime
|
Yes
|
Time/date record last updated by database logger. This field is populated and maintained by the database logger, but the time is from the database server's point of view - done by using MySQL's NOW() function in SQL statements
|
5. fasp_rates
|
Name
|
MySQL Type
|
Null Allowed?
|
Description
|
|
id
|
int(11)
|
No
|
Sequential integer, unique record id, assigned automatically by MySQL
|
|
logged_from
|
varchar(255)
|
No
|
MySQL user name used by the logger, plus the IP address of the node from MySQL server's point of view. Example: 'logger'@'192.168.1.101'. Although this field is populated and updated by a query from the database logger, the actual value is determined by MySQL, since the logger may not know what its IP address looks like to the database server. This is done by using MySQL's USER() function in the SQL statement.
|
|
node_uuid
|
varchar(36)
|
No
|
Foreign key referring to record in fasp_nodes for the node that logged this session
|
|
session_id
|
varchar(36)
|
No
|
fasp™ session id - for fasp™ 1.x, a 32 bit unsigned integer. For fasp™ 2.x, a UUID string of format 12345678-1234-1234-1234-123456789abc. In fasp™ 1.x, each side of a single transfer has its own session id. In fasp™ 2.x, both sides (endpoints) of a single transfer use the same session id.
|
|
applied_at
|
datetime
|
No
|
Time/date rate change was applied. Time is from the database server's point of view - done by using MySQL's NOW() function in SQL statements
|
|
policy
|
varchar(16)
|
No
|
Transfer policy. One of the following values (case sensitive): Fixed, Adaptive, Trickle
|
|
target_rate
|
int(11)
|
No
|
Target rate in kilobits per second.
|
|
min_rate
|
int(11)
|
No
|
Requested minimum rate in kilobits per second.
|
|
bw_cap
|
int(11)
|
No
|
Maximum allowed rate in kilobits per second.
|
|
priority
|
varchar(255)
|
Yes
|
Transfer priority.
|
6. fasp_sessions
|
Name
|
MySQL Type
|
Null Allowed?
|
Description
|
|
id
|
int(11)
|
No
|
Sequential integer, unique record id, assigned automatically by MySQL
|
|
logged_from
|
varchar(255)
|
No
|
MySQL user name used by the logger, plus the IP address of the node from the database server's point of view. Example: 'logger'@'192.168.1.101'. Although this field is populated and updated by a query from the database logger, the actual value is determined by MySQL, since the logger may not know what its IP address looks like to the database server. This is done by using MySQL's USER() function in the SQL statement.
|
|
node_uuid
|
varchar(36)
|
No
|
Foreign key referring to record in the fasp_nodes table for the node that logged this session
|
|
session_id
|
varchar(36)
|
No
|
fasp™ session id - for fasp™ 1.x, a 32 bit unsigned integer. For fasp™ 2.x, a UUID string of format 12345678-1234-1234-1234-123456789abc. In fasp™ 1.x, each side of a single transfer has its own session id. In fasp™ 2.x, both sides (endpoints) of a single transfer use the same session id.
|
|
status
|
varchar(16)
|
No
|
One of the following values (case sensitive): started, completed, paused, error, orphaned, cancelled, willretry . The 'orphaned' status is assigned when a session is found in the database that has status set to 'started' or 'paused', but the node itself reports that there is no active session with the specified session id. This may happen if the database server was inaccessible to the logger at time of termination. The 'paused' status means that target rate for the session has been set to zero. The 'unknown' status is assigned when a session is found in the database with status of 'started' or 'paused' but the node cannot be reached to query current session status. When connection to the node is restored, status will be updated to either 'started', 'paused', or 'orphaned'.
|
|
created_at
|
datetime
|
No
|
Time/date record created - maintained by Database Logger, not populated automatically by MySQL, but the time is from the database server's point of view - done by using MySQL's NOW() function in SQL statements
|
|
started_at
|
datetime
|
Yes
|
Time/date transfer started. Might be null if the database server was inaccessible to the logger at the start of a transfer. Time is from the database server's point of view - done by using MySQL's NOW() function in SQL statements
|
|
stopped_at
|
datetime
|
Yes
|
Time/date transfer stopped, either successfully or with an error. Null if transfer is active, or if database server was inaccessible to logger at time of termination. Time is from the database server's point of view - done by using MySQL's NOW() function in SQL statements
|
|
user
|
varchar(128)
|
Yes
|
Ssh user for transfer. Can be null if session failed to start. In the future, this may also be null for servers configured for open access.
|
|
cookie
|
text
|
Yes
|
extra user defined data attached to transfer
|
|
token
|
text
|
Yes
|
extra application defined data attached to transfer - used by Aspera Faspex to hold authentication token for a specific transfer request
|
|
initiator
|
varchar(16)
|
No
|
One of the following values (case sensitive): Local, Remote. If the transfer was initiated by the logging node, then initiator=Local, else initiator=Remote.
|
|
operation
|
varchar(16)
|
No
|
- If initiator is local and direction is in, the operation is Download.
- If initiator is local and direction is out, the operation is Upload.
- If initiator is remote and direction is in, the operation is Upload.
- If initiator is remote and direction is out, the operation is Download.
|
|
server_addr
|
varchar(255)
|
Yes
|
Non-initiator's IP address or network name - blank on server side in fasp™ 1.x. The transfer initiator is always considered the CLIENT. The non-initiator is always considered the SERVER.
|
|
server_sshport
|
int(11)
|
No
|
Ssh port used to login to the non-initiator (server).
|
|
server_faspport
|
int(11)
|
No
|
UDP port used for the transfer on the non-initiator (server) side.
|
|
client_addr
|
varchar(255)
|
Yes
|
Initiator's (client's) IP address or network name
|
|
client_faspport
|
int(11)
|
No
|
UDP port used for the transfer on the initiator (client) side.
|
|
cipher
|
varchar(16)
|
No
|
Encryption cipher used for the transfer. Currently one of the following values (case sensitive): None, AES128.
|
|
dest_path
|
text
|
Yes
|
Initiator's (client's) destination path for the entire session - full local system path for download, remote path relative to docroot for upload. Blank on the non-initiator's (server's) side.
|
|
files_complete
|
int(11)
|
No
|
Number of files successfully transferred
|
|
files_failed
|
int(11)
|
No
|
Number of files that failed to transfer
|
|
bytes_written
|
bigint(20)
|
No
|
Number of bytes written to disk on inbound side
|
|
bytes_transferred
|
bigint(20)
|
No
|
Number of bytes actually sent over network
|
|
bytes_lost
|
bigint(20)
|
No
|
Number of bytes lost (and retransmitted) due to network packet loss
|
|
usecs
|
bigint(20)
|
No
|
Microseconds elapsed since start of transfer
|
|
temp_prev_usecs
|
bigint(20)
|
Yes
|
Internal field used by Console for current rate calculation
|
|
temp_prev_bytes_transferred
|
bigint(20)
|
Yes
|
Internal field used by Console for current rate calculation
|
|
temp_bitrate
|
bigint(20)
|
Yes
|
Internal field used by Console for current rate calculation
|
|
temp_bitrate_calculated_at
|
datetime
|
Yes
|
Internal field used by Console for current rate calculation
|
|
network_delay
|
Int(11)
|
Yes
|
Network delay in milliseconds. Null for fasp™ 1.x, only available for fasp™ 2.x transfers
|
|
err_code
|
int(11)
|
No
|
fasp™ error code for session, 0 if no error
|
|
err_desc
|
varchar(255)
|
Yes
|
fasp™ error description for session, null if no error
|
|
source_paths
|
text
|
Yes
|
Source file paths. Not all source paths are recorded due to buffer limits.
|
|
args_attempted
|
int(11)
|
Yes
|
Number of command-line arguments being assigned.
|
|
args_completed
|
int(11)
|
Yes
|
Number of command-line arguments being completed.
|
|
paths_attempted
|
int(11)
|
Yes
|
File or directory paths traversed.
|
|
paths_failed
|
int(11)
|
Yes
|
File or directory paths with errors
|
|
paths_irreg
|
int(11)
|
Yes
|
File or directory paths with special files.
|
|
paths_excluded
|
int(11)
|
Yes
|
File or directory paths excluded due to exclude settings.
|
|
dirscans_completed
|
int(11)
|
Yes
|
Directory scans completed.
|
|
filescans_completed
|
int(11)
|
Yes
|
File scans completed.
|
|
mkdirs_attempted
|
int(11)
|
Yes
|
Attempts to create directories.
|
|
mkdirs_failed
|
int(11)
|
Yes
|
Failed directory creations.
|
|
mkdirs_passed
|
int(11)
|
Yes
|
Successful directory creations.
|
|
files_attempted
|
int(11)
|
Yes
|
File transfers attempted.
|
|
files_skipped
|
int(11)
|
Yes
|
File transfers skipped.
|
|
fallback_protocol
|
varchar(16)
|
Yes
|
Alternate transport protocol.
|
|
transport
|
varchar(16)
|
Yes
|
Transport protocol.
|
|
retry_timeout
|
int(11)
|
Yes
|
Retry timeout specified.
|
|
