Finding suspected abuse from log-table

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Finding suspected abuse from log-table

Author

Topic

KJensen
Starting Member

12 Posts

Posted - 2008-06-17 : 09:24:35

A client has a site, that requires users to log in, and each login is logged with IP in this table:

CREATE TABLE [dbo].[Event](
[EventId] [int] IDENTITY(1,1) NOT NULL,
[EventTypeId] [int] NOT NULL,
[IP] [varchar](50) NULL,
[UserId] [int] NULL
[DateCreated] [datetime] NOT NULL CONSTRAINT [DF_Event_DateCreated] DEFAULT (getdate()),
CONSTRAINT [PK_Event] PRIMARY KEY CLUSTERED
(
[EventId] ASC
)

How the client wants me to write a query, that will find "suspected abuse of accounts" - defined by being successful logins made within one hour from at least 3 different IPs.

So I need to check for events, that are within one hour from each other (datecreated field) and have the same userID - and EventTypeId - but at least 3 different IPs.

Maybe it's just late in the day or I am overworked, but I cant seem to find an effective way to approach this.

Ideas?

RickD
Slow But Sure Yak Herding Master

3608 Posts

Posted - 2008-06-17 : 09:30:03

cross join the table to itself and use datediff in the where clause.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-06-17 : 09:39:52

For IPv4, IP can never be longer than 15 characters.
For IPv6, IP can never be longer than 31 characters.

E 12°55'05.25"
N 56°04'39.16"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-06-17 : 09:46:39

[code]-- Prepare sample data
DECLARE @Event TABLE
(
EventID INT IDENTITY(1, 1) PRIMARY KEY CLUSTERED,
EventTypeID INT NOT NULL,
IP VARCHAR(15),
UserID INT,
DateCreated DATETIME NOT NULL DEFAULT GETDATE()
)

INSERT @Event
SELECT 1, '1.1.1.1', 1, '20080617 15:40:00' UNION ALL
SELECT 1, '1.1.1.2', 1, '20080617 15:42:00' UNION ALL
SELECT 1, '1.1.1.2', 1, '20080617 15:59:00' UNION ALL
SELECT 1, '1.1.1.3', 1, '20080617 16:15:00' UNION ALL
SELECT 2, '1.1.1.1', 2, '20080617 15:40:00' UNION ALL
SELECT 3, '1.1.1.2', 2, '20080617 15:42:00' UNION ALL
SELECT 3, '1.1.1.2', 2, '20080617 15:59:00' UNION ALL
SELECT 2, '1.1.1.3', 2, '20080617 16:15:00' UNION ALL
SELECT 4, '1.1.1.1', 4, '20080617 15:40:00' UNION ALL
SELECT 4, '1.1.1.2', 3, '20080617 15:42:00' UNION ALL
SELECT 4, '1.1.1.2', 3, '20080617 15:59:00' UNION ALL
SELECT 4, '1.1.1.3', 4, '20080617 16:15:00'

-- Show the expected output
SELECT e1.EventID
FROM @Event AS e1
INNER JOIN @Event AS e2 ON e2.UserID = e1.UserID
AND e2.EventTypeID = e1.EventTypeID
WHERE DATEDIFF(MINUTE, e1.DateCreated, e2.DateCreated) BETWEEN 0 AND 60
GROUP BY e1.EventID
HAVING COUNT(DISTINCT e2.IP) >= 3[/code]

E 12°55'05.25"
N 56°04'39.16"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources