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 [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
