AVG Duration (logon - logoff)
SELECT avg(datediff(MINUTE,l1.logontime,l2.logofftime)) avgDuration | |
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(MINUTE,l1.logontime,l2.logofftime)>0 --avg is based on all logs>0, if it is based on all entries -> where clause must be excluded |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment