Skip to content

Instantly share code, notes, and snippets.

@StaniTr StaniTr/LogON
Created Feb 2, 2015

Embed
What would you like to do?
LogON
---select * for max datetime for logons
Select * from puzzSessions where ActionName='LOGON' and Timestamp=(
SELECT logontime --103 rows---------------------------------------------------------------------get date with maximum count
from
--q3:select all entries with logon - logof > 0
--l1 - all logOns
(SELECT l1.logontime,l2.logofftime-----------------------------------------------------cntON and cntOff - joined - q3 (3)
FROM
--SELECT entries both with LOGON and LOGOFF
(SELECT Session_ID, TIMESTAMP logontime -----------------------get logOn (where has logOff) - l1 (4)
FROM puzzSessions
WHERE ActionName='LOGON' AND session_id IN
(--select entries where no LOGOFF
SELECT Session_ID--------------------------------------------------------(5)
FROM puzzSessions
WHERE ActionName='LOGOFF'
)
) l1
--RESULT: 8686 IDs - 8686 LOGONs
LEFT JOIN-----------------------------------------------------------------------------------------Left Join l1, l2 (2)
--l2 - all logOffs
(SELECT Session_ID, TIMESTAMP logofftime --------------------get logOff (where has logOn) - l2 (4)
FROM puzzSessions
WHERE ActionName='LOGOFF' AND session_id IN
(--select entries where no LOGOFF
SELECT Session_ID-----------------------------------------------------(5)
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
) q3-- duration hours more than 0; result -149 rows)------------------------------------------------------------------q3 (3)
group by logontime--------------------------------------------------------------------------------------------------------------------------------------------------maxOn
having count(logontime)=
-----------------------------------------------------------------------------------
(SELECT max(cntOn) maxLogOnDatetime---------------------------------------------------------------------------------------------------------------------------------max cnt logontime - maxOn (1)
FROM
( -- l1 and l2 select all entries with logOns and logOffs
SELECT logontime, count(logontime) cntOn --103 rows---------------------------------------------------------------------count logontime >0 (2)
from
--q3:select all entries with logon - logof > 0
--l1 - all logOns
(SELECT l1.logontime,l2.logofftime-----------------------------------------------------cntON and cntOff - joined - q3 (3)
FROM
--SELECT entries both with LOGON and LOGOFF
(SELECT Session_ID, TIMESTAMP logontime -----------------------get logOn (where has logOff) - l1 (4)
FROM puzzSessions
WHERE ActionName='LOGON' AND session_id IN
(--select entries where no LOGOFF
SELECT Session_ID--------------------------------------------------------(5)
FROM puzzSessions
WHERE ActionName='LOGOFF'
)
) l1
--RESULT: 8686 IDs - 8686 LOGONs
LEFT JOIN-----------------------------------------------------------------------------------------Left Join l1, l2 (2)
--l2 - all logOffs
(SELECT Session_ID, TIMESTAMP logofftime --------------------get logOff (where has logOn) - l2 (4)
FROM puzzSessions
WHERE ActionName='LOGOFF' AND session_id IN
(--select entries where no LOGOFF
SELECT Session_ID-----------------------------------------------------(5)
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
) q3-- duration hours more than 0; result -149 rows)------------------------------------------------------------------q3 (3)
group by logontime--------------------------------------------------------------------------------------------------------------------------------------------------maxOn
having count(logontime)>0-------------------------------------------------------------------------------------------------------------------------------------------maxOn
) maxON-----------------------------------------------------------------------------------------------------------------------------------------------------------------maxOn
)
)
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.