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
