• Our team is looking to connect with folks who use email services provided by Plesk, or a premium service. If you'd like to be part of the discovery process and share your experiences, we invite you to complete this short screening survey. If your responses match the persona we are looking for, you'll receive a link to schedule a call at your convenience. We look forward to hearing from you!
  • We are looking for U.S.-based freelancer or agency working with SEO or WordPress for a quick 30-min interviews to gather feedback on XOVI, a successful German SEO tool we’re looking to launch in the U.S.
    If you qualify and participate, you’ll receive a $30 Amazon gift card as a thank-you. Please apply here. Thanks for helping shape a better SEO product for agencies!
  • The BIND DNS server has already been deprecated and removed from Plesk for Windows.
    If a Plesk for Windows server is still using BIND, the upgrade to Plesk Obsidian 18.0.70 will be unavailable until the administrator switches the DNS server to Microsoft DNS. We strongly recommend transitioning to Microsoft DNS within the next 6 weeks, before the Plesk 18.0.70 release.
  • The Horde component is removed from Plesk Installer. We recommend switching to another webmail software supported in Plesk.

Issue Find all forwarded mail accounts - forwarding activated

Michael K

New Pleskian
Hallo

I try to find all mail accounts that are forwarded to certain domain
Since there is to my knowledge no command-line program for this, I look at the PSA database directly.

Code:
SELECT m.id,
        concat(lower(m.mail_name), '@', lower(d.name)) as mailaddress,
        (case when postbox = 'true' THEN 1 ELSE 0 END) AS is_postbox,
        count(r.id) as num_redirects,
        group_concat(r.address) as redirects,
        m.redirect,
        round (if(m.mbox_quota = -1, Limits.value, m.mbox_quota)/1024/1024) as 'mailquota',
        sfp_action.value as spam_action,
        sfp_required_score.value as spam_required_score
    FROM mail m
        LEFT JOIN domains d ON (m.dom_id = d.id)
        LEFT JOIN accounts a ON (m.account_id = a.id)
        LEFT JOIN Subscriptions ON (m.dom_id=Subscriptions.object_id) 
        LEFT JOIN SubscriptionProperties ON (Subscriptions.id=SubscriptionProperties.subscription_id AND SubscriptionProperties.name='limitsId')
        LEFT JOIN Limits ON (Limits.id=SubscriptionProperties.value AND limit_name = 'mbox_quota')
        LEFT JOIN mail_redir r ON (m.id = r.mn_id) 
    LEFT JOIN spamfilter sf ON (sf.username= concat(m.mail_name, "@", d.name))
    LEFT JOIN spamfilter_preferences sfp_action ON (sf.id=sfp_action.spamfilter_id AND sfp_action.preference='action')
    LEFT JOIN spamfilter_preferences sfp_required_score ON (sf.id=sfp_required_score.spamfilter_id AND sfp_required_score.preference='required_score')
    GROUP BY m.id 
    ORDER BY d.name , mailaddress


The query above lists all accounts where mail forwarding (a.k.a. redirect) is defined.
The trouble is that it does not catch if a forwarding address is inserted, but forwarding is switched off.
The DB field mail.redirect is always set to false.

Where do I need to look fort the extra info?

Thanks
 

Attachments

  • Auswahl_001.png
    Auswahl_001.png
    45.4 KB · Views: 6
Probably this will do it:
Code:
select concat(a.mail_name,'@',d.name) as EMAIL, c.address as FORWARDER, a.mail_group as Enabled, d.name from mail a, mail_redir c,domains d where a.id=c.mn_id and d.id=a.dom_id;

1589737342791.png

Edit:
Additionally, over the CLI you can get them also (slower than DB)
Code:
# for i in $(plesk bin mail -l | grep -v Alias |  awk '{print $NF}'); do plesk bin mail -i $i | grep Mailgroup |  awk '{print $NF}' | grep 'true' &> /dev/null; if [ $? == 0 ]; then echo "$i"; fi; done

1589738082726.png

This would output all emails that have the forwarding enabled
 
Last edited:
Back
Top