StaniTr

QuartersMap:  
MAPPING LOAD   
rowno() as Month,  
'Q' & Ceil (rowno()/3) as Quarter  
AUTOGENERATE (12);  
  
//Create min and max date (max=current date for the assignment)
Temp:
load date(minDate,'YYYY-MM-DD') AS minDate,  date(maxDate,'YYYY-MM-DD') AS maxDate inline [
minDate, maxDate

StaniTr

/*The most simultaneous LOGONNs and LOGOFFs for sessions, which have difference between LOGON and LOGOFF more than 0 hours
	2 main sub-queries:
	1) The most simultaneous LOGONs
	UNION all
	2) The most simultaneous LOGOFFs
*/


--The most simultaneous LOGONs
Select * from puzzSessions where ActionName='LOGON' and Timestamp=( 

StaniTr

---select * for max datetime for logoffs
Select * from puzzSessions where ActionName='LOGOFF' and Timestamp=( 
	SELECT  logofftime 
	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)

StaniTr

---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)

StaniTr

--maxOff - find out max of LOGOFF datetime counts
SELECT max(cntOff) maxLogOffDatetime------------------------------------------------------------------------------------------------------max cnt logofftime - maxOff  (1)
FROM
(	-- l1 and l2 select all entries with logOns and logOffs	
	SELECT  logofftime, count(logofftime) cntOff --118 rows---------------------------------------------------------------------count logofftime >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)---q3 START
		FROM 

StaniTr

SELECT max(cntOn)
from
(
SELECT logontime, count(logontime) cntOn --25 rows
from 
--q3:select all entries with logon - logof > 0
(SELECT   l1.logontime,l2.logofftime
FROM 
                        --SELECT entries both with LOGON and LOGOFF

StaniTr

--SELECT entries with LOGOFF w/o LOGON
SELECT Session_ID, TIMESTAMP
FROM puzzSessions
WHERE ActionName='LOGOFF' AND session_id NOT IN(
        --select entries where no LOGOFF
        SELECT Session_ID
        FROM puzzSessions
        WHERE ActionName='LOGON'
)
-- RESULT: 1 ID - 1 LOGOFF --logoff from 14 Dec 2014 --login ?? - hacker

StaniTr

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'

StaniTr

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'

StaniTr

SELECT Session_ID, TIMESTAMP, datediff(DAY, puzzsessions.TIMESTAMP, sysdatetime()) daysLogged   --logOn time - (current)system time 
FROM puzzSessions
--logon
WHERE ActionName='LOGON' AND session_id NOT IN
        -- no LOGOFF
        (SELECT Session_ID
        FROM puzzSessions
        WHERE ActionName='LOGOFF')
ORDER BY TIMESTAMP DESC --login from 15th and 16th dec 2014 :)))
--RESULT: 31 IDs -  3 LOGONs w/o LOGOFF