Issue Find all forwarded mail accounts - forwarding activated

[Michael K]

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.

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

 

[Martin Dias]

Probably this will do it:

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;

Edit:
Additionally, over the CLI you can get them also (slower than DB)

# 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

This would output all emails that have the forwarding enabled