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

host_ip			date
-------------------------------------------		2004-06-03 13:42:22		2004-06-03 13:42:25		2004-06-03 13:42:25		2004-06-03 13:42:27		2004-06-03 13:42:29		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
-----------------------------------------------------------		2		2004-06-03 13:42:27		2		2004-06-03 13:42:29		1		2004-06-03 13:42:30		1		2004-06-03 13:42:25		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
Tough Query ProblemAaron Clausen16 Jun
  • Re: Tough Query ProblemDan Nelson16 Jun
  • Re: Tough Query ProblemA. Clausen16 Jun