Skip to content

Instantly share code, notes, and snippets.

Avatar

Stanislava StaniTr

  • Bulgaria
View GitHub Profile
View gist:a0658c43cce73d0fccd3
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
StaniTr / PuzzleSolution
Last active Aug 29, 2015
PuzzleSolution - MS SQL Script
View PuzzleSolution
/*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=(
View LogOFF
---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)
View 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)
@StaniTr
StaniTr / max(OFF)
Created Feb 2, 2015
max LOGOFF Datetime
View max(OFF)
--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
StaniTr / TASK
Created Jan 30, 2015
max LOGON Date
View TASK
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
View Part 6
--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
StaniTr / Part 5
Created Jan 30, 2015
AVG Duration (logon - logoff)
View Part 5
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
StaniTr / Part 4
Created Jan 30, 2015
All Entries - LOGON and LOGOFF (duration>0 hours)
View Part 4
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
StaniTr / Part3
Created Jan 30, 2015
Entries LOGON Only (w/o LOGOFF)
View Part3
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
You can’t perform that action at this time.