Skip to content

Instantly share code, notes, and snippets.

Stanislava StaniTr

  • Bulgaria
Block or report user

Report or block StaniTr

Hide content and notifications from this user.

Learn more about blocking users

Contact Support about this user’s behavior.

Learn more about reporting abuse

Report abuse
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.