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