Find Login and Logout pair from logs base on log type-CodePudding

Event Logs table receives logs from each computer whenever user logs in and similarly whenever users logs out. I need to find login and logout pair/row. It essentially looks at the computer Id and checks does it have login types(40,43,44,53,57 ) and find first login then the corresponding logout(42,45,52) Also list the rows that don't have logout events found yet like logout time would null or insert into a variable table to keep track to display at the end. Because when user logs in only login(40,43,44,53,57 ) logs are received, which means user is logged in until next logout(42,45,52) is received. The current code I have does find somewhat login and logouts but does not show the orphan(not corresponding logout found) rows:

Login  types are  40,43,44,53,57  
Logout types are  42,45,52

The table structure is:

CREATE TABLE [dbo].[EventLogs](
    [ComputerId] [uniqueidentifier] NOT NULL, 
    [EventDateTime] [datetime2](7) NOT NULL,
    [EventType] [int] NOT NULL,
    [UserId] [uniqueidentifier] NULL     
) ON [PRIMARY]

Sample data

INSERT [dbo].[EventLogs] ([ComputerId], [EventDateTime], [EventType], [UserId]) VALUES (N'060ba6d9-4a58-4186-bb89-369fc56ae674',CAST(N'2022-12-31T00:37:33.0000000' AS DateTime2), 42, N'b2283380-6a52-492b-9617-5d4b202da8a1') 
INSERT [dbo].[EventLogs] ([ComputerId], [EventDateTime], [EventType], [UserId]) VALUES (N'060ba6d9-4a58-4186-bb89-369fc56ae674',CAST(N'2022-12-31T00:00:54.0000000' AS DateTime2), 43, N'b2283380-6a52-492b-9617-5d4b202da8a1')
INSERT [dbo].[EventLogs] ([ComputerId], [EventDateTime], [EventType], [UserId]) VALUES (N'060ba6d9-4a58-4186-bb89-369fc56ae674',CAST(N'2022-12-31T00:00:54.0000000' AS DateTime2), 53, N'b2283380-6a52-492b-9617-5d4b202da8a1')
INSERT [dbo].[EventLogs] ([ComputerId], [EventDateTime], [EventType], [UserId]) VALUES (N'060ba6d9-4a58-4186-bb89-369fc56ae674',CAST(N'2022-12-31T00:37:41.0000000' AS DateTime2), 43, N'b2283380-6a52-492b-9617-5d4b202da8a1')
INSERT [dbo].[EventLogs] ([ComputerId], [EventDateTime], [EventType], [UserId]) VALUES (N'060ba6d9-4a58-4186-bb89-369fc56ae674',CAST(N'2022-12-31T00:37:41.0000000' AS DateTime2), 53, N'b2283380-6a52-492b-9617-5d4b202da8a1')

Below is orphan login

INSERT [dbo].[EventLogs] ([ComputerId], [EventDateTime], [EventType], [UserId]) VALUES (N'060ba6d9-4a58-4186-bb89-369fc56ae674',CAST(N'2022-12-31T00:49:50.0000000' AS DateTime2), 53, N'b2283380-6a52-492b-9617-5d4b202da8a1')
INSERT [dbo].[EventLogs] ([ComputerId], [EventDateTime], [EventType], [UserId]) VALUES (N'060ba6d9-4a58-4186-bb89-369fc56ae674',CAST(N'2022-12-31T00:49:50.0000000' AS DateTime2), 43, N'b2283380-6a52-492b-9617-5d4b202da8a1')
INSERT [dbo].[EventLogs] ([ComputerId], [EventDateTime], [EventType], [UserId]) VALUES (N'060ba6d9-4a58-4186-bb89-369fc56ae674',CAST(N'2022-12-31T00:49:54.0000000' AS DateTime2), 44, N'b2283380-6a52-492b-9617-5d4b202da8a1')

The current code:

    DECLARE @StartDate DATETIME= CONVERT(DATETIME, CONVERT(VARCHAR,( Select CAST(Min(EventDateTime)as date) from dbo.EventLogs ))   ' '  CONVERT(VARCHAR, '00:00:01'))
    DECLARE @EndDate DATETIME= GETUTCDATE()
    
    DECLARE @Events table (ComputerId uniqueidentifier ,UserId uniqueidentifier,EventDateTime DateTime2,EventType   int);
    Insert into @Events (ComputerId,UserId,EventDateTime,EventType) 
    select  
    eventlogs.ComputerId,
    eventlogs.UserId as UserId ,
    eventlogs.EventDateTime,
    eventlogs.EventType       
    from  dbo.EventLogs as eventlogs  
        where (EventDateTime> @StartDate and EventDateTime<@EndDate) and EventType in (52, 57, 53, 42, 41, 40, 43, 44, 45)    
             
    SELECT
        dt.LoginTime, 
        dt.LogoutTime,
        dt.UserId,
        dt.ComputerId 
        FROM (
                SELECT
                    p.ComputerId as ComputerId,
                    p.UserId as UserId,
                    p.EventDateTime AS LoginTime,
                    CASE WHEN  c.EventType = 53 or c.EventType = 43 or c.EventType = 44 or c.EventType = 40 or c.EventType = 57
                    THEN NULL 
                    ELSE 
                        c.EventDateTime 
                    END 
                    AS LogoutTime, p.EventDateTime FROM @Events p               
                    left join @Events c ON p.EventDateTime<c.EventDateTime
                    WHERE 
                    (p.EventType=53 or p.EventType=43 or p.EventType = 44 or p.EventType = 40 or c.EventType = 57)
                    AND c.EventDateTime=(SELECT min(EventDateTime) FROM @Events WHERE EventDateTime>p.EventDateTime 
                    AND ComputerId=p.ComputerId AND UserId=p.UserId)
                UNION
                SELECT
                    p.ComputerId as ComputerId,
                    p.UserId as UserId, 
                    NULL AS LoginTime,
                    p.EventDateTime,
                    p.EventDateTime
                    FROM @Events p
                     left JOIN @Events  c ON p.EventDateTime>c.EventDateTime
                    WHERE 
                     c.EventDateTime=(SELECT MAX(EventDateTime) FROM @Events WHERE  EventDateTime<p.EventDateTime 
                     AND ComputerId=p.ComputerId and UserId = p.UserId) 
                     AND (p.EventType = 52 or p.EventType = 42 or p.EventType = 45) 
                     AND (c.EventType = 52 or c.EventType = 42 or c.EventType = 45)             
                ) dt
            where dt.LoginTime is not null and LogoutTime is not null and UserId is not null

Result:

The most important part is that we want to keep track of the events that were processed or that were part of creating login and logouts so that those can be deleted from EventLogs at the end. Because we don't want to go through processed logs again. User A logged in from Computer A at 2022-12-31 10:00:20 , tables received data like events 53,43. Then user logged out 2022-12-31 11:00:00 and events received like 42,52. So this became a complete login and logout. Now user logs in again 20 seconds later at 2022-12-31 11:00:20. again events 53,43 recorded in table. So now if we run sql script we will see that there is one login and logout session completed. But events 53,43 received at 2022-12-31 11:00:20 doesn't have logout events yet therefore user is logged in. at this time we want to delete rows from table that were procced and leave these new login events so that it will proceed when SQL script runs again.

CodePudding user response：

Here's one approach that finds login edges and ties all rows from one edge to just before the next edge together. If an edge has no subsequent logout detected (an orphan), that logout column is null.

I'm probably missing a little logic here. Slightly more varied data would probably expose it. I'll update the logic shortly. (Corrected)

I added some data to the fiddle (a second user) to expose the issue.

The fiddle

WITH args (_StartDate, _EndDate) AS (
        SELECT CAST(MIN(EventDateTime) AS date)
             , GETUTCDATE()
          FROM EventLogs
     )
   , _Events0 AS (
        SELECT *
             , CASE WHEN EventType     IN (53, 43, 44, 40, 57) THEN 1 ELSE 0 END AS isLogin
          FROM EventLogs
     )
   , _Events1 AS (
        SELECT *
             , CASE WHEN isLogin = 0 OR isLogin = LAG(isLogin) OVER (PARTITION BY ComputerId, UserId ORDER BY EventDateTime, EventType) THEN 0 ELSE 1 END AS edge
          FROM _Events0
     )
   , _Events AS (
        SELECT eventlogs.ComputerId
             , eventlogs.UserId as UserId
             , eventlogs.EventDateTime
             , eventlogs.EventType
             , SUM(edge) OVER (PARTITION BY ComputerId, UserId ORDER BY EventDateTime, isLogin DESC) AS loginGroup
             , CASE WHEN isLogin = 1 THEN EventDateTime END AS loginTime
             , CASE WHEN isLogin = 0 THEN EventDateTime END AS logoutTime
          FROM _Events1 AS eventlogs, args
         WHERE EventDateTime BETWEEN _StartDate AND _EndDate
           AND EventType IN (52, 57, 53, 42, 41, 40, 43, 44, 45)
     )
SELECT ComputerId, UserId, loginGroup
     , MIN(loginTime)  AS login
     , MAX(logoutTime) AS logout
  FROM _Events
 WHERE loginGroup > 0
 GROUP BY ComputerId, UserId, loginGroup
 ORDER BY ComputerId, UserId, loginGroup
;

With your sample data, the following is produced:

ComputerId	UserId	loginGroup	login	logout
060ba6d9-4a58-4186-bb89-369fc56ae674	b2283380-6a52-492b-9617-5d4b202da8a1	1	2022-12-31 00:00:54.0000000	2022-12-31 00:37:33.0000000
060ba6d9-4a58-4186-bb89-369fc56ae674	b2283380-6a52-492b-9617-5d4b202da8a1	2	2022-12-31 00:37:41.0000000	null
060ba6d9-4a58-4186-bb89-369fc56ae674	b2283380-6a52-492b-9617-5d4b202da8a2	1	2022-12-31 00:00:54.0000000	2022-12-31 00:37:33.0000000
060ba6d9-4a58-4186-bb89-369fc56ae674	b2283380-6a52-492b-9617-5d4b202da8a2	2	2022-12-31 00:37:41.0000000	null

CodePudding user response：

Using your example data (and adding a row to demonstrate a second logout):

DECLARE @EventLogs TABLE (ComputerId UNIQUEIDENTIFIER NOT NULL, EventDateTime DATETIME2(7) NOT NULL, EventType INT NOT NULL, UserId UNIQUEIDENTIFIER NULL);
INSERT INTO @EventLogs (ComputerId, EventDateTime, EventType, UserId) VALUES 
(N'060ba6d9-4a58-4186-bb89-369fc56ae674',CAST(N'2022-12-31T00:37:33.0000000' AS DateTime2), 42, N'b2283380-6a52-492b-9617-5d4b202da8a1'), 
(N'060ba6d9-4a58-4186-bb89-369fc56ae674',CAST(N'2022-12-31T00:00:54.0000000' AS DateTime2), 43, N'b2283380-6a52-492b-9617-5d4b202da8a1'),
(N'060ba6d9-4a58-4186-bb89-369fc56ae674',CAST(N'2022-12-31T00:00:54.0000000' AS DateTime2), 53, N'b2283380-6a52-492b-9617-5d4b202da8a1'),
(N'060ba6d9-4a58-4186-bb89-369fc56ae674',CAST(N'2022-12-31T00:37:41.0000000' AS DateTime2), 43, N'b2283380-6a52-492b-9617-5d4b202da8a1'),
(N'060ba6d9-4a58-4186-bb89-369fc56ae674',CAST(N'2022-12-31T00:37:41.0000000' AS DateTime2), 53, N'b2283380-6a52-492b-9617-5d4b202da8a1');

INSERT INTO @EventLogs (ComputerId, EventDateTime, EventType, UserId) VALUES 
(N'060ba6d9-4a58-4186-bb89-369fc56ae674',CAST(N'2022-12-31T00:49:50.0000000' AS DateTime2), 53, N'b2283380-6a52-492b-9617-5d4b202da8a1'),
(N'060ba6d9-4a58-4186-bb89-369fc56ae674',CAST(N'2022-12-31T00:49:50.0000000' AS DateTime2), 43, N'b2283380-6a52-492b-9617-5d4b202da8a1'),
(N'060ba6d9-4a58-4186-bb89-369fc56ae674',CAST(N'2022-12-31T00:49:54.0000000' AS DateTime2), 44, N'b2283380-6a52-492b-9617-5d4b202da8a1');

INSERT INTO @EventLogs (ComputerID, EventDateTime, EventType, UserId) VALUES
(N'060ba6d9-4a58-4186-bb89-369fc56ae674',CAST(N'2022-12-31T00:37:55.0000000' AS DateTime2), 52, N'b2283380-6a52-492b-9617-5d4b202da8a1');

SELECT *, 'These are the original rows'
  FROM @EventLogs;

ComputerId                              EventDateTime               EventType   UserId                                  (No column name)
060BA6D9-4A58-4186-BB89-369FC56AE674    2022-12-31 00:37:33.0000000 42          B2283380-6A52-492B-9617-5D4B202DA8A1    These are the original rows
060BA6D9-4A58-4186-BB89-369FC56AE674    2022-12-31 00:00:54.0000000 43          B2283380-6A52-492B-9617-5D4B202DA8A1    These are the original rows
060BA6D9-4A58-4186-BB89-369FC56AE674    2022-12-31 00:00:54.0000000 53          B2283380-6A52-492B-9617-5D4B202DA8A1    These are the original rows
060BA6D9-4A58-4186-BB89-369FC56AE674    2022-12-31 00:37:41.0000000 43          B2283380-6A52-492B-9617-5D4B202DA8A1    These are the original rows
060BA6D9-4A58-4186-BB89-369FC56AE674    2022-12-31 00:37:41.0000000 53          B2283380-6A52-492B-9617-5D4B202DA8A1    These are the original rows
060BA6D9-4A58-4186-BB89-369FC56AE674    2022-12-31 00:49:50.0000000 53          B2283380-6A52-492B-9617-5D4B202DA8A1    These are the original rows
060BA6D9-4A58-4186-BB89-369FC56AE674    2022-12-31 00:49:50.0000000 43          B2283380-6A52-492B-9617-5D4B202DA8A1    These are the original rows
060BA6D9-4A58-4186-BB89-369FC56AE674    2022-12-31 00:49:54.0000000 44          B2283380-6A52-492B-9617-5D4B202DA8A1    These are the original rows
060BA6D9-4A58-4186-BB89-369FC56AE674    2022-12-31 00:37:55.0000000 52          B2283380-6A52-492B-9617-5D4B202DA8A1    These are the original rows

We can run over the table and determine the completed sessions, assuming the only criteria is that it's the most recent logout type for the same computer/user using a CTE and then DELETE from the table where the rows match:

;WITH DeleteThese AS (
        SELECT li.ComputerId, li.UserId, li.EventDateTime AS LoginEventDateTime, li.EventType AS LoginEventType, lo.EventDateTime AS LogoutEventDateTime, lo.EventType AS LogoutEventType, ROW_NUMBER() OVER (PARTITION BY li.ComputerId, li.UserId, li.EventDateTime, li.EventType ORDER BY lo.EventType, lo.EventDateTime) AS rn
          FROM @EventLogs li
            LEFT OUTER JOIN @EventLogs lo
              ON li.ComputerId = lo.ComputerId
              AND li.UserId = lo.UserId
              AND lo.EventType IN (42,45,52)
              AND li.EventDateTime < lo.EventDateTime
         WHERE li.EventType IN (40,43,44,53,57)
)

DELETE e
OUTPUT DELETED.* INTO @WorkTable
  FROM DeleteThese dt
    INNER JOIN @EventLogs e
      ON dt.ComputerId = e.ComputerId
      AND dt.UserId = e.UserId
      AND (
               dt.LoginEventType = e.EventType
           AND dt.LoginEventDateTime = e.EventDateTime
          )
       OR (
               dt.LogoutEventType = e.EventType
           AND dt.LogoutEventDateTime = e.EventDateTime
          )
 WHERE rn = 1
   AND dt.LogoutEventDateTime IS NOT NULL;


SELECT *, 'These are rows left in the table.'
  FROM @EventLogs;

SELECT *, 'These are rows removed from the table, and ready to be processed.'
  FROM @WorkTable;

Now the rows are deleted from the original table, but we have them in a table variable (remember this will only persist for the duration of the execution)

ComputerId                              EventDateTime               EventType   UserId                                  (No column name)
060BA6D9-4A58-4186-BB89-369FC56AE674    2022-12-31 00:49:50.0000000 53          B2283380-6A52-492B-9617-5D4B202DA8A1    These are rows left in the table.
060BA6D9-4A58-4186-BB89-369FC56AE674    2022-12-31 00:49:50.0000000 43          B2283380-6A52-492B-9617-5D4B202DA8A1    These are rows left in the table.
060BA6D9-4A58-4186-BB89-369FC56AE674    2022-12-31 00:49:54.0000000 44          B2283380-6A52-492B-9617-5D4B202DA8A1    These are rows left in the table.

ComputerId                              EventDateTime               EventType   UserId                                  (No column name)
060BA6D9-4A58-4186-BB89-369FC56AE674    2022-12-31 00:37:33.0000000 42          B2283380-6A52-492B-9617-5D4B202DA8A1    These are rows removed from the table, and ready to be processed.
060BA6D9-4A58-4186-BB89-369FC56AE674    2022-12-31 00:00:54.0000000 43          B2283380-6A52-492B-9617-5D4B202DA8A1    These are rows removed from the table, and ready to be processed.
060BA6D9-4A58-4186-BB89-369FC56AE674    2022-12-31 00:00:54.0000000 53          B2283380-6A52-492B-9617-5D4B202DA8A1    These are rows removed from the table, and ready to be processed.
060BA6D9-4A58-4186-BB89-369FC56AE674    2022-12-31 00:37:41.0000000 43          B2283380-6A52-492B-9617-5D4B202DA8A1    These are rows removed from the table, and ready to be processed.
060BA6D9-4A58-4186-BB89-369FC56AE674    2022-12-31 00:37:41.0000000 53          B2283380-6A52-492B-9617-5D4B202DA8A1    These are rows removed from the table, and ready to be processed.
060BA6D9-4A58-4186-BB89-369FC56AE674    2022-12-31 00:37:55.0000000 52          B2283380-6A52-492B-9617-5D4B202DA8A1    These are rows removed from the table, and ready to be processed.

Does this get you close to where you needed to be?