MailWatch: blocked files fix

[Steve Freegard]

Ryan,

Here is the updated SQL statement:

 SELECT
  COUNT(*) AS processed,
  SUM(CASE WHEN virusinfected>0 THEN 1 ELSE 0 END) AS virii,
  ROUND((SUM(CASE WHEN virusinfected>0 THEN 1 ELSE 0 END)/COUNT(*))*100,1)
AS viriipercent,
  SUM(CASE WHEN nameinfected>0 THEN 1 ELSE 0 END) AS blockedfiles,
  ROUND((SUM(CASE WHEN nameinfected>0 THEN 1 ELSE 0 END)/COUNT(*))*100,1) AS
blockedfilespercent,
  SUM(CASE WHEN otherinfected>0 THEN 1 ELSE 0 END) AS otherinfected,
  ROUND((SUM(CASE WHEN otherinfected>0 THEN 1 ELSE 0 END)/COUNT(*))*100,1)
AS otherinfectedpercent,
  SUM(CASE WHEN isspam>0 THEN 1 ELSE 0 END) AS spam,
  ROUND((SUM(CASE WHEN isspam>0 THEN 1 ELSE 0 END)/COUNT(*))*100,1) AS
spampercent,
  SUM(CASE WHEN ishighspam>0 THEN 1 ELSE 0 END) AS highspam,
  ROUND((SUM(CASE WHEN ishighspam>0 THEN 1 ELSE 0 END)/COUNT(*))*100,1) AS
highspampercent,
  SUM(size) AS size
 FROM
  maillog
 WHERE
  DATE_FORMAT(timestamp, '%Y-%m-%d') = CURRENT_DATE()

Kind regards,
Steve.

-----Original Message-----
From: Bingham, Ryan
To: MAILSCANNER at JISCMAIL.AC.UK
Sent: 01/09/03 20:43
Subject: MailWatch: blocked files fix

Someone recently posted a patch to CustomConfig.pm to get MailWatch to
display the statistics on blocked files properly.  I accidentally
deleted the message and can't find it in the archives.

Could someone repost it?  Thanks!

Ryan

--
This email and any files transmitted with it are confidential and
intended solely for the use of the individual or entity to whom they
are addressed. If you have received this email in error please notify
the sender and delete the message from your mailbox.

This footnote also confirms that this email message has been swept by
MailScanner (www.mailscanner.info) for the presence of computer viruses.