CODEWITHSUNDEEP
sqlamazon-redshiftredash
Migust_a
Migust_a

Reputation: 1

Get the list of user sessions SQL Redshift

I've got a table with 3 fields: userID, url and a loged datetime of user action. I want to write a query in sql Redshift in Redash, which returns a list of user session (all actions which have less that an hour between them) with fields userID, start datetime and end datetime. What can I use to achieve this?

Upvotes: 0

Views: 1847

Answers (2)

debugme
debugme

Reputation: 1081

My $0.02, I use the following query since it excludes "lost connections" and only shows ones with active sessions. It also shows the running query if there is one.

-- Who
SELECT s.user_name,
       s.process,
       s.starttime,
       s.db_name,
       c.remotehost,
       si.starttime query_start_time,
       si.text      query
FROM stv_sessions s
    LEFT JOIN stl_connection_log c
        ON s.user_name = c.username AND s.process = c.pid AND c.event = 'authenticated'
    LEFT JOIN stv_inflight si ON c.pid = si.pid
WHERE s.user_name <> 'rdsdb'
ORDER BY user_name, starttime;

Upvotes: 0

Joe Harris
Joe Harris

Reputation: 14035

Have a look at the view v_connection_summary.sql in our GitHub repo.

https://github.com/awslabs/amazon-redshift-utils/blob/76823b71c7c5c9c7a8fb99183e1662a9476df65a/src/AdminViews/v_connection_summary.sql

Upvotes: 3

Related Questions