Skip to content

Instantly share code, notes, and snippets.

@StaniTr StaniTr/Part 4
Created Jan 30, 2015

Embed
What would you like to do?
All Entries - LOGON and LOGOFF (duration>0 hours)
SELECT l1.Session_ID, l1.logontime,l2.logofftime, datediff(HOUR,l1.logontime,l2.logofftime) logDurationHOURS
FROM
--SELECT entries both with LOGON and LOGOFF
(SELECT Session_ID, TIMESTAMP logontime
FROM puzzSessions
WHERE ActionName='LOGON' AND session_id IN(
--select entries where no LOGOFF
SELECT Session_ID
FROM puzzSessions
WHERE ActionName='LOGOFF'
)) l1
--RESULT: 8686 IDs - 8686 LOGONs
LEFT JOIN
(SELECT Session_ID, TIMESTAMP logofftime
FROM puzzSessions
WHERE ActionName='LOGOFF' AND session_id IN(
--select entries where no LOGOFF
SELECT Session_ID
FROM puzzSessions
WHERE ActionName='LOGON'
--RESULT: 8686 IDs - 8686 LOGOFFs
)) l2
ON l1.Session_ID=l2.Session_ID
WHERE datediff(HOUR,l1.logontime,l2.logofftime)>0 --result -149 rows
ORDER BY logDurationHOURS DESC;
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
You can’t perform that action at this time.