Skip to content

Instantly share code, notes, and snippets.

@xcompass

xcompass/bb_report_session_time

Last active December 24, 2015 08:38
Show Gist options
  • Star 0 You must be signed in to star a gist
  • Fork 0 You must be signed in to fork a gist
  • Save xcompass/6771398 to your computer and use it in GitHub Desktop.
Save xcompass/6771398 to your computer and use it in GitHub Desktop.
Download ZIP
UBC_ISS_SysAdmin role online time
Raw
bb_report_session_time
SELECT u.batch_uid, a.session_id, to_char(a.timestamp, 'yyyy-mm-dd hh24:mi:ss') starttime, to_char(b.timestamp, 'yyyy-mm-dd hh24:mi:ss') endtime, (b.timestamp - a.timestamp)*24*3600
FROM activity_accumulator a
INNER JOIN (SELECT max(timestamp) as timestamp, session_id FROM activity_accumulator WHERE event_type != 'SESSION_TIMEOUT' group by session_id) b
ON a.session_id = b.session_id
AND a.event_type = 'LOGIN_ATTEMPT'
AND a.user_pk1 IS NOT NULL
INNER JOIN (SELECT user_pk1 FROM domain_admin WHERE system_role = 'BB_LE_ADMIN') da
ON a.user_pk1 = da.user_pk1
LEFT JOIN users u
ON a.user_pk1 = u.pk1
WHERE a.timestamp BETWEEN '1-SEP-2012' AND '30-AUG-2013'
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment