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