Statistics from logfile to SQL

[Raimonds Aronietis <ra...@atd.lv>]

Dear all,

I have been trying to find a script which would allow me to copy data
from spamd logfile to MySQL database. It would be usefull, since then I
could provide datailed statistics to my users.

Does anybody know a way to do it or I will just have to write a small
script myself? Has anyone probably done it before? Any tips?

Raimonds Aronietis

Re: Statistics from logfile to SQL

[Mike Jackson <mj...@barking-dog.net>]

> If you do large volumes of email, expect to have a table with millions 
> upon
> millions of records, and slow access times :(

The altered script I'm using outputs spam/ham total summaries for each user, 
and spam/ham message hits for each rule. In a properly built database, that 
shouldn't be too terribly difficult to maintain, and I wouldn't think you'd 
need to retain history for very long.


> On 12/15/05 12:48 PM, "Mike Jackson" <mj...@barking-dog.net> wrote:
>
>>> I have been trying to find a script which would allow me to copy data
>>> from spamd logfile to MySQL database. It would be usefull, since then I
>>> could provide datailed statistics to my users.
>>>
>>> Does anybody know a way to do it or I will just have to write a small
>>> script myself? Has anyone probably done it before? Any tips?
>>
>> There's competing scripts out there named sa-stats.pl (one's included in 
>> the
>> contrib directory with the SA distribution, the other's listed in 
>> Freshmeat
>> as "SpamAssassin logfile analyser"). While neither one does exactly what 
>> you
>> want, it should be trivial to hack them to save to SQL rather than
>> outputting as text.
>>
>> If you're interested, I've made modifications to David Ramsden's script 
>> that
>> also outputs stats on the rules, not just user statistics.
>>
>
> -- 
> Matthew Yette
> Senior Engineer (NOC/Operations)
> M.A. Polce Consulting
> 315-838-1644
>
> 

Re: Statistics from logfile to SQL

[mouss <us...@free.fr>]

Matthew Yette a écrit :
> If you do large volumes of email, expect to have a table with millions upon
> millions of records,

the w-illions of records take less space than the x-illions of messages 
to which they correspond.

  and slow access times :(

and the access time will be less than that of:
- SA matching test
- SA net tests
- MTA dns lookups
- MDA execution
- message queing
- message storing

and in any case, if one needs it, one has to do it.

Re: Statistics from logfile to SQL

[Matthew Yette <my...@mapolce.com>]

If you do large volumes of email, expect to have a table with millions upon
millions of records, and slow access times :(


On 12/15/05 12:48 PM, "Mike Jackson" <mj...@barking-dog.net> wrote:

>> I have been trying to find a script which would allow me to copy data
>> from spamd logfile to MySQL database. It would be usefull, since then I
>> could provide datailed statistics to my users.
>> 
>> Does anybody know a way to do it or I will just have to write a small
>> script myself? Has anyone probably done it before? Any tips?
> 
> There's competing scripts out there named sa-stats.pl (one's included in the
> contrib directory with the SA distribution, the other's listed in Freshmeat
> as "SpamAssassin logfile analyser"). While neither one does exactly what you
> want, it should be trivial to hack them to save to SQL rather than
> outputting as text.
> 
> If you're interested, I've made modifications to David Ramsden's script that
> also outputs stats on the rules, not just user statistics.
> 

-- 
Matthew Yette
Senior Engineer (NOC/Operations)
M.A. Polce Consulting
315-838-1644

Re: Statistics from logfile to SQL

[Mike Jackson <mj...@barking-dog.net>]

> I have been trying to find a script which would allow me to copy data
> from spamd logfile to MySQL database. It would be usefull, since then I
> could provide datailed statistics to my users.
>
> Does anybody know a way to do it or I will just have to write a small
> script myself? Has anyone probably done it before? Any tips?

There's competing scripts out there named sa-stats.pl (one's included in the 
contrib directory with the SA distribution, the other's listed in Freshmeat 
as "SpamAssassin logfile analyser"). While neither one does exactly what you 
want, it should be trivial to hack them to save to SQL rather than 
outputting as text.

If you're interested, I've made modifications to David Ramsden's script that 
also outputs stats on the rules, not just user statistics.