一： 创建表

1、信息采集表

CREATE TABLE [dbo].[Send_Phone_Code_Log] (
  [ID] bigint  IDENTITY(1,1) NOT NULL,
  [IP] varchar(100) COLLATE Chinese_PRC_CI_AS  NULL,
  [Phone] varchar(20) COLLATE Chinese_PRC_CI_AS  NULL,
  [CreateDate] datetime  NULL,
  [UrlReferrer] varchar(100) COLLATE Chinese_PRC_CI_AS  NULL
)

2、异常ip表

CREATE TABLE [dbo].[Send_Phone_Code_Abnormal_Data] (
  [ID] bigint  IDENTITY(1,1) NOT NULL,
  [IP] varchar(100) COLLATE Chinese_PRC_CI_AS  NULL,
  [Phone] varchar(20) COLLATE Chinese_PRC_CI_AS  NULL,
  [CreateDate] datetime  NULL,
  [StartDate] datetime  NULL,
  [EndDate] datetime  NULL
)

二： 创建存储过程

1、统计存储过程

CREATE PROCEDURE DetectIPAnomalies
(
@LogStartTime DATETIME,  --访问开始时间
@DetectEndOfAnomaly DATETIME, --冻结结束时间
@VisitThreshold INT   --超限访问次数
)
AS
BEGIN
    -- 设置时间变量
    DECLARE @NowDate DATETIME = GETDATE(); -- 当前时间

    -- 插入异常IP到IP异常表（同时检查IP列表和访问次数）
    INSERT INTO Send_Phone_Code_Abnormal_Data (IP, CreateDate, StartDate, EndDate)
    SELECT
        IP,
        @NowDate AS CreateDate, -- 设置异常创建时间为当前时间
        @NowDate AS StartDate, -- 异常开始时间为当前时间
        @DetectEndOfAnomaly AS EndDate -- 异常结束时间为2小时后
    FROM (
		    -- 查询异常ip
        SELECT
            IP,
            COUNT(*) AS VisitCount
        FROM
            Send_Phone_Code_Log
        WHERE
            CreateDate BETWEEN @LogStartTime AND @NowDate
        GROUP BY
            IP
        HAVING
            COUNT(*) > @VisitThreshold
    ) AS CTE5_IPStats
    WHERE
		    -- 排除已经是异常的ip
        NOT EXISTS (
            SELECT 1
            FROM Send_Phone_Code_Abnormal_Data
            WHERE
                IP = CTE5_IPStats.IP AND
                EndDate > @NowDate AND
                StartDate < @NowDate
        );
END;

2、规则匹配存储过程

ALTER PROCEDURE ExecuteDetectIPAnomaliesMultipleTimes
AS
BEGIN

DECLARE @NowDate DATETIME = GETDATE(); -- 当前时间
delete  from Send_Phone_Code_Log where CreateDate<DATEADD(day, -7, @NowDate)
delete  from Send_Phone_Code_Abnormal_Data where EndDate<DATEADD(day, -3, @NowDate)

-- 6小时内异常
DECLARE @LogStart DATETIME = DATEADD(HOUR, -6, @NowDate); 
DECLARE @AnomalyEnd DATETIME  = DATEADD(HOUR, 24, @NowDate);  
DECLARE @Threshold INT = 20; 
EXEC DetectIPAnomalies @LogStart, @AnomalyEnd, @Threshold;

-- 2小时内异常
SET @LogStart  = DATEADD(HOUR, -2, @NowDate); 
SET @AnomalyEnd  = DATEADD(HOUR, 24, @NowDate);  
SET @Threshold  = 15; 
EXEC DetectIPAnomalies @LogStart, @AnomalyEnd, @Threshold;

-- 30分钟内异常
SET @LogStart  = DATEADD(MINUTE, -30, @NowDate); 
SET @AnomalyEnd  = DATEADD(HOUR, 6, @NowDate); 
SET @Threshold  = 10; 
EXEC DetectIPAnomalies @LogStart, @AnomalyEnd, @Threshold;

-- 5分钟内异常
SET @LogStart  = DATEADD(MINUTE, -5, @NowDate); 
SET @AnomalyEnd  = DATEADD(HOUR, 2, @NowDate);  
SET @Threshold  = 5; 
EXEC DetectIPAnomalies @LogStart, @AnomalyEnd, @Threshold;

END; 

三： 使用

exec ExecuteDetectIPAnomaliesMultipleTimes