基于日志类型匹配登录登出日志对及日志清理需求

需求背景 #

Event Logs表用于接收各计算机的用户登录、登出日志。

实现目标 #

• 依据ComputerId，匹配登录类型（40,43,44,53,57）与对应登出类型（42,45,52）的日志对；
• 列出无对应登出记录的孤立登录项（登出时间为NULL）；
• 跟踪已处理的日志记录，将其从EventLogs表中删除，仅保留未匹配的登录项，以便后续脚本执行时继续处理。

现有问题 #

现有SQL代码可部分匹配登录登出对，但无法展示孤立登录项。

日志类型说明 #

• 登录类型：40,43,44,53,57
• 登出类型：42,45,52

表结构 #

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

示例数据 #

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')

孤立登录数据 #

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')

现有代码 #

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

现有代码执行结果 #

仅展示匹配成功的登录登出对，无法显示无对应登出记录的孤立登录项。

核心诉求 #

优化现有代码，实现完整的登录登出日志对匹配，展示孤立登录项，并实现已处理日志的清理，保留未匹配登录项供后续处理。

内容的提问来源于stack exchange，提问作者Raj