EximAmalgamatedLog
From rsyslog wiki
Contents |
Amalgamated mail log
(If you are interested in this then please post on "discussion" for this page).
This configuration demonstrates some extremely powerful features of Rsyslog. Whilst the documentation is pretty complete there are quite a few things that are not immediately apparent that you can do. This is the result of some serious experimentation 8)
My goal is to provide useful consolidated logging from an anti-spam and virus mail proxy system. Exim with SA-Exim, Spam Assassin and ClamAV provides a pretty good combination of features to do the job. However, by default the logs relating to a particular email are all over the place.
By using Rsyslog I am able to get a single log line per email pulling in information from all the component parts. This is a work in progress and probably needs some optimization. I am not exactly an expert in regexs or SQL - but it works!
I hate seeing partial examples so I do present a bit more detail than you will actually need. I've kept the config files in their entirety for easy copy 'n' paste. However, you do get to reverse engineer the configuration of the applications being logged - ie Exim et al.
Incidentally, if you are not familiar with Exim, then I suggest you evaluate it. Although I don't know of any distro that specifies it as a default, it is easily the most powerfull MTA I have ever found by an order of magnitude. It can be a little complicated to set up but it also has the most complete documentation of any non trivial open source project that I know of.
Ingredients
You'll need a MySQL database server, Exim, ClamAV, Spam Assassin and SA-Exim. I'd also recommend Apache with PHP and phpMyAdmin as well. You'll also need a way to view the log files eg phpLogCon or PHPReportGenerator (non free). I personally use Gentoo so installing the software is a doddle (if a little time consuming). Make sure all components send their logs to rsyslog!
/etc/rsyslog.conf
This is my standard config. Create a user and group called rsyslog. Each component on the system should have a config in /etc/rsyslog.d/. If not their logs end up in the standardish files. Anything else ends up in uncategorized.log for evaluation.
# /etc/rsyslog.conf # RSyslog system logger configuration # Docs: http://www.rsyslog.com/doc-rsyslog_conf.html # "/etc/init.d/rsyslog reload" to reread the config # Jon Gerdes (http://www.blueloop.net) 23 Aug 2008 # # - Extra spaces for legibility in params causes errors, OK in # log output lines # Modules -------------------------------------------------------------------- # Input $ModLoad immark.so # Add Mark messages $ModLoad imuxsock.so # Unix sockets $ModLoad imklog.so # Kernel logger $ModLoad imudp.so # UDP input #$ModLoad imrelp.so # RELP input #$ModLoad imtcp.so # TCP input #$ModLoad file.so # Text file input #$ModLoad imgssapi.so # Plain TCP and GSSAPI #$ModLoad im1395.so # Messages via RFC1395 # Output #$ModLoad omsnmp.so # Send SNMP traps $ModLoad ommysql.so # Log to MySQL #$ModLoad ompgsql.so # Log to PostgreSQL #$ModLoad ommail.so # Send mail #$ModLoad omrelp.so # Send to another host via RELP #$ModLoad omlibdbi.so # Log via generic DB output #$ModLoad omgss.so # GSS enabled output # Globals -------------------------------------------------------------------- # There are many more - see docs # Files and dirs are created as needed (dirs only for "dynamic" files) $umask 0000 $DirCreateMode 0640 $FileCreateMode 0640 $FileOwner rsyslog $FileGroup rsyslog $DirOwner rsyslog $DirGroup rsyslog $RepeatedMsgReduction on # Include package specific logs (including rsyslog itself) $IncludeConfig /etc/rsyslog.d/*.conf # syslog style - everything else that falls through from above # will be logged in "traditional syslog style" # The "-" means don't sync after each write # & ~ means drop messages that the last filter found (avoid duplication) # Log to the console kern.* /dev/console & /var/log/kernel & ~ *.info;mail.none;authpriv.none;cron.none -/var/log/messages & ~ authpriv.* /var/log/secure & ~ mail.* -/var/log/maillog & ~ cron.* -/var/log/cron & ~ # Display using wall to all logged in users *.emerg * & ~ uucp,news.crit -/var/log/spooler & ~ local7.* /var/log/boot.log & ~ # Finally log everything else *.* /var/log/uncategorized.log
In the above configuration there is an include statement that will add all snippets in /etc/rsyslog.d/*.conf into the full configuration. I number my snippets so that they get included in order. I have not read the code to ensure this is actually the case but it is a fair assumption.
/etc/rsyslog.d/20-mail.conf
This is the important bit. This is a work in progress but I will only show here a (probably) working example. Note that I am really not an expert in optimizing things like regular expressions.
Before you dive in, I'd like to explain a few things. I want a single line in my database for each email that details what has happened during its lifetime within Exim. The mail is received, spam and virus scanned and then spat out to the next hop. By default (ie without rsyslog) you will get something like these files full of entries:
/var/log/exim/(mainlog|rejectlog|paniclog) /var/log/clamav/clamav.log /var/log/spamd/spamd.log
The Exim logs alone are pretty impressive with at least three lines per email: a <= entry for received, a => for out and a "Completed" line for the finish. Then there is the SA-Exim stuff as well. As far as I am concerned, Exim has the most fantastic logging but it's not what you wave at an Exchange admin for example (they are a bit simple - bless!)
Now if we send the logs to a DB it turns out that we can use not just an INSERT but also an UPDATE statement. Not only that but (at least with MySQL) we can use the full language. Check out this line:
WHERE MessageID = REVERSE(SUBSTRING(REVERSE(SUBSTRING('%msg:R,ERE,0,ZERO:mid=<.*@[A-Za-z0-9.-]+\.[A-Za-z]{2,4}>--end%' FROM 6)) FROM 2))
Here, I am using several functions defined in MySQL (not rsyslog) to tidy up the data that is logged. REVERSE and SUBSTRING are MySQL functions - smashing. So you have access to the full power of MySQL data manipulation.
Allow me to explain this particular entry:
The purpose is to retrieve the Message ID from a Spam Assassin (spamd) log line. With my effort at a regex I am able to get the Message ID as:
mid=<random stuff blah blah@domain>
Message IDs end with an email address format hence the @[A-Za-z0-9.-]+\.[A-Za-z]{2,4}. Now these are logged starting "mid=<" , so the ID itself starts on character 6 (FROM 6). Next I reverse the order of the characters, then I take all but the first character (FROM 2) and then reverse again to put the text back in it's original order. All that just to strip the trailing ">" (anyone got a better idea? - I'd love to hear it - I really am not asn expert on this stuff) So by using MySQL functions I am able to take the output from built in rsyslog functionality, manipulate it and then put that in the database.
In the example presented here I am logging to files as well. Once it is all working, you'll probably want to drop that. I also run the same query several times - this is to make it easier to maintain (see last sentence!) Also note that I am logging to two database tables. A copy of the text logs as well as the amalgamated mail log.
################################################################
# Mail system logging
# Exim, Spam Assassin, SA-Exim, ClamAV
# /etc/rsyslog.d/20-mail.conf
# JG 10 Jun 2009
# JG 22 Sep 2009 - Deal with P=local (ie locally generated mail)
# JG 22 Sep 2009 - Better handling of SA-Exim to give to, from etc
# Fixed no DateIn for temp rejects
# Separate out spamd - leave the SpamDResult field
# in case a method becomes available to use it
# Better HostFrom regex
# Separate out ClamAV by using Exim to log result
# in the ACL
# JG 23 Sep 2009 - Remove separate sa-exim and ioc logs
# Added DB logging for DNSBL rejections
################################################################
# NOTES
# Careful with quotes in if clauses
# seems to need ' and not " (JG 11 Jun 2009)
# Multi line logging from Exim "detector":
# :msg, regex, " \[[0-9]{1,3}[\\/][0-9]{1,3}\]" ~
# email address finder:
# %msg:R,ERE,0,ZERO:[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Za-z]{2,4}--end%
# Exim ID finder:
# %msg:R,ERE,0,ZERO:[A-Za-z0-9]{6}-[A-Za-z0-9]{6}-[A-Za-z0-9]{2}--end%
# Easier to read log format:
# $template Mail-Exim-File-Format,"%timegenerated:1:10:date-rfc3339% %timegenerated:12:19:date-rfc3339% %hostname% %syslogtag%%msg%\n"
#########################################################
# Syslog style to support OSSEC (JG 26 AUg 2009)
$template Mail-Exim-File-Format,"%timegenerated% %HOSTNAME% %syslogtag%%msg%\n"
#########################################################
# Amalgamated logging templates
# The log entry is built up an initial entry from ClamAV followed by successive updates from the vaious components, in the order
# of the templates here. The EximID is used to look up the entry except for SA-Exim (which uses the msgid).
# DB fields:
# EximID, MessageID, DateIn, DateLastProcessed, DateCompleted, FromAddr, FirstToAddr,
# AdditionalToAddr, HostFrom, FirstHostTo, Size, Subject, AVScanResult, SAAction, SpamdResult, Notes
# AV Scanner
# with virus
$template Mail-Exim-AV-Virus-Amalgamated,"INSERT INTO AmalgamatedMailLog \
(EximID, DateIn, DateLastProcessed, AVSCanResult) \
VALUES (\
'%msg:2:17%', \
'%timereported:::date-mysql%', \
'%timereported:::date-mysql%', \
'%msg:R,ERE,0,ZERO:This message contains a virus.*--end%' \
) \
",SQL
# without virus
$template Mail-Exim-AV-Clean-Amalgamated,"INSERT INTO AmalgamatedMailLog \
(EximID, DateIn, DateLastProcessed, AVSCanResult) \
VALUES (\
'%msg:2:17%', \
'%timereported:::date-mysql%', \
'%timereported:::date-mysql%', \
SUBSTRING('%msg:R,ERE,0,ZERO:BLL014.*--end%' FROM 8) \
) \
",SQL
# <= - In
# Local:
# Sep 15 09:06:17 loghost exim[20787]: 1MnT3J-0005PH-2y <= nagios@example.com U=nagios P=local S=794 T="** PROBLEM Service Alert: host-name/NTP-peer is CRITICAL **"
# Sep 22 10:40:59 portal exim[12557]: 1Mq1rn-0003GX-MZ <= root@blueloop.net U=root P=local S=516 T="test message"
# Relayed:
# Sep 15 09:03:38 loghost exim[20078]:
# 1MnT0g-0005Dq-BC <= user@example.com H=host.example.com [192.168.100.100] P=esmtp S=8690192 id=4AAF585B020000AA0004ED5B@port.blueloop.net T="Subject line from message"
# If an arg to CONCAT is NULL then the whole output is NULL
$template Mail-Exim-In-Amalgamated,"UPDATE AmalgamatedMailLog \
SET \
DateIn = '%timereported:::date-mysql%', \
DateLastProcessed = '%timereported:::date-mysql%', \
FromAddr = '%msg:R,ERE,0,ZERO:[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Za-z]{2,4}--end%', \
FirstToAddr = 'pending', \
AdditionalToAddr = ' ', \
HostFrom = SUBSTRING('%msg:R,ERE,0,ZERO:H=.*\[[0-9]{1,3}\.[0-9]{1,3}\.[0-9]{1,3}\.[0-9]{1,3}]--end%' FROM 3), \
Size = SUBSTRING('%msg:R,ERE,0,ZERO:S=[0-9]{1,}--end%' FROM 3), \
Subject = SUBSTRING('%msg:R,ERE,0,ZERO:T=.*--end%' FROM 3), \
MessageID = SUBSTRING('%msg:R,ERE,0,ZERO:id=[^T=]*[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Za-z]{2,4}--end%' FROM 4), \
FirstToAddr = 'pending', \
FirstHostTo = 'pending' \
WHERE EximID = '%msg:R,ERE,0,ZERO:[A-Za-z0-9]{6}-[A-Za-z0-9]{6}-[A-Za-z0-9]{2}--end%' \
",SQL
# == - Deferred
$template Mail-Exim-Def-Amalgamated,"UPDATE AmalgamatedMailLog \
SET \
DateLastProcessed = '%timereported:::date-mysql%', \
FirstToAddr = 'Deferred - see notes', \
FirstHostTo = 'Deferred - see notes', \
Notes = '%msg%' \
WHERE EximID = '%msg:R,ERE,0,ZERO:[A-Za-z0-9]{6}-[A-Za-z0-9]{6}-[A-Za-z0-9]{2}--end%' \
",SQL
# ** - Failed
$template Mail-Exim-Fail-Amalgamated,"UPDATE AmalgamatedMailLog \
SET \
DateLastProcessed = '%timereported:::date-mysql%', \
FirstToAddr = 'Failed - see notes', \
FirstHostTo = 'Failed - see notes', \
Notes = '%msg%' \
WHERE EximID = '%msg:R,ERE,0,ZERO:[A-Za-z0-9]{6}-[A-Za-z0-9]{6}-[A-Za-z0-9]{2}--end%' \
",SQL
# => - Out
$template Mail-Exim-Out-Amalgamated, "UPDATE AmalgamatedMailLog \
SET \
FirstToAddr = '%msg:R,ERE,0,ZERO:[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Za-z]{2,4}--end%', \
FirstHostTo = SUBSTRING('%msg:R,ERE,0,ZERO:H=.*]--end%' FROM 3), \
DateLastProcessed = '%timereported:::date-mysql%', \
Notes = 'Out' \
WHERE EximID = '%msg:R,ERE,0,ZERO:[A-Za-z0-9]{6}-[A-Za-z0-9]{6}-[A-Za-z0-9]{2}--end%' \
",SQL
# -> - additional deliveries
$template Mail-Exim-Add-Amalgamated, "UPDATE AmalgamatedMailLog \
SET \
AdditionalToAddr = CONCAT_WS(' ',AdditionalToAddr,'%msg:R,ERE,0,ZERO:[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Za-z]{2,4}--end%'), \
DateLastProcessed = '%timereported:::date-mysql%', \
Notes = 'Additional delivery' \
WHERE EximID = '%msg:R,ERE,0,ZERO:[A-Za-z0-9]{6}-[A-Za-z0-9]{6}-[A-Za-z0-9]{2}--end%' \
",SQL
# Completed
$template Mail-Exim-Completed-Amalgamated,"UPDATE AmalgamatedMailLog \
SET \
DateCompleted = '%timereported:::date-mysql%', \
DateLastProcessed = '%timereported:::date-mysql%', \
Notes = 'Completed' \
WHERE EximID = '%msg:R,ERE,0,ZERO:[A-Za-z0-9]{6}-[A-Za-z0-9]{6}-[A-Za-z0-9]{2}--end%' \
",SQL
# SA-Exim - clean
$template Mail-SA-Exim-Clean-Amalgamated,"UPDATE AmalgamatedMailLog \
SET \
SAAction = SUBSTRING('%msg:R,ERE,0,ZERO:SA: Action:.*(required=[0-9]{1,3}\.[0-9]|expanded to false)--end%' FROM 13) \
WHERE EximID = '%msg:R,ERE,0,ZERO:[A-Za-z0-9]{6}-[A-Za-z0-9]{6}-[A-Za-z0-9]{2}--end%' \
",SQL
# SA-Exim - rejected
# no <= or => for these so need to fill in details from the SA log line
$template Mail-SA-Exim-Rejected-Amalgamated,"UPDATE AmalgamatedMailLog \
SET \
HostFrom = SUBSTRING('%msg:R,ERE,0,ZERO:\(host=.*\[.*\]--end%' FROM 7), \
FromAddr = SUBSTRING('%msg:R,ERE,0,ZERO:From <[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Za-z]{2,4}--end%' FROM 7), \
FirstToAddr = SUBSTRING('%msg:R,ERE,0,ZERO:]) for [A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Za-z]{2,4}--end%' FROM 8), \
SAAction = SUBSTRING('%msg:R,ERE,0,ZERO:SA: Action:.*trigger=[0-9]{1,3}\.[0-9]--end%' FROM 13) \
WHERE EximID = '%msg:R,ERE,0,ZERO:[A-Za-z0-9]{6}-[A-Za-z0-9]{6}-[A-Za-z0-9]{2}--end%' \
",SQL
# SA-Exim - flagged as Spam
$template Mail-SA-Exim-FlagSpam-Amalgamated,"UPDATE AmalgamatedMailLog \
SET \
SAAction = SUBSTRING('%msg:R,ERE,0,ZERO:SA: Action:.*required=[0-9]{1,3}\.[0-9]--end%' FROM 13) \
WHERE EximID = '%msg:R,ERE,0,ZERO:[A-Za-z0-9]{6}-[A-Za-z0-9]{6}-[A-Za-z0-9]{2}--end%' \
",SQL
#########################################################
# Notes on the DNSBL and connect ACL handling
# Firewalling:
# Update a DB of IPs failing DNSBL lookups
# Update the DB if connect ACL is passed
# Every t:
# if count_of_lookups > n and last_updated > (now() - t*2) then
# add ip to firewall)
# 0 = FALSE
# 1 = TRUE (actually !0 = true)
# DNSBL
# DB - Syslog/MAIL_DNSBL:
# IP, HostName, DNSBL, Count, DateLastUpdated, DateFirstEntered
# This template adds a new entry for a unique IP or updates an existing one
$template Mail-DNSBL,"INSERT INTO Mail_DNSBL \
(IP, Block, HostName, DNSBL, DateFirstEntered, DateLastUpdated) \
VALUES (\
SUBSTRING('%msg:R,ERE,0,ZERO:[ ][0-9]{1,3}\.[0-9]{1,3}\.[0-9]{1,3}\.[0-9]{1,3}--end%' FROM 2), \
'1', \
SUBSTRING('%msg:R,ERE,0,ZERO:H=.*\[[0-9]{1,3}\.[0-9]{1,3}\.[0-9]{1,3}\.[0-9]{1,3}]--end%' FROM 3), \
SUBSTRING('%msg:R,ERE,0,ZERO:listed by.*--end%' FROM 11), \
'%timereported:::date-mysql%', \
'%timereported:::date-mysql%' \
) \
ON DUPLICATE KEY UPDATE Block = '1', \
DNSBL = SUBSTRING('%msg:R,ERE,0,ZERO:listed by.*--end%' FROM 11), \
Count = Count + 1, \
DateLastUpdated = '%timereported:::date-mysql%' \
",SQL
$template Mail-DNSBL-Clean,"INSERT INTO Mail_DNSBL \
(IP, Block, HostName, DNSBL, DateFirstEntered, DateLastUpdated) \
VALUES (\
'%msg:R,ERE,0,ZERO:[0-9]{1,3}\.[0-9]{1,3}\.[0-9]{1,3}\.[0-9]{1,3}--end%', \
'0', \
SUBSTRING('%msg:R,ERE,0,ZERO:H=.*--end%' FROM 3), \
'Passed ACL helo', \
'%timereported:::date-mysql%', \
'%timereported:::date-mysql%' \
) \
ON DUPLICATE KEY UPDATE Block = '0', \
Count = Count + 1, \
DNSBL = 'Passed ACL helo', \
DateLastUpdated = '%timereported:::date-mysql%' \
",SQL
# This template clears out an entry if it gets through the connect ACl, where DNSBLs are checked
# thus avoiding an entry perpetually living in the DB
#------ End of templates
# Connect rejections due to DNSBLs (DB only)
if $programname == 'exim' \
and $msg contains 'ACL: BLL002' \
then :ommysql:localhost,Syslog,rsyslog,rsysl0g;Mail-DNSBL
& ~
if $programname == 'exim' \
and $msg contains 'BLL015' \
then :ommysql:localhost,Syslog,rsyslog,rsysl0g;Mail-DNSBL-Clean
& ~
#### Drop these for now
if $programname == 'exim' \
and ( \
$msg contains 'BLL016' \
or $msg contains 'BLL017' \
or $msg contains 'no host name found for IP address' \
) \
then ~
# Attachments logfile
if $programname == 'exim' \
and ( \
$msg contains 'BLL018' \
) \
then /var/log/mail/attachments.log;Mail-Exim-File-Format
& ~
# Full Exim log (bar the bits that are filtered out above) - file
if $programname == 'exim' then /var/log/mail/mail.log;Mail-Exim-File-Format
###################################
# Amalgamated Mail log - single line per mail, some details lost - DB
if $programname == 'exim' \
and $msg contains 'BLL006' \
then :ommysql:localhost,Syslog,rsyslog,rsysl0g;Mail-Exim-AV-Virus-Amalgamated
if $programname == 'exim' \
and $msg contains 'BLL014' \
then :ommysql:localhost,Syslog,rsyslog,rsysl0g;Mail-Exim-AV-Clean-Amalgamated
if $programname == 'exim' \
and $msg contains '<=' \
then :ommysql:localhost,Syslog,rsyslog,rsysl0g;Mail-Exim-In-Amalgamated
if $programname == 'exim' \
and $msg contains '=>' \
then :ommysql:localhost,Syslog,rsyslog,rsysl0g;Mail-Exim-Out-Amalgamated
if $programname == 'exim' \
and $msg contains '->' \
then :ommysql:localhost,Syslog,rsyslog,rsysl0g;Mail-Exim-Add-Amalgamated
if $programname == 'exim' \
and $msg contains '==' \
then :ommysql:localhost,Syslog,rsyslog,rsysl0g;Mail-Exim-Def-Amalgamated
if $programname == 'exim' \
and $msg contains '**' \
then :ommysql:localhost,Syslog,rsyslog,rsysl0g;Mail-Exim-Fail-Amalgamated
if $programname == 'exim' \
and $msg contains 'Completed' \
then :ommysql:localhost,Syslog,rsyslog,rsysl0g;Mail-Exim-Completed-Amalgamated
if $programname == 'exim' \
and $msg contains 'SA: Action:' \
and ($msg contains 'expanded to false' \
or $msg contains 'canned but message isn') \
then :ommysql:localhost,Syslog,rsyslog,rsysl0g;Mail-SA-Exim-Clean-Amalgamated
if $programname == 'exim' \
and $msg contains 'SA: Action:' \
and $msg contains 'rejected message' \
then :ommysql:localhost,Syslog,rsyslog,rsysl0g;Mail-SA-Exim-Rejected-Amalgamated
if $programname == 'exim' \
and $msg contains 'SA: Action:' \
and $msg contains 'flagged as Spam but accepted' \
then :ommysql:localhost,Syslog,rsyslog,rsysl0g;Mail-SA-Exim-FlagSpam-Amalgamated
##################################
# Dump Exim messages
if $programname == 'exim' then ~
This is the MySQL table structure. If you are using my config verbatim, then import the following schema. Also create a user called "rsyslog" with a password of "rsysl0g" and grant right to insert data to this database.
-- phpMyAdmin SQL Dump -- version 2.11.9.4 -- http://www.phpmyadmin.net -- -- Host: localhost -- Generation Time: Dec 24, 2009 at 11:01 AM -- Server version: 5.0.70 -- PHP Version: 5.2.9-pl2-gentoo SET SQL_MODE="NO_AUTO_VALUE_ON_ZERO"; -- -- Database: `Syslog` -- CREATE DATABASE `Syslog` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci; USE `Syslog`; -- -------------------------------------------------------- -- -- Table structure for table `AmalgamatedMailLog` -- CREATE TABLE IF NOT EXISTS `AmalgamatedMailLog` ( `EximID` varchar(16) NOT NULL, `MessageID` varchar(255) default NULL, `DateIn` datetime default NULL, `DateLastProcessed` datetime default NULL, `DateCompleted` datetime default NULL, `FromAddr` varchar(100) default NULL, `FirstToAddr` varchar(100) default NULL, `AdditionalToAddr` varchar(255) default NULL, `HostFrom` varchar(100) default NULL, `FirstHostTo` varchar(100) default NULL, `Size` int(11) default NULL, `Subject` varchar(255) default NULL, `AVScanResult` varchar(255) default NULL, `SAAction` varchar(255) default NULL, `SpamdResult` varchar(255) default NULL, `Notes` varchar(255) default NULL, UNIQUE KEY `EximID` (`EximID`), KEY `MessageID` (`MessageID`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8 COMMENT='Amalgamated Mail Log'; -- -------------------------------------------------------- -- -- Table structure for table `Mail_DNSBL` -- CREATE TABLE IF NOT EXISTS `Mail_DNSBL` ( `IP` varchar(15) NOT NULL, `Block` tinyint(1) NOT NULL default '0', `HostName` varchar(100) default NULL, `DNSBL` varchar(100) default NULL, `Count` int(7) NOT NULL default '1', `DateLastUpdated` datetime default NULL, `DateFirstEntered` datetime default NULL, PRIMARY KEY (`IP`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8; -- -------------------------------------------------------- -- -- Stand-in structure for view `vMaillog` -- CREATE TABLE IF NOT EXISTS `vMaillog` ( `EximID` varchar(16) ,`MessageID` varchar(255) ,`DateIn` datetime ,`DateLastProcessed` datetime ,`DateCompleted` datetime ,`FromAddr` varchar(100) ,`FirstToAddr` varchar(100) ,`HostFrom` varchar(100) ,`FirstHostTo` varchar(100) ,`Size` int(11) ,`Subject` varchar(255) ,`AVScanResult` varchar(255) ,`SAAction` varchar(255) ,`SpamdResult` varchar(255) ,`FromDomain` varchar(100) ,`ToDomain` varchar(100) ); -- -------------------------------------------------------- -- -- Stand-in structure for view `vTop20SendersByCount` -- CREATE TABLE IF NOT EXISTS `vTop20SendersByCount` ( `FromAddress` varchar(100) ,`MessageCount` bigint(21) ); -- -------------------------------------------------------- -- -- Stand-in structure for view `vTop20SendingDomainsByCount` -- CREATE TABLE IF NOT EXISTS `vTop20SendingDomainsByCount` ( `FromDomain` varchar(100) ,`MessageCount` bigint(21) ); -- -------------------------------------------------------- -- -- Structure for view `vMaillog` -- DROP TABLE IF EXISTS `vMaillog`; CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `Syslog`.`vMaillog` AS select `Syslog`.`AmalgamatedMailLog`.`EximID` AS `EximID`,`Syslog`.`AmalgamatedMailLog`.`MessageID` AS `MessageID`,`Syslog`.`AmalgamatedMailLog`.`DateIn` AS `DateIn`,`Syslog`.`AmalgamatedMailLog`.`DateLastProcessed` AS `DateLastProcessed`,`Syslog`.`AmalgamatedMailLog`.`DateCompleted` AS `DateCompleted`,`Syslog`.`AmalgamatedMailLog`.`FromAddr` AS `FromAddr`,`Syslog`.`AmalgamatedMailLog`.`FirstToAddr` AS `FirstToAddr`,`Syslog`.`AmalgamatedMailLog`.`HostFrom` AS `HostFrom`,`Syslog`.`AmalgamatedMailLog`.`FirstHostTo` AS `FirstHostTo`,`Syslog`.`AmalgamatedMailLog`.`Size` AS `Size`,`Syslog`.`AmalgamatedMailLog`.`Subject` AS `Subject`,`Syslog`.`AmalgamatedMailLog`.`AVScanResult` AS `AVScanResult`,`Syslog`.`AmalgamatedMailLog`.`SAAction` AS `SAAction`,`Syslog`.`AmalgamatedMailLog`.`SpamdResult` AS `SpamdResult`,right(`Syslog`.`AmalgamatedMailLog`.`FromAddr`,(length(`Syslog`.`AmalgamatedMailLog`.`FromAddr`) - locate(_utf8'@',`Syslog`.`AmalgamatedMailLog`.`FromAddr`))) AS `FromDomain`,right(`Syslog`.`AmalgamatedMailLog`.`FirstToAddr`,(length(`Syslog`.`AmalgamatedMailLog`.`FirstToAddr`) - locate(_utf8'@',`Syslog`.`AmalgamatedMailLog`.`FirstToAddr`))) AS `ToDomain` from `Syslog`.`AmalgamatedMailLog`; -- -------------------------------------------------------- -- -- Structure for view `vTop20SendersByCount` -- DROP TABLE IF EXISTS `vTop20SendersByCount`; CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `Syslog`.`vTop20SendersByCount` AS select `vMaillog`.`FromAddr` AS `FromAddress`,count(`vMaillog`.`FromAddr`) AS `MessageCount` from `Syslog`.`vMaillog` group by `vMaillog`.`FromAddr` order by count(`vMaillog`.`FromAddr`) desc limit 20; -- -------------------------------------------------------- -- -- Structure for view `vTop20SendingDomainsByCount` -- DROP TABLE IF EXISTS `vTop20SendingDomainsByCount`; CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `Syslog`.`vTop20SendingDomainsByCount` AS select `vMaillog`.`FromDomain` AS `FromDomain`,count(`vMaillog`.`FromDomain`) AS `MessageCount` from `Syslog`.`vMaillog` group by `vMaillog`.`FromDomain` order by count(`vMaillog`.`FromDomain`) desc limit 20;
Resources
http://www.rsyslog.com/tool-regex - Rsyslog regex tester
