[Sqlgrey-users] Re: sqlgrey easier data mining

[Michael S. <Mic...@lr...>]

Hi Lionel,

to be a little bit more detailed than Max :-)

We just started to use greylisting and the first day shows a reduction of
spam by about a factor of 15, that's really great. However, looking
around in the logfiles and the mysql database, I am missing some
information, to help me see what actually happens.

Therefore I would like three additions to the tables in the database.

1. Addition: first_seen

Extra field first_seen also for tables form_awl and domain_awl. With this
addition you are able to see which new entries have been entered into
the database like it is possible now with table connect:

select * from connect where first_seen > now() - interval 5 minute;

With the from_awl and domain_awl you can only find out which entries have
been added OR have been updated.

2. Addition: client_name

Extra field client_name in all 3 tables. This would help a human to see
from where a connection came. Otherwise, you must always use nslookup or
dig to find the name.

3. Addition: usage_count

Every update of an entry in from_awl and domain_awl should increment an
usage_count.

The processing of these fields by sqlgrey should be triggered by
configuration options. For people, who do not need the information and do
not want to waste storage, they would disable these features.

4. Consistent naming

In table connect ip_addr is used whereas host_ip in from_awl and
domain_awl. Since it depends on the greylisting mode if the IP address is
a full host address or a class C network, you should use ip_addr for all
three tables. Now, if you try to find out what information is in every
table about an IP address, you can't just change the tablename in the
select, but you have to change the fieldname too.

Thanks,
Michael


On Fri, 4 Feb 2005, Max Diehn wrote:

> Hi Lionel,
>
> what do You think about the following issues to ease data mining:
>
> -> field 'client_name' in connect
> -> field 'first_seen' in from_awl, domain_awl
> -> logging (now()-first_seen) within 'too early' - statements in the
> logfile (easier to grep)
> -> putting all info concerning a single transaction into one single line
> instead of two lines (makes it easier to grep)
>
> BTW, could You rename connect.ip_addr into host_ip for consistency with
> from_awl and domain_awl? I understand, that, from a point of view of
> smart or c-class greylisting, these are different concepts. But from the
> sql schema point of view I find it rather uncomfortable to use different
> names for this field.
>
> LBNL, hope You enjoyed skiing and returned in good health when You read
> this (I start my first skiing holiday in my life tonight!)
>
> Max
>

Michael Storz
-------------------------------------------------
Leibniz-Rechenzentrum   !   <mailto:St...@lr...>
Barer Str. 21           !   Fax: +49 89 2809460
80333 Muenchen, Germany !   Tel: +49 89 289-28840