Find time interval between dates

Hi, all. I've got a classic ASP app that I've been tasked with modifying to
track users' IP addresses. If they fail to login, we want to record their IP
and the date/time when they failed. If they fail more than 5 times within 10
minutes, we want to lock them out for 5 minutes. Below are the two SQL
Server 2000 tables I've created.
The problem I'm having is this: How do you figure out if user has failed 5
times within the last 10 minutes. If you believe there's a better way to
design the tables for this task, please let me know. Thank you :)
CREATE TABLE [tblHits] (
[hitID] [int] IDENTITY (1, 1) NOT NULL ,
[IP] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[email] [varchar] (195) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[dateAdded] [smalldatetime] NULL CONSTRAINT [DF_tblHits_dateAdded] DEFAULT
(getdate()),
CONSTRAINT [PK_tblHits] PRIMARY KEY CLUSTERED
(
[hitID]
) ON [PRIMARY] ,
CONSTRAINT [FK_tblHits_tblIPs] FOREIGN KEY
(
[IP]
) REFERENCES [tblIPs] (
[IP]
)
) ON [PRIMARY]
GO
CREATE TABLE [tblIPs] (
[IP] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[IPCount] [int] NULL CONSTRAINT [DF_tblIPs_IPCount] DEFAULT (1),
[dateLastMod] [smalldatetime] NULL CONSTRAINT [DF_tblIPs_dateLastMod]
DEFAULT (getdate()),
CONSTRAINT [PK_tblIPs] PRIMARY KEY CLUSTERED
(
[IP]
) ON [PRIMARY]
) ON [PRIMARY]
GOHi
smalldatetime data type has only the accuracy of 1 minute, so you might be
working between 9 and 11 minutes. The data type datetime might be more
appropriate.
Unless this is an intranet application, there is no guarantee that the IP
address could not be the source for multiple users. Corporates have large
proxy servers that do not expose the internal source IP. You might make a
lot of users unhappy if one of them fails to login correctly.
That aside. The following will return the number of hits in the last 10
minutes:
SELECT
COUNT(*)
FROM
tblHits
WHERE
dateAdded > DATEADD(mi, -10, GETDATE())
AND IP = @.YourCurrentIPAddress
Regards
Mike
This posting is provided "AS IS" with no warranties, and confers no rights.
"curious_Lee" <oneworld95_NOSPAM@.yahoo.com> wrote in message
news:eLAzbYbIGHA.3460@.TK2MSFTNGP12.phx.gbl...
> Hi, all. I've got a classic ASP app that I've been tasked with modifying
> to track users' IP addresses. If they fail to login, we want to record
> their IP and the date/time when they failed. If they fail more than 5
> times within 10 minutes, we want to lock them out for 5 minutes. Below are
> the two SQL Server 2000 tables I've created.
> The problem I'm having is this: How do you figure out if user has failed 5
> times within the last 10 minutes. If you believe there's a better way to
> design the tables for this task, please let me know. Thank you :)
> CREATE TABLE [tblHits] (
> [hitID] [int] IDENTITY (1, 1) NOT NULL ,
> [IP] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [email] [varchar] (195) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [dateAdded] [smalldatetime] NULL CONSTRAINT [DF_tblHits_dateAdded] DEFAULT
> (getdate()),
> CONSTRAINT [PK_tblHits] PRIMARY KEY CLUSTERED
> (
> [hitID]
> ) ON [PRIMARY] ,
> CONSTRAINT [FK_tblHits_tblIPs] FOREIGN KEY
> (
> [IP]
> ) REFERENCES [tblIPs] (
> [IP]
> )
> ) ON [PRIMARY]
> GO
> CREATE TABLE [tblIPs] (
> [IP] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> [IPCount] [int] NULL CONSTRAINT [DF_tblIPs_IPCount] DEFAULT (1),
> [dateLastMod] [smalldatetime] NULL CONSTRAINT [DF_tblIPs_dateLastMod]
> DEFAULT (getdate()),
> CONSTRAINT [PK_tblIPs] PRIMARY KEY CLUSTERED
> (
> [IP]
> ) ON [PRIMARY]
> ) ON [PRIMARY]
> GO
>
>
>|||Hi
. If you believe there's a better way to
> design the tables for this task, please let me know. Thank you :)
>
create table tblConnection
(
StartTimeCon datetime not null,
EndTimeCon datetime not null
)
insert into tblConnection(StartTimeCon,EndTimeCon)va
lues ('20000610
10:00','20000610 10:10')
insert into tblConnection(StartTimeCon,EndTimeCon)va
lues ('20000610
10:20','20000610 10:22')
insert into tblConnection(StartTimeCon,EndTimeCon)va
lues ('20000610
10:23','20000610 10:25')
insert into tblConnection(StartTimeCon,EndTimeCon)va
lues ('20000610
10:27','20000610 10:45')
insert into tblConnection(StartTimeCon,EndTimeCon)va
lues ('20000610
11:57','20000610 12:00')
insert into tblConnection(StartTimeCon,EndTimeCon)va
lues ('20000610
12:01','20000610 12:04')
insert into tblConnection(StartTimeCon,EndTimeCon)va
lues ('20000610
12:04','20000610 12:40')
SELECT
StartTimeCon,
ISNULL(
(SELECT MIN(EndTimeCon)
FROM tblConnection AS S3
WHERE S3.StartTimeCon >= S1.StartTimeCon
AND ISNULL(
DATEDIFF(
minute,
S3.EndTimeCon,
(SELECT MIN(StartTimeCon)
FROM tblConnection AS S4
WHERE S4.StartTimeCon > S3.EndTimeCon)), 5) >= 5),
EndTimeCon) AS EndTimeCon
FROM tblConnection AS S1
WHERE ISNULL(
DATEDIFF(
minute,
(SELECT MAX(EndTimeCon)
FROM tblConnection AS S2
WHERE S2.EndTimeCon < S1.StartTimeCon),S1.StartTimeCon),5) >= 5
Note: this SELECT returns rows that StartCon and EndCon >5 minutes. I hope
you get the idea and an modify the query.
"curious_Lee" <oneworld95_NOSPAM@.yahoo.com> wrote in message
news:eLAzbYbIGHA.3460@.TK2MSFTNGP12.phx.gbl...
> Hi, all. I've got a classic ASP app that I've been tasked with modifying
> to track users' IP addresses. If they fail to login, we want to record
> their IP and the date/time when they failed. If they fail more than 5
> times within 10 minutes, we want to lock them out for 5 minutes. Below are
> the two SQL Server 2000 tables I've created.
> The problem I'm having is this: How do you figure out if user has failed 5
> times within the last 10 minutes. If you believe there's a better way to
> design the tables for this task, please let me know. Thank you :)
> CREATE TABLE [tblHits] (
> [hitID] [int] IDENTITY (1, 1) NOT NULL ,
> [IP] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [email] [varchar] (195) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [dateAdded] [smalldatetime] NULL CONSTRAINT [DF_tblHits_dateAdded] DEFAULT
> (getdate()),
> CONSTRAINT [PK_tblHits] PRIMARY KEY CLUSTERED
> (
> [hitID]
> ) ON [PRIMARY] ,
> CONSTRAINT [FK_tblHits_tblIPs] FOREIGN KEY
> (
> [IP]
> ) REFERENCES [tblIPs] (
> [IP]
> )
> ) ON [PRIMARY]
> GO
> CREATE TABLE [tblIPs] (
> [IP] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> [IPCount] [int] NULL CONSTRAINT [DF_tblIPs_IPCount] DEFAULT (1),
> [dateLastMod] [smalldatetime] NULL CONSTRAINT [DF_tblIPs_dateLastMod]
> DEFAULT (getdate()),
> CONSTRAINT [PK_tblIPs] PRIMARY KEY CLUSTERED
> (
> [IP]
> ) ON [PRIMARY]
> ) ON [PRIMARY]
> GO
>
>
>|||Thank you, Michael and Uri. You've got some very creative solutions. By the
way, the point about public computers or proxy servers is a valid one; we
discussed it, but the powers-that-be wanted to go ahead and do this anyway.
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:OhYgKlbIGHA.3144@.TK2MSFTNGP10.phx.gbl...
> Hi
> . If you believe there's a better way to
>
> create table tblConnection
> (
> StartTimeCon datetime not null,
> EndTimeCon datetime not null
> )
> insert into tblConnection(StartTimeCon,EndTimeCon)va
lues ('20000610
> 10:00','20000610 10:10')
> insert into tblConnection(StartTimeCon,EndTimeCon)va
lues ('20000610
> 10:20','20000610 10:22')
> insert into tblConnection(StartTimeCon,EndTimeCon)va
lues ('20000610
> 10:23','20000610 10:25')
> insert into tblConnection(StartTimeCon,EndTimeCon)va
lues ('20000610
> 10:27','20000610 10:45')
> insert into tblConnection(StartTimeCon,EndTimeCon)va
lues ('20000610
> 11:57','20000610 12:00')
> insert into tblConnection(StartTimeCon,EndTimeCon)va
lues ('20000610
> 12:01','20000610 12:04')
> insert into tblConnection(StartTimeCon,EndTimeCon)va
lues ('20000610
> 12:04','20000610 12:40')
> SELECT
> StartTimeCon,
> ISNULL(
> (SELECT MIN(EndTimeCon)
> FROM tblConnection AS S3
> WHERE S3.StartTimeCon >= S1.StartTimeCon
> AND ISNULL(
> DATEDIFF(
> minute,
> S3.EndTimeCon,
> (SELECT MIN(StartTimeCon)
> FROM tblConnection AS S4
> WHERE S4.StartTimeCon > S3.EndTimeCon)), 5) >= 5),
> EndTimeCon) AS EndTimeCon
> FROM tblConnection AS S1
> WHERE ISNULL(
> DATEDIFF(
> minute,
> (SELECT MAX(EndTimeCon)
> FROM tblConnection AS S2
> WHERE S2.EndTimeCon < S1.StartTimeCon),S1.StartTimeCon),5) >= 5
>
>
> Note: this SELECT returns rows that StartCon and EndCon >5 minutes. I hope
> you get the idea and an modify the query.
>
>
> "curious_Lee" <oneworld95_NOSPAM@.yahoo.com> wrote in message
> news:eLAzbYbIGHA.3460@.TK2MSFTNGP12.phx.gbl...
>