SYS_CONNECTION_LOG - Amazon Redshift
Table columnsSample queries
Services or capabilities described in Amazon Web Services documentation might vary by Region. To see the differences applicable to the China Regions, see Getting Started with Amazon Web Services in China (PDF).

SYS_CONNECTION_LOG

Logs authentication attempts and connections and disconnections.

SYS_CONNECTION_LOG is visible only to superusers. For more information, see Visibility of data in system tables and views.

Table columns

Column name Data type Description
event character(50) Connection or authentication event.
record_time timestamp Time the event occurred.
remote_host character(45) Name or IP address of remote host.
remote_port character(32) Port number for remote host.
session_id integer Process ID associated with the statement.
database_name character(50) Database name.
user_name character(50) Username.
auth_method character(32) Authentication method.
duration integer Duration of connection in microseconds.
ssl_version character(50) Secure Sockets Layer (SSL) version.
ssl_cipher character(128) SSL cipher.
mtu integer Maximum transmission unit (MTU).
ssl_compression character(64) SSL compression type.
ssl_expansion character(64) SSL expansion type.
iam_auth_guid character(36) The IAM authentication ID for the CloudTrail request.
application_name character(250) The initial or updated name of the application for a session.
driver_version character(64) The version of ODBC or JDBC driver that connects to your Amazon Redshift cluster from your third-party SQL client tools.
os_version character(64) The version of the operating system that is on the client machine that connects to your Amazon Redshift cluster.
plugin_name character(32) The name of the plugin used to connect to your Amazon Redshift cluster.
protocol_version integer The internal protocol version that the Amazon Redshift driver uses when establishing its connection with the server. The protocol versions are negotiated between the driver and server. The version describes the features available. Valid values include:

  • 0 (BASE_SERVER_PROTOCOL_VERSION)

  • 1 (EXTENDED_RESULT_METADATA_SERVER_PROTOCOL_VERSION) – To save a round trip per query, the server sends extra result set metadata information.

  • 2 (BINARY_PROTOCOL_VERSION) – Depending on the data type of the result set, the server sends data in binary format.

  • 3 (EXTENDED2_RESULT_METADATA_SERVER_PROTOCOL_VERSION) – The server sends case sensitivity (collation) information of a column.
global_session_id character(36) The globally unique identifier for the current session. The session ID persists through node failure restarts.

Sample queries

To view the details for open connections, run the following query.

select record_time, user_name, database_name, remote_host, remote_port
from sys_connection_log
where event = 'initiating session'
and session_id not in 
(select session_id from sys_connection_log
where event = 'disconnecting session')
order by 1 desc;

record_time         | user_name   | database_name   | remote_host   | remote_port                      
--------------------+-------------+-----------------+---------------+---------------------------------
2014-11-06 20:30:06 | rdsdb       | dev             | [local]       |                            
2014-11-06 20:29:37 | test001     | test            | 10.49.42.138  | 11111                           
2014-11-05 20:30:29 | rdsdb       | dev             | 10.49.42.138  | 33333                                                 
2014-11-05 20:28:35 | rdsdb       | dev             | [local]       |  
(4 rows)

The following example reflects a failed authentication attempt and a successful connection and disconnection. 

select event, record_time, remote_host, user_name
from sys_connection_log order by record_time;            

            event      |         record_time        |  remote_host  | user_name                      
-----------------------+----------------------------+---------------+---------
authentication failure | 2012-10-25 14:41:56.96391  | 10.49.42.138  | john                                              
authenticated          | 2012-10-25 14:42:10.87613  | 10.49.42.138  | john                                              
initiating session     | 2012-10-25 14:42:10.87638  | 10.49.42.138  | john                                              
disconnecting session  | 2012-10-25 14:42:19.95992  | 10.49.42.138  | john                                              
(4 rows)

The following example shows the version of the ODBC driver, the operating system on the client machine, and the plugin used to connect to the Amazon Redshift cluster. In this example, the plugin used is for standard ODBC driver authentication using a login name and password.

select driver_version, os_version, plugin_name from sys_connection_log;
                
driver_version                          |  os_version                       | plugin_name
----------------------------------------+-----------------------------------+--------------------
Amazon Redshift ODBC Driver 1.4.15.0001 | Darwin 18.7.0 x86_64              | none
Amazon Redshift ODBC Driver 1.4.15.0001 | Linux 4.15.0-101-generic x86_64   | none

The following example shows the version of the operating system on the client machine, the driver version, and the protocol version.

select os_version, driver_version, protocol_version from sys_connection_log;
                
os_version                      |  driver_version              | protocol_version
--------------------------------+------------------------------+--------------------
Linux 4.15.0-101-generic x86_64 | Redshift JDBC Driver 2.0.0.0 | 2
Linux 4.15.0-101-generic x86_64 | Redshift JDBC Driver 2.0.0.0 | 2 
Linux 4.15.0-101-generic x86_64 | Redshift JDBC Driver 2.0.0.0 | 2