I have added this answer in response to the OP comment.
I've used the following test data:
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')
First you need to create a Function to do the working out:
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's ok
Set @IsSuspect = 0
END
end
return @IsSuspect
End
Go
Then this select statement should give you the correct answer:
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
If the first and last dates are less than 10 mins apart then it checks if they have exceed the hit count. If first date and last date are more than 10 mins apart it increments the first date by intervals of 10 mins and checks to see if they have exceeded the hitcount during that 10 min period.
I hope this is what you need.
Barry