List of email-accounts and forwardings

[M_From_H]

Hi Forum,

how can I get an overview about my email-accounts and the forwardings resp. mailgroup entries? Is there any SQL-query availalable?

Thanks in advance.

--
BFN
Michael

 

[IgorG]

Look at mail and mail_redir tables at least.

 

[M_From_H]

Is there any SQL-example available? Or a field description for these tables?

 

[IgorG]

What about

desc mail;
desc mail_redir;

?

You can easily write SQL query by yourself.

 

[halliday]

Hi,
While searching for the same, found this:-

SELECT mail.mail_name,mail.redir_addr,accounts.password
FROM domains,mail,accounts
WHERE domains.id=mail.dom_id AND accounts.id=mail.account_id
ORDER BY domains.name ASC,mail.mail_name ASC;

It works for me.


More on the mail table:-

psa.mail
The mail table contains information about all of the e-mail accounts configured on the server.

Column Descriptions:-

id – primary key that is automatically incremented
mail_name – the name of the e-mail account (text before the @ sign)
perm_id – links to id in Permissions table
postbox – true if the mailbox is enabled, otherwise false
account_id – links to id in accounts table
redirect – true if e-mail should be redirected, otherwise false
redir_addr – contains an e-mail address for redirection, otherwise empty
mail_group – true if the e-mail account is a mail group, otherwise false
autoresponder – true if the account has an autoresponder, otherwise false
spamfilter – true if spam filtering is enabled, otherwise false
virusfilter – contains incoming, outgoing, any, or none
mbox_quota – limit on mailbox size in bytes
dom_id – links to id in domains


I hope that helps!

 

[M_From_H]

Thank you very much for your great support.

To get an overview about all redirections and mailgroup entries, I created a shell script:

#!/bin/bash
mysql -uadmin -p`cat /etc/psa/.psa.shadow` -Dpsa -e"select concat(mail.mail_name,'@',domains.name) as myEMail, mail.redirect, mail.redir_addr, mail.mail_group,mail_redir.address from domains,mail left join mail_redir on mail_redir.mn_id = mail.id WHERE domains.id=mail.dom_id order by domains.name ASC,mail.mail_name ASC ;"

Thus, I need only to type

./emails.sh | grep [email protected]

to get a quick overview for a single email account.

--
BFN
Michael

 

[TEN]

Seriously, there should be a way for "mere mortal" domain owners (even where there are several of them sharing a server, and without ssh access) to import all of their mailbox names and forwarding targets at once in a CSV file:
http://forum.parallels.com/showthread.php?t=88104#4

Moreover, they should have a way of writing their respective domain's .qmail and .procmailrc in /var/qmail/mailnames/their.dom so as to customize their anti-spam settings. An enormous lot of thought has gone into this already by many customers, and should make its way onto servers and into the product (so tech support's "can't be done" at many a hosting provider won't be blamed on Plesk):
http://forum.parallels.com/showthread.php?p=364943
http://netweblogic.com/linux/mail/filter-spam-folder-procmail-qmail-plesk/
http://rackerhacker.com/2007/11/27/sort-e-mail-in-plesk-with-procmail/
http://www.europheus.com/?p=66
http://forums.deftechgroup.com/showthread.php?t=235
http://www.russwittmann.com/2007/07/14/server-side-mail-filtering-using-qmailprocmail-under-plesk/
http://www.huschi.net/5_134_de-plesk-spamassassin-und-clamav-per-qsheff-einbinden.html