All geek questions in one place

SQL SELECT with a time range

I have bottom click_log logs for some urls

site    ip    ua    direction   hit_time
-----------------------------------------------------
 1      127.0.0.1      1         20010/01/01 00:00:00

 2      127.0.0.1      1         20010/01/01 00:01:00

 3      127.0.0.1      0         20010/01/01 00:10:00

....    .........

I want to select incoming hits (direction: 1) and a group of sites that:

  • from the same ip and browser
  • registered within 10 minutes of each other.
  • happened more than 4 times in 10 minutes.

I'm not sure the above was clear enough. English is not my first language. Let me try to explain with an example.

If site 1 receives 5 hits from the same ip and browser, 10 minutes after receiving the first unique hit from this ip and browser, I want it to be included in the selection.

Mostly I'm trying to find offenders.

+3
source share
5 answers

, , . .

Create Table #t
(
[Site] int,
IP varchar(20),
Direction int,
Hit_Time datetime
)

Insert Into #t
Values (1,'127.0.0.1',1,'2010-01-01 00:00:00')

Insert Into #t
Values (1,'127.0.0.1',1,'2010-01-01 00:01:00')

Insert Into #t
Values (1,'127.0.0.1',1,'2010-01-01 00:03:00')

Insert Into #t
Values (1,'127.0.0.1',1,'2010-01-01 00:04:00')


Insert Into #t
Values (2,'127.0.0.2',1,'2010-01-01 00:00:00')

Insert Into #t
Values (2,'127.0.0.2',1,'2010-01-01 00:01:00')

Insert Into #t
Values (2,'127.0.0.2',0,'2010-01-01 00:03:00')

Insert Into #t
Values (2,'127.0.0.2',1,'2010-01-01 00:04:00')


Select Distinct Site
From #t
Where Direction = 1
Group by Site, IP
Having (DateDiff(minute,Min(HIt_Time), max(hit_time)) <= 10) And Count(*) >= 4

Drop Table #t
+2

SELECT IP, (SELECT COUNT(*) FROM Click_Log WHERE Click_Log.IP = CL.IP 
     AND DIRECTION = 1 AND DATEDIFF(MINUTE, ClickLog.HIT_TIME, CL.HIT_TIME)
     BETWEEN -10 AND 10) AS CLICK_COUNT
FROM Click_Log CL
WHERE DIRECTION = 1 AND CLICK_COUNT > 4
0
;WITH rankings AS (
    SELECT *, DENSE_RANK() OVER(ORDER BY [site], ip, ua) groupId,
        ROW_NUMBER() OVER(PARTITION BY [site], ip, ua ORDER BY hit_time) sequence
    FROM Hits
    WHERE direction = 1),
periods AS (
    SELECT r.groupId, r.sequence, count(*) hitCount
    FROM rankings r
    LEFT OUTER JOIN rankings r2
        ON r2.groupId = r.groupId and r2.sequence < r.sequence
        AND r2.hit_time >= DATEADD(second, -10*60, r.hit_time)
        AND r2.hit_time < r.hit_time
    GROUP BY r.groupId, r.sequence
),
groups AS (
    SELECT p.groupId, MAX(p.hitCount) maxHitCount
    FROM periods p
    GROUP BY p.groupId
)
SELECT DISTINCT r.[site], r.ip, r.ua, g.maxHitCount 
FROM rankings r
INNER JOIN groups g ON g.groupId = r.groupId
WHERE maxHitCount >= 5
ORDER BY maxHitCount DESC
0

OP.

:

Create Table dbo.Temp
(
[Site] int,
IP varchar(20),
Direction int,
Hit_Time datetime
)

Insert Into dbo.Temp
Values (1,'127.0.0.1',1,'2010-01-01 00:00:00')

Insert Into dbo.Temp
Values (1,'127.0.0.1',1,'2010-01-01 00:01:00')

Insert Into dbo.Temp
Values (1,'127.0.0.1',1,'2010-01-01 00:03:00')

Insert Into dbo.Temp
Values (1,'127.0.0.1',1,'2010-01-01 00:04:00')


Insert Into dbo.Temp
Values (2,'127.0.0.2',1,'2010-01-01 15:00:00')

Insert Into dbo.Temp
Values (2,'127.0.0.2',1,'2010-01-01 15:31:00')

Insert Into dbo.Temp
Values (2,'127.0.0.2',1,'2010-01-01 15:32:00')

Insert Into dbo.Temp
Values (2,'127.0.0.2',1,'2010-01-01 15:33:00')

Insert Into dbo.Temp
Values (2,'127.0.0.2',1,'2010-01-01 15:34:00')

:

Create Function dbo.fn_CheckSuspectActivity (@Site int, @IP varchar(20), @MinDate datetime,
                                                    @MaxDate datetime, @Direction int, @Interval int,
                                                    @MaxCount int)

                                                    returns int
        as begin
        Declare @OrigMaxDate datetime,
                @IsSuspect int

        Set @OrigMaxDate = @MaxDate
        Set @IsSuspect = 0

        if (DATEDIFF(minute, @MinDate, @MaxDate) > 10)
                --Min and Max dates for site & Ip
                -- are more than 10 minutes apart
                begin
                        --Loop through the records
                        While (@MaxDate <= @OrigMaxDate And @IsSuspect = 0)
                        begin
                                -- Set The MaxDate to the MinDate plus 10 mins
                                Set @MaxDate = DATEADD(Minute, 10, @MinDate)

                                If (Select COUNT(*) 
                                    From dbo.Temp
                                    Where Site = @Site
                                    And IP = @IP
                                    And Hit_Time >= @MinDate
                                    And Hit_Time <= @MaxDate
                                    And Direction = @Direction
                                    ) >= @MaxCount

                                        Begin
                                                -- Hit Count exceeded for the specified 10 min range
                                                set @IsSuspect = 1
                                        End

                                    Else

                                        Begin
                                        -- Set the minDate to the maxDate 
                                        Set @MinDate = @MaxDate
                                        --Add another 10 minutes on
                                        Set @MaxDate = DATEADD(minute, 10,@MaxDate)

                                        End

                        end
                        -- We've  finished the loop but if @IsSuspect is still zero we need to do one final check
                        if (@IsSuspect = 0)
                            begin
                                    -- Check the number of records based on the last MinDate used
                                    -- and the original MaxDate

                                    If (Select COUNT(*) 
                                    From dbo.Temp
                                    Where Site = @Site
                                    And IP = @IP
                                    And Hit_Time >= @MinDate
                                    And Hit_Time <= @OrigMaxDate
                                    And Direction = @Direction
                                    ) >= @MaxCount
                                            begin
                                                    -- Hit Count exceeded for the specified 10 min range
                                                    set @IsSuspect = 1
                                            end
                                        else
                                            begin
                                                    set @IsSuspect = 0
                                            end

                            end

                end

            else
                -- Time difference isn't more than 10 minutes so do a "normal" check
                begin

                        If (Select COUNT(*)
                            From dbo.Temp 
                            Where Site = @Site
                            And IP = @IP
                            And Hit_Time >= @MinDate
                            And Hit_Time <= @MaxDate
                            And Direction = @Direction) >= @MaxCount

                            BEGIN   -- Its a suspect IP
                                    Set @IsSuspect = 1
                            END

                                ELSE

                            BEGIN
                                    -- It ok
                                    Set @IsSuspect = 0
                            END

                end


return @IsSuspect

End
Go

select :

With Qry as
(

Select  Site,
        IP,
        MIN(Hit_Time) as'MinTime',
        MAX(Hit_TIme) as 'MaxTime'

From dbo.Temp
Group By Site, IP
)

Select Site
From Qry
Where dbo.fn_CheckSuspectActivity(Site, IP, MinTime, MaxTime, 1, 10, 4) = 1
-- function params are as follows: Site Number, IP Address, FirstTimeLogged, 
--                                  LastTimeLogged, Direction, IntervalToCheck, MaxOccurences

10 , , . 10 , 10 , 10- .

, , .

Barry

0

Source: https://habr.com/ru/post/1744745/

More articles:


All Articles