Redshift tables contains a lot of useful information about database sessions.
select s.process as process_id, c.remotehost || ':' || c.remoteport as remote_address, s.user_name as username, s.starttime as session_start_time, s.db_name, i.starttime as current_query_time, i.text as query from stv_sessions s left join pg_user u on u.usename = s.user_name left join stl_connection_log c on c.pid = s.process and c.event = 'authenticated' left join stv_inflight i on u.usesysid = i.userid and s.process = i.pid where username <> 'rdsdb' order by session_start_time desc;
- process_id - Process ID of the session
- remote_address - remote host with remote port
- username - user name
- session_start_time - date and time of session start
- db_name - database name
- current_query_time - time of current query executing was started
- query - current query executing in session
- One row: represents one active connection
- Scope of rows: all active connections
- Ordered by start time of the session
You can see session list on our test server.