Tough Query Problem

[Aaron Clausen]

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.722004-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 attemptslast_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.721   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]

Re: Tough Query Problem

[Dan Nelson]

In the last episode (Jun 16), Aaron Clausen said:
 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
 
 And so on and so forth.
 
 Now it's trivial to write a query to find the pure counting of the
 attacks: 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):

Add MAX(date) as last_attempt_date to your select field list.

-- 
Dan Nelson
[EMAIL PROTECTED]

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Tough Query Problem

[A. Clausen]

- Original Message - 
From: Dan Nelson [EMAIL PROTECTED]
To: Aaron Clausen [EMAIL PROTECTED]
Cc: MySQL Mailing List [EMAIL PROTECTED]
Sent: Wednesday, June 16, 2004 10:20
Subject: Re: Tough Query Problem


 In the last episode (Jun 16), Aaron Clausen said:
  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
  
  And so on and so forth.
  
  Now it's trivial to write a query to find the pure counting of the
  attacks: 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):
 
 Add MAX(date) as last_attempt_date to your select field list.

That did the job!  Thanks.

-- 
A. Clausen
[EMAIL PROTECTED]

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

a tough(?) query problem

[[EMAIL PROTECTED]]

hi

I need to select the ten latest entries from a diary-database, but i don't want
to get any duplicate users, so if i want to select the ten latest entries and
someone has written five entries of these latest ones, i only want to get the
latest entries from each user. So far no problem, my query below fixes that. But
the problem is that i also want to get the correct headline from the latset entries 
from
each person.

Since mysql dosen't support subselects i have tried to use userdefined variables
and the max()-function in a where-clause, but i found that it's not possible to
use these methods.

If anyone got a solution i would be really happy.

The fields in the database looks like this:
referer - int
time - datetime
headline - varchar

My current nonworking query looks like this:
select referer,max(time) as maxtime,headline from diary group by referer order
by maxtime desc limit 10;


/tobias


-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php