List:General Discussion« Previous MessageNext Message »
From:Aaron Clausen Date:June 16 2004 5:10pm
Subject:Tough Query Problem
View as plain text  
I am trying to write a script that can take logs from our mail server, boil down the
rejections to determine the sources of distributed SMTP dictionary attacks against our
mail server.

Basically I have a table "send_failures like this that gets fed with the raw data from the
logs:

host_ip			date
-------------------------------------------
111.111.111.110		2004-06-03 13:42:22
34.23.28.1		2004-06-03 13:42:25
65.2.88.25		2004-06-03 13:42:25
111.111.111.110		2004-06-03 13:42:27
65.2.88.25		2004-06-03 13:42:29
64.251.68.72		2004-06-03 13:42:30

And so on and so forth.

Now it's trivial to write a query to find the pure counting of the attacks:

SELECT host_ip, COUNT(host_ip) AS attempts FROM send_failures GROUP BY host_ip;

However, I also want to have latest date of the attack included as well, so that the above
exampe would boil down to a query with results like this (I'm running MySQL 3.23.58):

host_ip			attempts	last_attempt_date
-----------------------------------------------------------
111.111.111.110		2		2004-06-03 13:42:27
65.2.88.25		2		2004-06-03 13:42:29
64.251.68.72		1		2004-06-03 13:42:30
65.2.88.25		1		2004-06-03 13:42:25
34.23.28.1		1		2004-06-03 13:42:25

Obviously the actual table is going to have hundreds of thousands of entries (the log file
for a couple of days ago had 1.2 million rejections).

-- 
A. Clausen
techlists@stripped
Thread
Tough Query ProblemAaron Clausen16 Jun
  • Re: Tough Query ProblemDan Nelson16 Jun
  • Re: Tough Query ProblemA. Clausen16 Jun