如何用T-SQL检测同一IP短时间内被多用户登录的可疑行为

思路说明 #

核心逻辑是先排除同一IP下同一用户的重复登录记录，再统计每个IP对应的不同用户数量，筛选出用户数≥2的可疑IP。以下提供两种CTE实现方案，分别满足不同输出需求。

方案1：输出可疑IP及对应不同用户数 #

此脚本返回符合条件的IP，以及该IP在指定时间范围内的不同登录用户数量：

WITH CTE_DistinctLogins AS (
    -- 去重：同一IP下同一用户的多次登录仅保留一条
    SELECT DISTINCT
        SourceIP,
        UserName,
        DATEADD(HOUR, DATEDIFF(HOUR, 0, LoginTime), 0) AS LoginHour -- 按小时划分时间窗口，可调整粒度
    FROM MySQLTableWithActivitiyRecords
    -- 指定目标时间范围：2024-01-27 15:00 至 16:00
    WHERE LoginTime BETWEEN '2024-01-27 15:00:00' AND '2024-01-27 16:00:00'
),
CTE_SuspiciousIPs AS (
    -- 统计每个IP的不同用户数，筛选用户数≥2的可疑IP
    SELECT
        SourceIP,
        COUNT(DISTINCT UserName) AS DistinctUserCount
    FROM CTE_DistinctLogins
    GROUP BY SourceIP, LoginHour
    HAVING COUNT(DISTINCT UserName) >= 2
)
SELECT * FROM CTE_SuspiciousIPs;

方案2：输出可疑IP下的所有用户登录记录 #

此脚本返回可疑IP对应的所有原始登录记录（包含同一用户的多次登录，但仅针对已判定为可疑的IP）：

WITH CTE_DistinctLogins AS (
    SELECT DISTINCT
        SourceIP,
        UserName,
        DATEADD(HOUR, DATEDIFF(HOUR, 0, LoginTime), 0) AS LoginHour
    FROM MySQLTableWithActivitiyRecords
    WHERE LoginTime BETWEEN '2024-01-27 15:00:00' AND '2024-01-27 16:00:00'
),
CTE_SuspiciousIPs AS (
    SELECT SourceIP, LoginHour
    FROM CTE_DistinctLogins
    GROUP BY SourceIP, LoginHour
    HAVING COUNT(DISTINCT UserName) >= 2
)
SELECT
    t.LoginTime,
    t.SourceIP,
    t.UserName
FROM MySQLTableWithActivitiyRecords t
JOIN CTE_SuspiciousIPs s ON t.SourceIP = s.SourceIP
    AND DATEADD(HOUR, DATEDIFF(HOUR, 0, t.LoginTime), 0) = s.LoginHour
WHERE t.LoginTime BETWEEN '2024-01-27 15:00:00' AND '2024-01-27 16:00:00'
ORDER BY t.SourceIP, t.LoginTime;

注意事项 #

• 请根据实际表结构替换字段名：比如LoginTime（登录时间字段）、SourceIP（源IP字段）、UserName（用户名字段）。
• 时间粒度可灵活调整：若需要检测更短/更长时间窗口，修改DATEADD(HOUR, ...)中的HOUR为MINUTE等单位，并同步调整时间范围条件。
• 若无符合条件的可疑情况，两个脚本均不会返回任何结果。

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