MS SQL 2014查询NPS Radius拒绝事件并关联获取用户名的SQL方案

你可以使用SQL Server 2012及以上版本原生支持的LAG()窗口函数实现需求，该函数可以直接提取同一分组内当前行的上一行数据，完美匹配你需要关联同NPS服务器下拒绝事件前置请求的场景。

基础可用版本 #

SELECT 
    id,
    timestamp,
    NPS_Svr,
    Packet_Type,
    Description,
    -- 取同NPS_Svr下上一行的用户名，即对应Access Request里存储的用户信息
    LAG([User-Computer], 1) OVER (PARTITION BY NPS_Svr ORDER BY timestamp ASC, id ASC) AS rejected_username
FROM accounting_data
WHERE Packet_Type = 3

逻辑说明 #

• 用PARTITION BY NPS_Svr将日志按NPS服务器分组，确保只会关联同一台服务器产生的日志
• 按timestamp ASC, id ASC排序保证日志时序和实际发生顺序一致，如果你表中id是严格自增的写入顺序ID，也可以仅按id排序
• LAG([User-Computer], 1)直接提取同分组内上一行的User-Computer字段值，也就是拒绝事件之前的Access Request（Packet_Type=1）记录里存储的用户名

严谨校验版本 #

如果你的日志存在异常场景（比如两条包中间插入了其他类型的日志），可以增加逻辑校验上一条记录确实是Access Request类型，避免取到错误的用户名：

WITH log_with_prev AS (
    SELECT 
        *,
        LAG([User-Computer], 1) OVER (PARTITION BY NPS_Svr ORDER BY timestamp ASC, id ASC) AS rejected_username,
        LAG(Packet_Type, 1) OVER (PARTITION BY NPS_Svr ORDER BY timestamp ASC, id ASC) AS prev_packet_type
    FROM accounting_data
)
SELECT 
    id,
    timestamp,
    NPS_Svr,
    Packet_Type,
    Description,
    rejected_username
FROM log_with_prev
WHERE Packet_Type = 3 
  AND prev_packet_type = 1 -- 仅保留上一条确实是Access Request的拒绝记录

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