STL_CONNECTION_LOG
Logs authentication attempts and connections and disconnections.
STL_CONNECTION_LOG is visible only to superusers. For more information, see Visibility of data in system tables and views.
Some or all of the data in this table can also be found in the SYS monitoring view SYS_CONNECTION_LOG. The data in the SYS monitoring view is formatted to be easier to use and understand. We recommend that you use the SYS monitoring view for your queries.
Table columns
| Column name | Data type | Description |
|---|---|---|
| event | character(50) | Connection or authentication event. |
| recordtime | timestamp | Time the event occurred. |
| remotehost | character(45) | Name or IP address of remote host. |
| remoteport | character(32) | Port number for remote host. |
| pid | integer | Process ID associated with the statement. |
| dbname | character(50) | Database name. |
| username | character(50) | User name. |
| authmethod | character(32) | Authentication method. |
| duration | integer | Duration of connection in microseconds. |
| sslversion | character(50) | Secure Sockets Layer (SSL) version. |
| sslcipher | character(128) | SSL cipher. |
| mtu | integer | Maximum transmission unit (MTU). |
| sslcompression | character(64) | SSL compression type. |
| sslexpansion | character(64) | SSL expansion type. |
| iamauthguid | 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. |
| os_version | character(64) | The version of the operating system that is on the client machine that connects to your Amazon Redshift cluster. |
| 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. |
| 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 is negotiated between the driver and server.
The version describes the features available.
Valid values include:
|
| sessionid | character(36) | The globally unique identifier for the current session. The session ID persists through node failure restarts. |
| compression | character(16) | The compression algorithm in use for the connection. |
Sample queries
To view the details for open connections, run the following query.
select recordtime, username, dbname, remotehost, remoteport from stl_connection_log where event = 'initiating session' and pid not in (select pid from stl_connection_log where event = 'disconnecting session') order by 1 desc; recordtime | username | dbname | remotehost | remoteport --------------------+-------------+------------+---------------+------------ 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, recordtime, remotehost, username from stl_connection_log order by recordtime; event | recordtime | remotehost | username -----------------------+---------------------------+--------------+--------- 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 stl_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 stl_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
