What tables to create for using SQL capability of MailScanner
conf.
Tomáš Kurinec
tomb.stoney at gmail.com
Thu Apr 5 16:01:13 IST 2012
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!
>
More information about the MailScanner
mailing list