[Sussex] SkyPickle stuff...

[Alan F]

On Tue, Dec 28, 2004 at 01:52:37AM +0000, Geoffrey Teale wrote:
> Chaps,
> 
> For those who have any interest.  My little Christmas project SkyPickle 
> Grumbleweed just got its 0.1 release and should be turning up on 
> Freshmeat in the next 24 hours.  See http://www.skypickle.com or details.
> 
> It's nothing particularly impressive (the more skilled members of this 
> list could write similar functionality in a few bash commands) but its 
> very fast and I find it useful in general cicrcumstances (the second 
> point is my criteria for what I release publically).
> 
Is there any particular reason you chose to write it in C? Or just
preference?

http://loonix.net/files/mailstats.pl is my first attempt at writing a
log reporting tool. As simple as it is, it produces some pretty useful
statistics. My thinking was that the best way to get flexible reports,
low processing overhead and smallest amount of code was to run the
script as a cronjob to parse the daily log which had just been
rotated, then put it in a postgresql database and let the database do
any aggregates when needed. It's far more specialised than your
creation, only of use to people using amavisd-new and spamassassin.

Here's a few examples of how I do something useful with the data it
populates the SQL database with... Like finding which spamassassin
tests get the most hits.

mailstats=> SELECT test,SUM(hits) FROM spamtests GROUP BY test ORDER BY SUM(hits) DESC;
           test            | sum
---------------------------+-----
 BAYES_99                  | 552
 RAZOR2_CHECK              | 552
 RAZOR2_CF_RANGE_51_100    | 550
 BAYES_00                  | 473
 URIBL_WS_SURBL            | 458
 RCVD_IN_BL_SPAMCOP_NET    | 455
 RCVD_IN_XBL               | 450

mailstats=> SELECT SUM(clean),SUM(virus),SUM(spam),SUM(relaydenied) FROM general WHERE EXTRACT(MONTH FROM time) = 12;
 sum | sum | sum | sum
-----+-----+-----+-----
 531 |  36 | 619 |  86
(1 row)

I've been meaning to write a fancy CGI script to generate graphs and
stuff, but I've been too lazy.

> The code is very rough and the docs are very basic, but they will only 
> improve if people actively bug me ;-)
> 
The documentation looks perfectly comprehensive, my idea of
"documentation" never amounts to more than a few comments at the top
of a script. This is mainly because anything I write is so simple and
short, and usually of little use to anyone else anyway. :-)

Alan