What tables to create for using SQL capability of MailScanner
conf.
Milenko Letic
mikoletic at gmail.com
Thu Apr 5 15:38:35 IST 2012
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!
>
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://lists.mailscanner.info/pipermail/mailscanner/attachments/20120405/867f8a3e/attachment.html
More information about the MailScanner
mailing list