Hi Ralf,
> i've the requirement to store the virus name in the logs in case of an
> virus and to store the banned filename in case of a banned file Logging
> is currently on an mysql server.
Ok. I've got this running on our servers so that I can send nice reports
every week to our users telling them how many mails were caught and why.
> Which two variables contain the needed info (virusname or banned
> filename ?) to insert into the sql statement ?
I've picked up the virus name from the smtp_resp column in the msg_rcpt
table. A sample entry in this column being something like "250 2.7.0 Ok,
discarded, id=27873-03 - VIRUS: Worm.Stration.YY".
In my case I've created a couple of new tables - a 'viruses' table which
stores the virus names uniquely:
CREATE TABLE `viruses` (
`virus_id` int(11) NOT NULL auto_increment,
`virus_name` char(255) NOT NULL,
PRIMARY KEY (`virus_id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;
Together with a msg_viruses table that links infected messages to a
particular virus:
CREATE TABLE `msg_viruses` (
`mail_id` char(13) NOT NULL,
`virus_id` int(11) NOT NULL,
KEY `mail_id` (`mail_id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
These tables mean that you are effectively storing the details on which
messages are infected twice, though do remove the need for the massive
cross table join queries which are really slow when you get near 1,000,000
messages in the msgs table. They also mean that you only get the details
recorded once for emails where there was a CC or BCC. I have just noticed
that they could really do with some indexes being added though having been
running these boxes for 2 years there is only 900 rows in the viruses
table.
I then have a php script on a cron job which runs hourly. It's quite long
so I'll provide just the essentials:
// Find all infected messages
$sql="
SELECT msgs.mail_id, msgrcpt.smtp_resp
FROM msgs
LEFT JOIN msgrcpt
ON msgs.mail_id = msgrcpt.mail_id
LEFT JOIN msg_viruses
ON msgs.mail_id = msg_viruses.mail_id
WHERE msgs.content='V'
AND msg_viruses.virus_id IS NULL";
Each result in this resultset is then run through a function which cleans
up the smtp_resp column and extracts the virus name:
// Go through all virus infected messages
while($row=mysql_fetch_array($link))
{
// Find the VIRUS: section of the SMTP response field (looks like
it's usually 39)s
s=strpos($row['smtp_resp'], "VIRUS");
$pos=strpos($row['smtp_resp'], "VIRUS");
// Find the names of viruses
$virus=substr($row['smtp_resp'], $pos+7,
strlen($row['smtp_resp']));
// They may be more than 1 virus separated by a comma
$viruses = explode(", ", $virus);
// Go through each virus
foreach($viruses as $vir)
{
// Try and get the id for this virus
$id=getVirusId($vir);
printf("Got ID %s for %s<br/>\n", $id, $vir);
}
// Set this message to have this virus
$mi=addslashes($row['mail_id']);
$sql="INSERT INTO `msg_viruses` (`mail_id`, `virus_id`)
VALUES('$mi', '$id')";
$llink=runSql($sql, $db_kfs);
}
// Name that virus!
function getVirusId($name)
{
global $db_kfs;
$n=addslashes($name);
$sql="SELECT virus_id FROM viruses WHERE virus_name='$n' LIMIT 1";
$link=runSql($sql, $db_kfs);
if(mysql_num_rows($link)==1)
{
return mysql_result($link,0,0);
} else {
$sql="INSERT INTO `viruses` (`virus_id`,`virus_name`)
VALUES ('', '$n')";
$link=mysql_query($sql, $db_kfs);
return mysql_insert_id();
}
}
And that's it really. If you'd like me to send over the whole script
please contact me offlist - the same goes for anyone else. It shouldn't be
too difficult to port into Perl if you're that way inclined.
> I've read the archive and understood the potential problems with
> more-than-one virus in a mail, the info about the first virus or the
> first banned attachment would suffice our needs.
>
If you look through the code above you'll see that I've taken account of
this. Any comma separated list of viruses results in n rows being inserted
into msg_viruses to represent this, you could always drop this code and
you'll end up with just the first virus.
Regards
Richard
-------------------------------------------------------------------------
This SF.Net email is sponsored by the Moblin Your Move Developer's challenge
Build the coolest Linux based applications with Moblin SDK & win great prizes
Grand prize is a trip for two to an Open Source event anywhere in the world
http://moblin-contest.org/redirect.php?banner_id=100&url=/
_______________________________________________
AMaViS-user mailing list
[email protected]
https://lists.sourceforge.net/lists/listinfo/amavis-user
AMaViS-FAQ:http://www.amavis.org/amavis-faq.php3
AMaViS-HowTos:http://www.amavis.org/howto/
