Skip to content

Clients

The client-related virtual tables provide visibility into connected CQL clients and currently executing queries.

clients

Shows all currently connected CQL clients with connection details.

Schema

VIRTUAL TABLE system_views.clients (
    address inet,
    port int,
    client_options frozen<map<text, text>>,
    connection_stage text,
    driver_name text,
    driver_version text,
    hostname text,
    keyspace_name text,
    protocol_version int,
    request_count bigint,
    ssl_cipher_suite text,
    ssl_enabled boolean,
    ssl_protocol text,
    username text,
    PRIMARY KEY (address, port)
) WITH CLUSTERING ORDER BY (port ASC)
Column Type Description
address inet Client IP address
port int Client source port
hostname text Client hostname (if resolvable)
username text Authenticated username
keyspace_name text Current keyspace
driver_name text Client driver name
driver_version text Driver version
protocol_version int Native protocol version (3, 4, 5)
request_count bigint Total requests from this connection
connection_stage text Connection state
ssl_enabled boolean TLS encryption enabled
ssl_protocol text TLS protocol version (TLSv1.2, TLSv1.3)
ssl_cipher_suite text TLS cipher suite
client_options map Driver-reported connection options

Equivalent nodetool command: nodetool clientstats

Basic Queries

-- All connected clients
SELECT address, port, username, driver_name, driver_version, request_count
FROM system_views.clients;

-- Clients with username
SELECT address, username, keyspace_name, request_count
FROM system_views.clients;

-- All connections
SELECT address, port, username
FROM system_views.clients;

Security Monitoring

-- All connections with TLS status
SELECT address, port, username, ssl_enabled, ssl_protocol, ssl_cipher_suite
FROM system_views.clients;

Filter results in application for non-TLS connections (ssl_enabled = false) or weak ciphers.

Driver Version Monitoring

-- All driver versions in use
SELECT address, driver_name, driver_version, protocol_version
FROM system_views.clients;

Aggregate driver versions in application logic.

High-Activity Clients

-- All connections with request counts
SELECT address, port, username, request_count
FROM system_views.clients;

-- Connections with no activity (potentially stale)
SELECT address, port, username, request_count
FROM system_views.clients
WHERE request_count < 10;

queries

Shows currently executing queries on this node.

Schema

VIRTUAL TABLE system_views.queries (
    thread_id text PRIMARY KEY,
    queued_micros bigint,
    running_micros bigint,
    task text
)
Column Type Description
thread_id text Executing thread identifier
task text Query description
running_micros bigint Time executing (microseconds)
queued_micros bigint Time spent waiting in queue (microseconds)

Basic Queries

-- All active queries
SELECT thread_id, task, running_micros / 1000 AS running_ms
FROM system_views.queries;

-- Long-running queries (> 1 second)
SELECT thread_id, task, running_micros / 1000000 AS running_seconds
FROM system_views.queries
WHERE running_micros > 1000000;

-- Queries that waited in queue
SELECT thread_id, task,
       queued_micros / 1000 AS queued_ms,
       running_micros / 1000 AS running_ms
FROM system_views.queries
WHERE queued_micros > 10000;

Identifying Slow Queries

-- Find queries running > 5 seconds (potential problems)
SELECT thread_id, task, running_micros / 1000000.0 AS running_seconds
FROM system_views.queries
WHERE running_micros > 5000000;

Query Visibility

The queries table shows point-in-time active queries. Fast queries may complete before being captured. For historical query analysis, use query tracing or full query logging.

Monitoring Use Cases

Connection Pool Health

-- All connections with hostname for categorization
SELECT address, hostname, username, request_count
FROM system_views.clients;

Group by hostname pattern in application logic to categorize connections.

Security Audit

-- All clients with usernames
SELECT address, username, driver_name, request_count
FROM system_views.clients;

-- All client addresses
SELECT address, port, username
FROM system_views.clients;

Filter in application for non-application users and unexpected network ranges.

Capacity Planning

-- All connections with request counts
SELECT address, port, request_count
FROM system_views.clients;

Use COUNT(*) in application to get total connections. Sum request_count values in application for total requests.

Alerting Rules

Unencrypted Connections

-- Alert: Non-TLS connections in production
SELECT address, username
FROM system_views.clients
WHERE ssl_enabled = false;

Outdated Drivers

-- All driver versions
SELECT address, driver_name, driver_version
FROM system_views.clients;

Check driver versions in application for outdated clients.

Long-Running Queries

-- Active queries with execution time
SELECT thread_id, task, running_micros
FROM system_views.queries;

Alert when running_micros > 30000000 (30 seconds).

Connection Spike

-- Compare against baseline (application logic needed)
SELECT COUNT(*) AS current_connections
FROM system_views.clients;
-- Alert if significantly above normal

Security Cache Tables

Related tables show cached authentication/authorization state:

credentials_cache_keys

-- Roles with cached credentials
SELECT role FROM system_views.credentials_cache_keys;

permissions_cache_keys

-- Cached permission entries
SELECT role, resource FROM system_views.permissions_cache_keys;

roles_cache_keys

-- Roles in the role cache
SELECT role FROM system_views.roles_cache_keys;

network_permissions_cache_keys

-- Network permission cache entries
SELECT role FROM system_views.network_permissions_cache_keys;

jmx_permissions_cache_keys

-- JMX permission cache entries
SELECT role FROM system_views.jmx_permissions_cache_keys;

These tables are useful for: - Verifying cache population after authentication changes - Debugging permission issues - Understanding cache invalidation behavior