What tables to create for using SQL capability of MailScanner
conf.
Milenko Letic
mikoletic at gmail.com
Thu Apr 5 16:17:52 IST 2012
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/e2918330/attachment.html
More information about the MailScanner
mailing list