Topic: Announce - alpha Spam and Anti virus analysis script

[brianr]

I have been working on a replacement for the spam and anti Virus filter scripts which came as part of Jesper's excellent contrib for SME6.

It is based on Jesper's script (which I had worked on before), and the single script incorporates both spam and AV data in one report.  It also lists the number of junkmails left in each junkmail folder.

It is downloadable from here:

http://www.abandonmicrosoft.co.uk/temp/spamfilter-stats-7.pl

and should be dropped into /etc/e-smith/cron.daily

Remember to make it executable (chmod +x).

As I have limited email data on my test server, it needs proper testing, if anyone has a large volume of data  in /var/log/qpsmtpd/current consisting of spam, and virus notifications, I would be glad to get a copy for further testing.

The script does not yet take note of the spam scores.  

All comments to me here please until I can get a category in the bug tracker created.

[idyll]

Hello Bryan.

I sent you an email with a zipped logfile.

regards,

patrick

[brianr]

Hi Patrick

Thanks for that - plenty of spam, although you do not seem to have received any Viruses, so i am still looking for logs with viruses in!!

[idyll]

Hello Brian.

Well, the pattern matching built into 7.0 really eliminates virus, and perhaps even the need to report it? The proof lies in the logs.

I have enabled all but ZIP2 and it hasn't impacted anyone negatively yet.

?

regards,

patrick

[brianr]

mmm, perhaps I ought to be reporting on the pattern matching as well...

actually clamav also eliminates phishing emails, which I presume  the pattern matching will not..

[raem]

brianr

> ...reporting on the pattern matching as well...
> ... clamav also eliminates phishing emails, which I presume  the pattern > matching will not..


On a sme6 server I see that html viruses get past the pattern matching but are detected by clamav, mostly phishing ...."something"....

[brianr]

Thanks to Idyll's log file I have been able to refine it a bit and add in the spam score processing, however I still need a log with a virus detected to be sure that I have got the search right - any takers?

New version as before:

http://www.abandonmicrosoft.co.uk/temp/spamfilter-stats-7.pl

Suggest you copy it to /usr/bin and also copy

http://www.abandonmicrosoft.co.uk/temp/mailstats.cron

to /etc/cron.d , then restart cron:

/etc/rc.d/init.d/crond restart

perl scripts don't seem to work out of cron.daily.

[idyll]

Bryan - runs without any glitches on my 7.0 pr 1 server as far as I can tell. Very useful information.

thanks for the work.

regards,

patrick

[brianr]

Thanks patrick - I'm still not happy about the virus counts, and I'd like to add RBL and Pattern matching counts as well,

***** but i need some logs with suitable data in them **********!! (hint, hint)

[JonB]

Brian,

I have been working on something similar and logging the results of the qpsmtpd plugins on a pie chart.

http://khunjarnet.com/qpsmtpd.htm

This is pretty rough at the moment.

The problem with qpsmtpd is that it rotates the qpsmtpd logs once the current log size reaches 5MB not every 24 hours so in my case qpsmtpd/current usually only has 3-4 hours worth of logs before it rotates. The script that you have modified, when run in my case would produce incorrect results giving a 24 hour result for 3-4 hours worth of data.

Jon[/url]

[brianr]

Jonb

I like your pie chart, but I am keen to watch the spam scores and also have some idea about when things come in.  I also want it emailed as I have 16 servers out in the field, and need the info emailed to me for checking each day.

As far as the log rotation is concerned qpsmtpd uses multilog to log its messages, and this is set off from /var/service/qpsmtpd/log/run, you can modfiy the "s" parameter there, although to do it properly you ought to template it.

If you sent me the "current" log, plus the others that cover a day, I could see if i can modify the script to take into account the log archives.

[JonB]

Brian,

The pie chart is an overall view of what is happening with the mail server. It shows all smtp connections made to the server, the percentage of connections that result in deliverable mail and the percentage of rejections and the reasons. I can look at the chart and see straight away that 85% of all connections are rejected.

The Spam and AV analysis script serves a different but useful function. I must admit that I have hacked it and used it on non SME mail servers that are running qmail, spamassassin and clamav.

I can send you 40MB of logs covering around 24 hrs. Dont expect to find a virus amongst them though. There arn't any  pattern matching and RBL seems to take care of most of them.

You should look at using collapse_qpsmtpd_conn.pl to collapse the qpsmtpd log files. collapse_qpsmtpd_conn extracts commands and responses from qpsmtpd log files and prints a single line for each connection with the entire SMTP dialog. The output is intended to be easily filtered with grep or awk

I use it and pipe the results to the script that creates the chart.

You could use it to create a single log file with one line per connection/pid.

collapse_qpsmtpd_conn.pl /var/log/qpsmtpd/* > qpsmtpd.log

http://www.hjp.at/projekte/qpsmtpd/log-tools/

Jon

[brianr]

Jon

Yes, I have already found the collapse script, and will be trying it later today I hope.  I would like your logs, if you email me, I'll give you details of an ftp site where you can upload them to - unless you already have them somewhere i can download them?

[jfarschman]

Brian,

  The RBL work isn't as clean as the work you did detecting spamassassin's presence, but you can still pull these with:

dnsbl plugin: name  53.192.206.87.sbl-xbl.spamhaus.org

That's a positive hit from one of the DBs.  You could detect it by grep-ing  -   grep "dnsbl plugin: current

Code: [Select]


@40000000442d11b2393df41c 14789 running plugin (rcpt): dnsbl
@40000000442d11b23941cc7c 14789 trying to get config for dnsbl_zones
@40000000442d11b23946a2c4 14789 dnsbl plugin: waiting for dnsbl dns
@40000000442d11b2394a3ca4 14789 dnsbl plugin: DONE waiting for dnsbl dns, got  4  answers ...
@40000000442d11b239b2706c 14789 dnsbl plugin: name  53.192.206.87.sbl-xbl.spamhaus.org
@40000000442d11b239b53b44 14789 dnsbl plugin: got txt record
@40000000442d11b239b85054 14789 trying to get config for dnsbl_rejectmsg
@40000000442d11b239bfc67c 14789 Plugin dnsbl, hook rcpt returned DENY, http://www.spamhaus.org/query/bl?ip=87.206.192.53
@40000000442d11b239c28984 14789 550 http://www.spamhaus.org/query/bl?ip=87.206.192.53
@40000000442d11b30bbd0334 24294 cleaning up after 14789



False looks like this:

Code: [Select]


@40000000442d14520330a0a4 14920 dnsbl plugin: RBLSMTPD not set for 63.231.195.113
@40000000442d1452034abc3c 14920 dnsbl plugin: Checking 113.195.231.63.relays.ordb.org for TXT record in the background
@40000000442d14520373992c 14920 dnsbl plugin: Checking 113.195.231.63.dnsbl.njabl.org for TXT record in the background
@40000000442d145203868cbc 14920 dnsbl plugin: Checking 113.195.231.63.whois.rfc-ignorant.org for TXT record in the background
@40000000442d14520397e624 14920 dnsbl plugin: Checking 113.195.231.63.sbl-xbl.spamhaus.org for TXT record in the background
@40000000442d14520c7db354 14920 dnsbl plugin: waiting for dnsbl dns
@40000000442d14520c80fb2c 14920 dnsbl plugin: DONE waiting for dnsbl dns, got  3  answers ...
@40000000442d14520ca27964 14920 dnsbl plugin: waiting for dnsbl dns
@40000000442d14520cf880d4 14920 dnsbl plugin: DONE waiting for dnsbl dns, got  1  answers ...


[jfarschman]

One other note:

If you have not already done so, you should set qpsmtpd to use the dnsbl:

/sbin/e-smith/config setprop qpsmtpd DNSBL enabled
/sbin/e-smith/signal-event email-update

[CharlieBrady]

The problem with qpsmtpd is that it rotates the qpsmtpd logs once the current log size reaches 5MB not every 24 hours so in my case qpsmtpd/current usually only has 3-4 hours worth of logs before it rotates.

You could adjust the parameters in /service/qpsmtpd/log/run to keep more old logs, or change the size at which log rotations occur. You can also adjust how verbose the qpsmtpd logs are.

[brianr]

ok, new version here:

http://mirror.contribs.org/smeserver/contribs/bread/mailstats

Now uses all the log files in the qpsmtpd log directory (and therefore takes longer!), and also gathers stats about RBL and patternfilters as well as spam and ham and viruses.  The spam delete code is no loger dependant on the actual threshold set.

comments/bugs/suggestions/etc here please..

[CharlieBrady]

ok, new version here:

http://mirror.contribs.org/smeserver/contribs/bread/mailstats

Now uses all the log files in the qpsmtpd log directory (and therefore takes longer!), and also gathers stats about RBL and patternfilters as well as spam and ham and viruses.  The spam delete code is no loger dependant on the actual threshold set.

comments/bugs/suggestions/etc here please..

Brian, please attach your code to

http://bugs.contribs.org/show_bug.cgi?id=819

and ask people to provide feedback there.

[JonB]

Brian,

The script relies on people having their Qpsmtpd log level set to 8. The default is 6 for pre3 and above which does not include the DENY or DENYSOFT messages.

To set log level to 8

Code: [Select]

sbin/e-smith/config setprop qpsmtpd LogLevel 8
/sbin/e-smith/signal-event email-update


Sorry, I should have mentioned that the logs were at log level 8 when I sent the logs.

Jon

[brianr]

Brian, please attach your code to

http://bugs.contribs.org/show_bug.cgi?id=819

done, please put comments there...

[brianr]

Sorry, I should have mentioned that the logs were at log level 8 when I sent the logs.

Anyone willing to donate me some logs with the LogLevel set to 6?

[chris burnat]

Hello Brian, earlier in this thread, you wrote:

and should be dropped into /etc/e-smith/cron.daily
Remember to make it executable (chmod +x).

In sme7, it should go into /etc/cron.daily ?

Just installed it -  288 emails analysed over past 24 hours, t'is a great job!  Thank you.

Question:  Misc.rejected                    :       24 (  8.33%)    What is this?
Regards
chris

[brianr]

In sme7, it should go into /etc/cron.daily ?

See my later post - use mailstats.cron and cron.d

Question: Misc.rejected : 24 ( 8.33%) What is this?

There are an number of other "tests" that qpsmtp does, this is the sum of all the rejects as a result of those.

Just installed it - 288 emails analysed over past 24 hours, t'is a great job! Thank you.

thanks - I am still working on it - so look out for updates on my contribs area.

[brianr]

Quote:
Sorry, I should have mentioned that the logs were at log level 8 when I sent the logs.


Anyone willing to donate me some logs with the LogLevel set to 6?

Actually it turns out that rc1 loglevel is at 8, it got put back up in order that enough detail is in the log.

I could still do with some more examples of logs though...(you could edit out parts of the addresses if you wanted).

Should have a new version tomorrow with Ham counts re-instated and checks for RBSBL and DNSBL and loglevel. also seperate spam score averages for below and above the reject threshold.

[JonB]

Brian,

The qpsmtpd log level in RC1 is 6. The bug you were pointed to on the dev list discuss's increasing it back up to 8 on the next release.

This is from a brand new rc1 install. The install cd is still warm  

Code: [Select]

qpsmtpd=service
    Bcc=disabled
    BccUser=maillog
    DNSBL=disabled
    LogLevel=6
    MaxScannerSize=25000000
    RBLList=sbl-xbl.spamhaus.org,whois.rfc-ignorant.org,dnsbl.njabl.org,relays.ordb.org
    RHSBL=disabled
    RequireResolvableFromHost=no
    SBLList=dsn.rfc-ignorant.org
    access=public
    status=enabled


Jon

[brianr]

The qpsmtpd log level in RC1 is 6. The bug you were pointed to on the dev list discuss's increasing it back up to 8 on the next release.

Strange I have an rc1 system here where I am sure I didn't change the loglevel, and it is set to 8, however i have confirmed your point with another install.

However my script now checks the LogLevel and puts out a warning if it is less than 8.

New version in my contribs directory:

http://mirror.contribs.org/smeserver/contribs/bread/

Bugs here:

http://bugs.contribs.org/show_bug.cgi?id=819

[jsheets]

I was trying to download the script to give it a try and it doesn't look like the files are there anymore.  If anybody has another location where I can get the files, I would like to test this out.  Thanks!

[chris burnat]

Jsheets, try this, checked it, its all there.
http://mirror.contribs.org/smeserver/contribs/bread/

[jsheets]

My fault, I was still looking in the old location.  I didn't realize there was a second page to this thread yet.  Doh!  Thanks for the response!

[CharlieBrady]

I was trying to download the script to give it a try and it doesn't look like the files are there anymore.  If anybody has another location where I can get the files, I would like to test this out.

Please download the script via the bug tracker entry, and return any feedback there.

[brianr]

New version on the bug tracker and in my contrib directory:


http://mirror.contribs.org/smeserver/contribs/bread/

Bugs here:

http://bugs.contribs.org/show_bug.cgi?id=819

Based on Charlie's re-write it runs very much faster, and does not use external commands.

It now tracks email delivered through fetchmail, and also local LAN sending as well.

[brianr]

New version upoaded, fixing a bug giving negative counts for the accepted emails.

And I forgot to say that you need to replace the mailstats.cron with the new one from the contribs directory.

[kruhm]

can you put a short howto.txt in the dir. Include:
-last date updated
-problem
-solution
-install procedures for those not familiar

[brianr]

will do, but not for a day or so - work intervenes!!

[brianr]

.. .and have done, plus update with some additions from Pascal SCHIRRMANN  .

[brianr]

and a small update - fixing percentages for RBL etc, and also re-arrangement of total lines.

[byte]

Brian,

Could you tell me what mail is defined as...

Misc.rejected                    :       58 (  5.28%)

The other results are clear, but I was wondering how it got to that result.

Thanks for your & others effort on this neat little script

[brianr]

"misc" covers mainly the situations where qpsmtp finds that the smtp protocol is not followed properly, arithmetically it is all rejected situations which are not covered by the RBL or Executible columns.

As far as I can make out many "built in" Virus and Spam engines often do not "wait" for the correct responses etc, but just try t send email as fast as possible to the receivers.  My own experience on my servers is that this seems to eliminate most Viruses, I only rarely get an "executible" rejection and NEVER get a Virus detected by Clam.

Hope that helps.  Others may have other thoughts on this.