0

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?

Migust_a
  • 1
  • 1
  • You can use sql. please have a try and post what you get. (This is not a code writing service). One approach will be to use window functions and search previous answers. – Jon Scott May 09 '18 at 16:21

2 Answers2

3

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

Joe Harris
  • 13,671
  • 4
  • 47
  • 54
0

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;
debugme
  • 1,041
  • 1
  • 10
  • 22