[Speakers] Re: Centralized Logging

[Adam Tauno Williams]

> > > > [BTW, the message *ALWAYS* goes through just fine,  but users are
> > > > frequently convinced that the system 'lost it'.
> > > Yup, amazing how that works.
> > > > My favorite was the
> > > > furious arm waving user who had set their SIEVE filter to delete ALL
> > > > incoming mail.
> > > Wow ...  :-)
> > It was quite shocking at the time;  and the torrent of profanity when I
> > showed him what he had done....  but it was *STILL* the systems fault,
> > it should stop him from making mistakes..... still waiting for an
> > upgrade that does that.
> YOU should program that update yourself!  :-)

Not even .NET has sufficient reflection to pull that one off.

> Or better yet, a faster and easier fix is "no more sieve for you"!

That is one solution,  I can also think of a few others.

BTW,  here is a quick SQL I cooked up to report daily mail statistics
for the last thirty days.  Making a page to display the output would be
pretty trivial (but not my job) -

SELECT DISTINCT l0.date, 
  (SELECT COUNT(*) 
   FROM logs 
   WHERE host = 'FIREWALL' AND facility = 'mail' AND priority = 'info'
AND program = 'sendmail' AND msg LIKE '%Milter: data, reject=554%'
    AND date = l0.date
  ) AS rejected_noattch,
  (SELECT COUNT(*) 
   FROM logs 
   WHERE host = 'FIREWALL' AND facility = 'mail' AND priority = 'notice'
AND program = 'sendmail' AND msg LIKE '%reject=553 5.3.0 Spam blocked%'
    AND date = l0.date
  ) AS rejected_spamcop,
  (SELECT COUNT(*) 
   FROM logs 
   WHERE host = 'FIREWALL' AND facility = 'mail' AND program =
'milter-greylist' AND msg LIKE '%exception%'
    AND date = l0.date
  ) AS greylist_exepmtion,
  (SELECT COUNT(*) 
   FROM logs 
   WHERE host = 'FIREWALL' AND facility = 'mail' AND program =
'milter-greylist' AND msg LIKE '%delayed for%'
    AND date = l0.date
  ) AS greylist_delayed,
  (SELECT COUNT(*) 
   FROM logs 
   WHERE host = 'FIREWALL' AND facility = 'mail' AND program =
'milter-greylist' AND msg LIKE '%autowhitelisted%'
    AND date = l0.date
  ) AS greylist_whitelisted,
  (SELECT COUNT(*) 
   FROM logs 
   WHERE host = 'FIREWALL' AND facility = 'mail' AND program =
'milter-greylist' AND msg LIKE '%expired%'
    AND date = l0.date
  ) AS greylist_expirations,
  (SELECT COUNT(*) 
   FROM logs 
   WHERE host = 'FIREWALL' AND facility = 'mail' AND program =
'sendmail' AND msg LIKE '%relay=sardine.%'
    AND date = l0.date
  ) AS firewall_accepted,
  (SELECT COUNT(*) 
   FROM logs 
   WHERE host = 'SARDINE' AND facility = 'mail'  AND program =
'clamav-milter' AND msg LIKE '%Redirected virus to%'
    AND date = l0.date
  ) AS internal_quarantined,
  (SELECT COUNT(*) 
   FROM logs 
   WHERE host = 'SARDINE' AND facility = 'mail' AND priority = 'info'
AND msg LIKE '%DSN: User unknown'
    AND date = l0.date
  ) AS internal_unknown_user,
  (SELECT COUNT(*) 
   FROM logs 
   WHERE host = 'SARDINE' AND facility = 'mail' AND priority = 'info'
AND msg LIKE '%mailer=cyrusv2%stat=Sent' 
    AND date = l0.date
  ) AS internal_delivered
FROM logs l0
WHERE date > current_date - interval '30 days'
GROUP BY date
-------------- next part --------------
A non-text attachment was scrubbed...
Name: not available
Type: application/pgp-signature
Size: 189 bytes
Desc: This is a digitally signed message part
Url : http://www.kalamazoolinux.org/pipermail/speakers/attachments/20060214/e2a82ea0/attachment.bin