This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
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 |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
/*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=( |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
---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) |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
---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) |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
--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 |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
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 |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
--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 |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
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' |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
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' |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
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 |
NewerOlder