What tables to create for using SQL capability of MailScanner
conf.
Milenko Letic
mikoletic at gmail.com
Thu Apr 5 16:31:20 IST 2012
maybe this can help:
http://www.mailscanner.info/MailScanner.conf.index.html#SQL%20Ruleset
http://www.mailscanner.info/MailScanner.conf.index.html
Milenko Letic
2012/4/5 Milenko Letic <mikoletic at gmail.com>
> aha ok, so i am at home now, i will send you that info tomorow morrnig.
> Milenko Letic
>
> 2012/4/5 Tomáš Kurinec <tomb.stoney at gmail.com>
>
>> Thank you. It looks like mailwatch table structures, am I right? I
>> have mailwatch fully functional, but in mailscanner.conf there is:
>> # This should be a valid SQL statement that has a single placeholder
>> argument
>> # and must return two columns and one or more rows. The first column
>> must be
>> # a numeric starting at 1 and in ascending order and the second column
>> should
>> # be the rule string. The placeholder will contain the ruleset name.
>> #
>> # Example: SQL Ruleset = SELECT num, rule FROM ruleset WHERE
>> rulesetname=? ORDER BY num ASC
>>
>> So it's saying that you need num and rule columns and rulesetname as
>> placeholder. What do you have in SQL Ruleset = ? Or do you use just
>> mailwatch without setting this? If yes, than this is not what I was
>> loooking for, but thank you very much for the effort.
>>
>> Tom
>>
>> Dňa 5. apríla 2012 16:38, Milenko Letic <mikoletic at gmail.com> napísal/a:
>> > this is structure on my server:
>> > Tables_in_mailscanner |
>> >
>> >
>> > +-----------------------+
>> >
>> >
>> > | audit_log |
>> >
>> >
>> > | blacklist |
>> >
>> >
>> > | geoip_country |
>> >
>> >
>> > | inq |
>> >
>> >
>> > | maillog |
>> > | mcp_rules |
>> > | mtalog |
>> > | outq |
>> > | sa_rules |
>> > | saved_filters |
>> > | spamscores |
>> > | user_filters |
>> > | users |
>> > | whitelist
>> > and tables look like this:
>> > describe audit_log;
>> >
>> +------------+-------------+------+-----+-------------------+-----------------------------+
>> > | Field | Type | Null | Key | Default | Extra
>> > |
>> >
>> +------------+-------------+------+-----+-------------------+-----------------------------+
>> > | timestamp | timestamp | NO | | CURRENT_TIMESTAMP | on update
>> > CURRENT_TIMESTAMP |
>> > | user | varchar(20) | NO | | |
>> > |
>> > | ip_address | varchar(15) | NO | | |
>> > |
>> > | action | text | NO | | NULL |
>> > |
>> >
>> +------------+-------------+------+-----+-------------------+-----------------------------+
>> > 4 rows in set (0.08 sec)
>> > describe blacklist;
>> > +--------------+---------+------+-----+---------+----------------+
>> > | Field | Type | Null | Key | Default | Extra |
>> > +--------------+---------+------+-----+---------+----------------+
>> > | id | int(11) | NO | PRI | NULL | auto_increment |
>> > | to_address | text | YES | MUL | NULL | |
>> > | to_domain | text | YES | | NULL | |
>> > | from_address | text | YES | | NULL | |
>> > +--------------+---------+------+-----+---------+----------------+
>> > 4 rows in set (0.00 sec)
>> > describe geoip_country;
>> > +------------------+-------------+------+-----+---------+-------+
>> > | Field | Type | Null | Key | Default | Extra |
>> > +------------------+-------------+------+-----+---------+-------+
>> > | begin_ip | varchar(15) | YES | | NULL | |
>> > | end_ip | varchar(15) | YES | | NULL | |
>> > | begin_num | bigint(20) | YES | MUL | NULL | |
>> > | end_num | bigint(20) | YES | MUL | NULL | |
>> > | iso_country_code | char(2) | YES | | NULL | |
>> > | country | text | YES | | NULL | |
>> > +------------------+-------------+------+-----+---------+-------+
>> > 6 rows in set (0.00 sec)
>> > describe inq;
>> > +--------------+------+------+-----+---------+-------+
>> > | Field | Type | Null | Key | Default | Extra |
>> > +--------------+------+------+-----+---------+-------+
>> > | id | text | YES | | NULL | |
>> > | cdate | date | YES | | NULL | |
>> > | ctime | time | YES | | NULL | |
>> > | from_address | text | YES | | NULL | |
>> > | to_address | text | YES | | NULL | |
>> > | subject | text | YES | | NULL | |
>> > | message | text | YES | | NULL | |
>> > | size | text | YES | | NULL | |
>> > | priority | text | YES | | NULL | |
>> > | attempts | text | YES | | NULL | |
>> > | lastattempt | text | YES | | NULL | |
>> > | hostname | text | YES | MUL | NULL | |
>> > +--------------+------+------+-----+---------+-------+
>> > 12 rows in set (0.00 sec)
>> > describe maillog;
>> >
>> +-----------------+--------------+------+-----+-------------------+-----------------------------+
>> > | Field | Type | Null | Key | Default |
>> Extra
>> > |
>> >
>> +-----------------+--------------+------+-----+-------------------+-----------------------------+
>> > | timestamp | timestamp | NO | | CURRENT_TIMESTAMP | on
>> > update CURRENT_TIMESTAMP |
>> > | id | text | YES | MUL | NULL |
>> > |
>> > | size | bigint(20) | YES | | 0 |
>> > |
>> > | from_address | text | YES | MUL | NULL |
>> > |
>> > | from_domain | text | YES | MUL | NULL |
>> > |
>> > | to_address | text | YES | MUL | NULL |
>> > |
>> > | to_domain | text | YES | MUL | NULL |
>> > |
>> > | subject | text | YES | | NULL |
>> > |
>> > | clientip | text | YES | MUL | NULL |
>> > |
>> > | archive | text | YES | | NULL |
>> > |
>> > | isspam | tinyint(1) | YES | | 0 |
>> > |
>> > | ishighspam | tinyint(1) | YES | | 0 |
>> > |
>> > | issaspam | tinyint(1) | YES | | 0 |
>> > |
>> > | isrblspam | tinyint(1) | YES | | 0 |
>> > |
>> > | isfp | tinyint(1) | YES | | 0 |
>> > |
>> > | isfn | tinyint(1) | YES | | 0 |
>> > |
>> > | spamwhitelisted | tinyint(1) | YES | | 0 |
>> > |
>> > | spamblacklisted | tinyint(1) | YES | | 0 |
>> > |
>> > | sascore | decimal(7,2) | YES | | 0.00 |
>> > |
>> > | spamreport | text | YES | | NULL |
>> > |
>> > | virusinfected | tinyint(1) | YES | | 0 |
>> > |
>> > | nameinfected | tinyint(1) | YES | | 0 |
>> > |
>> > | otherinfected | tinyint(1) | YES | | 0 |
>> > |
>> > | report | text | YES | | NULL |
>> > |
>> > | ismcp | tinyint(1) | YES | | 0 |
>> > |
>> > | ishighmcp | tinyint(1) | YES | | 0 |
>> > |
>> > | issamcp | tinyint(1) | YES | | 0 |
>> > |
>> > | mcpwhitelisted | tinyint(1) | YES | | 0 |
>> > |
>> > | mcpblacklisted | tinyint(1) | YES | | 0 |
>> > |
>> > | mcpsascore | decimal(7,2) | YES | | 0.00 |
>> > |
>> > | mcpreport | text | YES | | NULL |
>> > |
>> > | hostname | text | YES | MUL | NULL |
>> > |
>> > | date | date | YES | MUL | NULL |
>> > |
>> > | time | time | YES | | NULL |
>> > |
>> > | headers | text | YES | | NULL |
>> > |
>> > | quarantined | tinyint(1) | YES | MUL | 0 |
>> > |
>> >
>> +-----------------+--------------+------+-----+-------------------+-----------------------------+
>> > 36 rows in set (0.01 sec)
>> > describe mtalog;
>> > +-----------+-------------+------+-----+---------+-------+
>> > | Field | Type | Null | Key | Default | Extra |
>> > +-----------+-------------+------+-----+---------+-------+
>> > | timestamp | datetime | YES | MUL | NULL | |
>> > | host | text | YES | | NULL | |
>> > | type | text | YES | MUL | NULL | |
>> > | msg_id | varchar(20) | YES | | NULL | |
>> > | relay | text | YES | | NULL | |
>> > | dsn | text | YES | | NULL | |
>> > | status | text | YES | | NULL | |
>> > | delay | time | YES | | NULL | |
>> > +-----------+-------------+------+-----+---------+-------+
>> > 8 rows in set (0.00 sec)
>> >
>> > describe mcp_rules
>> > -> ;
>> > +-----------+-----------+------+-----+---------+-------+
>> > | Field | Type | Null | Key | Default | Extra |
>> > +-----------+-----------+------+-----+---------+-------+
>> > | rule | char(100) | NO | PRI | | |
>> > | rule_desc | char(200) | NO | | | |
>> > +-----------+-----------+------+-----+---------+-------+
>> > 2 rows in set (0.00 sec)
>> >> describe outq;
>> > +--------------+------+------+-----+---------+-------+
>> > | Field | Type | Null | Key | Default | Extra |
>> > +--------------+------+------+-----+---------+-------+
>> > | id | text | YES | | NULL | |
>> > | cdate | date | YES | | NULL | |
>> > | ctime | time | YES | | NULL | |
>> > | from_address | text | YES | | NULL | |
>> > | to_address | text | YES | | NULL | |
>> > | subject | text | YES | | NULL | |
>> > | message | text | YES | | NULL | |
>> > | size | text | YES | | NULL | |
>> > | priority | text | YES | | NULL | |
>> > | attempts | text | YES | | NULL | |
>> > | lastattempt | text | YES | | NULL | |
>> > | hostname | text | YES | MUL | NULL | |
>> > +--------------+------+------+-----+---------+-------+
>> > 12 rows in set (0.00 sec)
>> > describe sa_rules;
>> > +-----------+--------------+------+-----+---------+-------+
>> > | Field | Type | Null | Key | Default | Extra |
>> > +-----------+--------------+------+-----+---------+-------+
>> > | rule | varchar(100) | NO | PRI | | |
>> > | rule_desc | varchar(200) | NO | | | |
>> > +-----------+--------------+------+-----+---------+-------+
>> > 2 rows in set (0.00 sec)
>> > describe saved_filters;
>> > +----------+------+------+-----+---------+-------+
>> > | Field | Type | Null | Key | Default | Extra |
>> > +----------+------+------+-----+---------+-------+
>> > | name | text | NO | MUL | NULL | |
>> > | col | text | NO | | NULL | |
>> > | operator | text | NO | | NULL | |
>> > | value | text | NO | | NULL | |
>> > | username | text | NO | | NULL | |
>> > +----------+------+------+-----+---------+-------+
>> > 5 rows in set (0.00 sec)
>> > describe spamscore;
>> > ERROR 1146 (42S02): Table 'mailscanner.spamscore' doesn't exist
>> > mysql> describe spamscores;
>> > +---------------+---------------+------+-----+---------+-------+
>> > | Field | Type | Null | Key | Default | Extra |
>> > +---------------+---------------+------+-----+---------+-------+
>> > | user | varchar(40) | NO | PRI | | |
>> > | lowspamscore | decimal(10,0) | NO | | 0 | |
>> > | highspamscore | decimal(10,0) | NO | | 0 | |
>> > +---------------+---------------+------+-----+---------+-------+
>> > 3 rows in set (0.00 sec)
>> > describe user_filters;
>> > +------------+---------------+------+-----+---------+-------+
>> > | Field | Type | Null | Key | Default | Extra |
>> > +------------+---------------+------+-----+---------+-------+
>> > | username | varchar(60) | NO | MUL | | |
>> > | filter | text | YES | | NULL | |
>> > | verify_key | varchar(32) | NO | | | |
>> > | active | enum('N','Y') | YES | | N | |
>> > +------------+---------------+------+-----+---------+-------+
>> > 4 rows in set (0.00 sec)
>> > describe users;
>> >
>> +-------------------+---------------------------+------+-----+---------+-------+
>> > | Field | Type | Null | Key | Default |
>> > Extra |
>> >
>> +-------------------+---------------------------+------+-----+---------+-------+
>> > | username | varchar(60) | NO | PRI | |
>> > |
>> > | password | varchar(32) | YES | | NULL |
>> > |
>> > | fullname | varchar(50) | NO | | |
>> > |
>> > | type | enum('A','D','U','R','H') | YES | | NULL |
>> > |
>> > | quarantine_report | tinyint(1) | YES | | 0 |
>> > |
>> > | spamscore | tinyint(4) | YES | | 0 |
>> > |
>> > | highspamscore | tinyint(4) | YES | | 0 |
>> > |
>> > | noscan | tinyint(1) | YES | | 0 |
>> > |
>> > | quarantine_rcpt | varchar(60) | YES | | NULL |
>> > |
>> >
>> +-------------------+---------------------------+------+-----+---------+-------+
>> > 9 rows in set (0.00 sec)
>> > describe whitelist;
>> > +--------------+---------+------+-----+---------+----------------+
>> > | Field | Type | Null | Key | Default | Extra |
>> > +--------------+---------+------+-----+---------+----------------+
>> > | id | int(11) | NO | PRI | NULL | auto_increment |
>> > | to_address | text | YES | MUL | NULL | |
>> > | to_domain | text | YES | | NULL | |
>> > | from_address | text | YES | | NULL | |
>> > +--------------+---------+------+-----+---------+----------------+
>> > 4 rows in set (0.00 sec)
>> >
>> >
>> > is this you looking for??
>> > i hope this help you.
>> > Milenko Letic
>> >
>> > 2012/4/5 Tomáš Kurinec <tomb.stoney at gmail.com>
>> >>
>> >> Oh God THANK YOU Peter!!!
>> >>
>> >> You seems to be only one who really got it. I thought it's obvious
>> what I
>> >> meant, but I was probably wrong. Still I can't figure it out.
>> >>
>> >> Please creators of MailScanner the great great, may be the best free
>> >> mighty tool. Can you write me the table structure that actually works
>> with
>> >> MailScanner? I'm probably stupid, because I can't get it from the
>> manual.
>> >> Can you accept my stupidity and write me the table structure? I would
>> be
>> >> very thankful and may be even donate your great project!
>> >>
>> >> Many many thanks.
>> >>
>> >> Tom
>> >>
>> >> Dňa 5.4.2012 15:43, "Peter Bonivart" <bonivart at opencsw.org>
>> napísal(-a):
>> >>
>> >>> On Thu, Apr 5, 2012 at 3:11 PM, Joolee <mailscanner at joolee.nl> wrote:
>> >>> > Well, if you did read the manual, you'd seen that there is a
>> property
>> >>> > called "SQL Debug" you can set to "Yes" to get more information from
>> >>> > Mailscanner about the MySQL connection.
>> >>> > You tell us MailScanner connected to MySQL properly? In that case,
>> you
>> >>> > can tell us what queries your MailScanner sends to MySQL and what
>> >>> > answers it receives. How do you know that Mailscanner connects
>> >>> > properly and the configured queries give the correct results?
>> >>>
>> >>> What he's asking for is to how to set up the database needed, he even
>> >>> gives an example of what he wants in his first post. Still people just
>> >>> post stuff like RTFM, turn debugging on, read the source, if you don't
>> >>> understand it you shouldn't run it and so on.
>> >>>
>> >>> I have snapped at people too but I have never seen so many feel
>> >>> obliged to reply to this guy without actually helping him the way he
>> >>> wants to be helped. If you can't or won't help him why do feel a need
>> >>> to tell him so? Can't you just ignore his post?
>> >>>
>> >>> If you have figured out how to set up a working database it wouldn't
>> >>> be so hard to dump the structure of it so he could import it. Maybe
>> >>> someone could post it to a wiki and help the next guy out as well.
>> >>>
>> >>> /peter
>> >>> --
>> >>> MailScanner mailing list
>> >>> mailscanner at lists.mailscanner.info
>> >>> http://lists.mailscanner.info/mailman/listinfo/mailscanner
>> >>>
>> >>> Before posting, read http://wiki.mailscanner.info/posting
>> >>>
>> >>> Support MailScanner development - buy the book off the website!
>> >>
>> >>
>> >> --
>> >> MailScanner mailing list
>> >> mailscanner at lists.mailscanner.info
>> >> http://lists.mailscanner.info/mailman/listinfo/mailscanner
>> >>
>> >> Before posting, read http://wiki.mailscanner.info/posting
>> >>
>> >> Support MailScanner development - buy the book off the website!
>> >>
>> >
>> >
>> > --
>> > MailScanner mailing list
>> > mailscanner at lists.mailscanner.info
>> > http://lists.mailscanner.info/mailman/listinfo/mailscanner
>> >
>> > Before posting, read http://wiki.mailscanner.info/posting
>> >
>> > Support MailScanner development - buy the book off the website!
>> >
>> --
>> MailScanner mailing list
>> mailscanner at lists.mailscanner.info
>> http://lists.mailscanner.info/mailman/listinfo/mailscanner
>>
>> Before posting, read http://wiki.mailscanner.info/posting
>>
>> Support MailScanner development - buy the book off the website!
>>
>
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://lists.mailscanner.info/pipermail/mailscanner/attachments/20120405/6ed2f428/attachment-0001.html
More information about the MailScanner
mailing list