Mailwatch "Today's totals" report is incorrect [includes fix]

[pdwalker]

Now that I am happily blocking messages with crappy attachments in zip files, I noticed the statistics were not showing the blocked messages and that the messages didn't add up.

this doesn't add up

Screen Shot 2016-05-05 at 10.45.png (15.96 KiB) Viewed 3019 times

In fact, the total is short by the number of blocked messages. That's mighty suspicious.

Short answer: the monster query used to generate this table is incorrect. The blocked messages are not counted if they are also marked as spam - which messages that get blocked tend to be. The change needed to the query is:

Code: Select all

[root@efa mailscanner]# diff -c functions.php.org functions.php
*** functions.php.org    2016-05-05 10:48:31.136301817 +0800
--- functions.php    2016-05-05 10:49:29.659234606 +0800
***************
*** 431,438 ****
      nameinfected>0
      AND (virusinfected=0 OR virusinfected IS NULL)
      AND (otherinfected=0 OR otherinfected IS NULL)
!     AND (isspam=0 OR isspam IS NULL)
!     AND (ishighspam=0 OR ishighspam IS NULL)
     THEN 1 ELSE 0 END
    ) AS blockedfiles,
    ROUND((
--- 431,438 ----
      nameinfected>0
      AND (virusinfected=0 OR virusinfected IS NULL)
      AND (otherinfected=0 OR otherinfected IS NULL)
!     -- AND (isspam=0 OR isspam IS NULL)
!     -- AND (ishighspam=0 OR ishighspam IS NULL)
     THEN 1 ELSE 0 END
    ) AS blockedfiles,
    ROUND((
***************
*** 441,448 ****
       nameinfected>0
       AND (virusinfected=0 OR virusinfected IS NULL)
       AND (otherinfected=0 OR otherinfected IS NULL)
!      AND (isspam=0 OR isspam IS NULL)
!      AND (ishighspam=0 OR ishighspam IS NULL)
      THEN 1 ELSE 0 END
     )/COUNT(*))*100,1
    ) AS blockedfilespercent,
--- 441,448 ----
       nameinfected>0
       AND (virusinfected=0 OR virusinfected IS NULL)
       AND (otherinfected=0 OR otherinfected IS NULL)
!      -- AND (isspam=0 OR isspam IS NULL)
!      -- AND (ishighspam=0 OR ishighspam IS NULL)
      THEN 1 ELSE 0 END
     )/COUNT(*))*100,1
    ) AS blockedfilespercent,

fixed

Screen Shot 2016-05-05 at 10.49.png (16.44 KiB) Viewed 3018 times

I've submitted a bug report to the Mailwatch project.