TSQL OrderedTable

Question

So I am querying some data and trying to determine the amount of time between log on and log off for each user. I am close to getting this but am getting results that do not work. Here is the code I have right now:

WITH OrderedTable AS
(
SELECT  EventType
        , ModuleAndEventText
        , Time
        , Node
        , UserSID
        , ROW_Number() OVER (Partition BY UserSID ORDER BY UserSID,EventID,Time) RN 
FROM    viewevent 
where EventType in ('BROKER_USERLOGGEDIN','BROKER_USERLOGGEDOUT') and usersid = 'S-1-5-21-999033763-294680432-740312968-10026'
)

SELECT  t1.EventType
    , t1.ModuleAndEventText
    , t1.Node
    , t1.UserSID
    , t1.Time as TimeIn
    , t2.Time as TimeOut
    , DATEDIFF(hour, t1.Time, t2.Time ) TimeElapsedInHours
FROM    OrderedTable t1
JOIN    OrderedTable t2 ON t1.UserSID = t2.UserSID AND t2.RN = t1.RN + 1
WHERE   t1.RN % 2 <> 0
ORDER BY t1.UserSID

Here are some of the results I am getting:

BROKER_USERLOGGEDIN     User YRMC_MAIN\jerogers has logged in   VMVIEWTC1.yrmc.org  S-1-5-21-999033763-294680432-740312968-10026    2012-04-14 08:00:36.137   2012-04-14 09:32:08.267     1
BROKER_USERLOGGEDIN     User YRMC_MAIN\jerogers has logged in   VMVIEWTC1.yrmc.org  S-1-5-21-999033763-294680432-740312968-10026    2012-04-14 08:00:36.137   2012-04-14 09:32:08.267     1
BROKER_USERLOGGEDIN     User YRMC_MAIN\jerogers has logged in   VMVIEWWC2.yrmc.org  S-1-5-21-999033763-294680432-740312968-10026    2012-06-11 14:50:40.420   2012-06-11 16:43:08.640     2
BROKER_USERLOGGEDIN     User YRMC_MAIN\jerogers has logged in   VMVIEWWC2.yrmc.org  S-1-5-21-999033763-294680432-740312968-10026    2012-06-11 17:49:46.330   2012-06-11 18:42:50.047     1
BROKER_USERLOGGEDOUT    User YRMC_MAIN\jerogers has logged out  VMVIEWWC2.yrmc.org  S-1-5-21-999033763-294680432-740312968-10026    2012-06-11 18:59:40.550   2012-06-12 23:20:16.027     29
BROKER_USERLOGGEDOUT    User YRMC_MAIN\jerogers has logged out  VMVIEWWC2.yrmc.org  S-1-5-21-999033763-294680432-740312968-10026    2012-06-12 23:20:16.777   2012-06-12 23:20:16.823     0
BROKER_USERLOGGEDOUT    User YRMC_MAIN\jerogers has logged out  VMVIEWWC2.yrmc.org  S-1-5-21-999033763-294680432-740312968-10026    2012-06-12 23:20:17.120   2012-06-15 13:03:31.807     62

The first two records are correct. The last few are not, it is picking the logged out record as the time in and I am not clear on how to fix this. Here is the data:

EventType    ModuleAndEventText    Time    Node    UserSID
BROKER_USERLOGGEDIN     User YRMC_MAIN\jerogers has logged in   2012-04-14 08:00:36.137 VMVIEWTC1.yrmc.org  S-1-5-21-999033763-294680432-740312968-10026
BROKER_USERLOGGEDOUT    User YRMC_MAIN\jerogers has logged out  2012-04-14 09:32:08.267 VMVIEWTC1.yrmc.org  S-1-5-21-999033763-294680432-740312968-10026
BROKER_USERLOGGEDIN     User YRMC_MAIN\jerogers has logged in   2012-04-14 08:00:36.137 VMVIEWTC1.yrmc.org  S-1-5-21-999033763-294680432-740312968-10026
BROKER_USERLOGGEDOUT    User YRMC_MAIN\jerogers has logged out  2012-04-14 09:32:08.267 VMVIEWTC1.yrmc.org  S-1-5-21-999033763-294680432-740312968-10026
BROKER_USERLOGGEDIN     User YRMC_MAIN\jerogers has logged in   2012-06-11 14:50:40.420 VMVIEWWC2.yrmc.org  S-1-5-21-999033763-294680432-740312968-10026
BROKER_USERLOGGEDIN     User YRMC_MAIN\jerogers has logged in   2012-06-11 16:43:08.640 VMVIEWWC2.yrmc.org  S-1-5-21-999033763-294680432-740312968-10026
BROKER_USERLOGGEDIN     User YRMC_MAIN\jerogers has logged in   2012-06-11 17:49:46.330 VMVIEWWC2.yrmc.org  S-1-5-21-999033763-294680432-740312968-10026
BROKER_USERLOGGEDIN     User YRMC_MAIN\jerogers has logged in   2012-06-11 18:42:50.047 VMVIEWWC2.yrmc.org  S-1-5-21-999033763-294680432-740312968-10026
BROKER_USERLOGGEDOUT    User YRMC_MAIN\jerogers has logged out  2012-06-11 18:59:40.550 VMVIEWWC2.yrmc.org  S-1-5-21-999033763-294680432-740312968-10026
BROKER_USERLOGGEDOUT    User YRMC_MAIN\jerogers has logged out  2012-06-12 23:20:16.027 VMVIEWWC2.yrmc.org  S-1-5-21-999033763-294680432-740312968-10026
BROKER_USERLOGGEDOUT    User YRMC_MAIN\jerogers has logged out  2012-06-12 23:20:16.777 VMVIEWWC2.yrmc.org  S-1-5-21-999033763-294680432-740312968-10026
BROKER_USERLOGGEDOUT    User YRMC_MAIN\jerogers has logged out  2012-06-12 23:20:16.823 VMVIEWWC2.yrmc.org  S-1-5-21-999033763-294680432-740312968-10026
BROKER_USERLOGGEDOUT    User YRMC_MAIN\jerogers has logged out  2012-06-12 23:20:17.120 VMVIEWWC2.yrmc.org  S-1-5-21-999033763-294680432-740312968-10026
BROKER_USERLOGGEDIN     User YRMC_MAIN\jerogers has logged in   2012-06-15 13:03:31.807 VMVIEWWC2.yrmc.org  S-1-5-21-999033763-294680432-740312968-10026

Thanks in advance. Kameron

Answer 1

assuming that there are no disconnect without connect, I like the following approach producing a pseudo sessionId

declare @tEvent table (
    UserId int,
    EventType varchar(10),
    EventDate datetime2
)

insert into @tEvent (UserId, EventType, EventDate) values
(1, 'CONNECT', '20120101'),
(2, 'CONNECT', '20120101'),
(1, 'DISCONNECT', '20120102'),
(1, 'CONNECT', '20120103'),
(2, 'DISCONNECT', '20120103'),
(1, 'DISCONNECT', '20120105'),
(1, 'CONNECT', '20120106')

select 
    conn.UserId, conn.SessionId, conn.EventDate as startDate, disco.EventDate as endDate, DATEDIFF(d, conn.EventDate, disco.EventDate) as duration
from 
    (select 
        ROW_NUMBER() over (partition by UserId order by EventDate) SessionId,
        UserId,
        EventDate
    from
        @tEvent 
    where EventType = 'CONNECT') conn
    left join (
    select 
        ROW_NUMBER() over (partition by UserId order by EventDate) SessionId,
        UserId,
        EventDate
    from
        @tEvent 
    where EventType = 'DISCONNECT' ) disco on conn.UserId = disco.UserId and conn.SessionId = disco.SessionId

Answer 2

Could you just add the restriction on your t1 WHERE

WITH OrderedTable  AS
(
SELECT  EventType
    , ModuleAndEventText
    , Time
    , Node
    , UserSID
    , ROW_Number() OVER (Partition BY UserSID ORDER BY UserSID,EventID,Time) RN 
FROM  viewevent 
where EventType in ('BROKER_USERLOGGEDIN','BROKER_USERLOGGEDOUT') and usersid = 'S-1-5-21-      999033763-294680432-740312968-10026'
 )

SELECT  t1.EventType
    , t1.ModuleAndEventText
    , t1.Node
    , t1.UserSID
    , t1.Time as TimeIn
    , t2.Time as TimeOut
    , DATEDIFF(hour, t1.Time, t2.Time ) TimeElapsedInHours
FROM    OrderedTable2 t1
JOIN    OrderedTable2 t2 ON t1.UserSID = t2.UserSID AND t2.RN = t1.RN + 1
WHERE   t1.RN % 2 <> 0 AND t1.EventType = 'BROKER_USERLOGGEDIN' --ONLY CHANGE IS HERE - THIS RETURNS WHAT MY UNDERSTANDING OF WHAT YOU EXPECT IS

EventType   ModuleAndEventText  Node    UserSID TimeIn  TimeOut TimeElapsedInHours
--------------------------------------------------------------------------------------
BROKER_USERLOGGEDIN User YRMC_MAIN\jerogers has logged in   VMVIEWTC1.yrmc.org  S-1-5-21-999033763-294680432-740312968-10026    2012-04-14 08:00:36.137 2012-04-14 09:32:08.267 1
BROKER_USERLOGGEDIN User YRMC_MAIN\jerogers has logged in   VMVIEWTC1.yrmc.org  S-1-5-21-999033763-294680432-740312968-10026    2012-04-14 08:00:36.137 2012-04-14 09:32:08.267 1
BROKER_USERLOGGEDIN  User YRMC_MAIN\jerogers has logged in  VMVIEWWC2.yrmc.org  S-1-5-21-999033763-294680432-740312968-10026    2012-06-11 14:50:40.420 2012-06-11 16:43:08.640 2
BROKER_USERLOGGEDIN User YRMC_MAIN\jerogers has logged in   VMVIEWWC2.yrmc.org  S-1-5-21-999033763-294680432-740312968-10026    2012-06-11 17:49:46.330 2012-06-11 18:42:50.047 1
ORDER BY t1.UserSID

Answer 3

The problem is that log files do not neatly interleave the logins and logouts.

Here is an alternative method. It chooses the minimum logout time after each login:

select ins.UserSID, ins.time as login_time, min(outs.time) as logout_time
from (SELECT *
      FROM viewevent
      where EventType in ('BROKER_USERLOGGEDOUT')
     ) outs left outer join
     (SELECT *
      FROM viewevent
      where EventType in ('BROKER_USERLOGGEDIN')
     ) ins
     on outs.UserSID = ins.UserSID and
        outs.Time >= ins.Time
group by ins.UserSID

I've left out the restriction on the user id (which you should add to both subqueries or place into a with clause), and the specific calculation of the difference in hours (because I would do it using a float not an int).