system.session_log

System table containing information about all successful and failed login and logout events.

Querying in ClickHouse Cloud

The data in this system table is held locally on each node in ClickHouse Cloud. Obtaining a complete view of all data, therefore, requires the clusterAllReplicas function. See here for further details.

Description

Contains information about all successful and failed login and logout events.

Columns

• hostname (LowCardinality(String)) — Hostname of the server executing the query.
• type (Enum8(‘LoginFailure’ = 0, ‘LoginSuccess’ = 1, ‘Logout’ = 2)) — Login/logout result. Possible values: LoginFailure — Login error. LoginSuccess — Successful login. Logout — Logout from the system.
• auth_id (UUID) — Authentication ID, which is a UUID that is automatically generated each time user logins.
• session_id (String) — Session ID that is passed by client via HTTP interface.
• event_date (Date) — Login/logout date.
• event_time (DateTime) — Login/logout time.
• event_time_microseconds (DateTime64(6)) — Login/logout starting time with microseconds precision.
• user (Nullable(String)) — User name.
• auth_type (Nullable(Enum8(‘NO_PASSWORD’ = 0, ‘PLAINTEXT_PASSWORD’ = 1, ‘SHA256_PASSWORD’ = 2, ‘DOUBLE_SHA1_PASSWORD’ = 3, ‘LDAP’ = 4, ‘KERBEROS’ = 5, ‘SSL_CERTIFICATE’ = 6, ‘BCRYPT_PASSWORD’ = 7, ‘SSH_KEY’ = 8, ‘HTTP’ = 9, ‘JWT’ = 10, ‘SCRAM_SHA256_PASSWORD’ = 11, ‘NO_AUTHENTICATION’ = 12))) — The authentication type.
• profiles (Array(LowCardinality(String))) — The list of profiles set for all roles and/or users.
• roles (Array(LowCardinality(String))) — The list of roles to which the profile is applied.
• settings (Array(Tuple(LowCardinality(String), String))) — Settings that were changed when the client logged in/out.
• client_address (IPv6) — The IP address that was used to log in/out.
• client_port (UInt16) — The client port that was used to log in/out.
• interface (Enum8(‘TCP’ = 1, ‘HTTP’ = 2, ‘gRPC’ = 3, ‘MySQL’ = 4, ‘PostgreSQL’ = 5, ‘Local’ = 6, ‘TCP_Interserver’ = 7, ‘Prometheus’ = 8, ‘Background’ = 9)) — The interface from which the login was initiated.
• client_hostname (String) — The hostname of the client machine where the clickhouse-client or another TCP client is run.
• client_name (String) — The clickhouse-client or another TCP client name.
• client_revision (UInt32) — Revision of the clickhouse-client or another TCP client.
• client_version_major (UInt32) — The major version of the clickhouse-client or another TCP client.
• client_version_minor (UInt32) — The minor version of the clickhouse-client or another TCP client.
• client_version_patch (UInt32) — Patch component of the clickhouse-client or another TCP client version.
• failure_reason (String) — The exception message containing the reason for the login/logout failure.
• certificate_subjects (Array(LowCardinality(String))) — The list of subjects (Common Name and Subject Alternative Names) of the TLS client certificate presented on the connection, in the form ‘CN:…’ / ‘SAN:…’. Empty if no certificate was presented.
• certificate_serial (LowCardinality(String)) — Serial number of the TLS client certificate. Empty if no certificate was presented.
• certificate_issuer (LowCardinality(String)) — Issuer of the TLS client certificate. Empty if no certificate was presented.
• certificate_not_before (Nullable(DateTime64)) — Time from which the TLS client certificate is valid. NULL if no certificate was presented.
• certificate_not_after (Nullable(DateTime64)) — Time after which the TLS client certificate expires. NULL if no certificate was presented.

Example

Query

SELECT * FROM system.session_log LIMIT 1 FORMAT Vertical;

Response

Row 1:
──────
hostname:                clickhouse.eu-central1.internal
type:                    LoginSuccess
auth_id:                 45e6bd83-b4aa-4a23-85e6-bd83b4aa1a23
session_id:
event_date:              2021-10-14
event_time:              2021-10-14 20:33:52
event_time_microseconds: 2021-10-14 20:33:52.104247
user:                    default
auth_type:               PLAINTEXT_PASSWORD
profiles:                ['default']
roles:                   []
settings:                [('load_balancing','random'),('max_memory_usage','10000000000')]
client_address:          ::ffff:127.0.0.1
client_port:             38490
interface:               TCP
client_hostname:
client_name:             ClickHouse client
client_revision:         54449
client_version_major:    21
client_version_minor:    10
client_version_patch:    0
failure_reason:
certificate_subjects:    []
certificate_serial:
certificate_issuer:
certificate_not_before:  ᴺᵁᴸᴸ
certificate_not_after:   ᴺᵁᴸᴸ