Resolved MailBox Quota show as unlimited vía CLI

[Ohya]

Server operating system version

Centos

Plesk version and microupdate number

18.0.46

Hi All

When I go to plesk -> domains -> mails accounts, I can see a list with emails account listed fine, With mail usage and mail quota, like 200MB used from 500 MB, so all as espected.

But when I see this information vía CLI to export all accounts mailbox quota return value is -1 ( unlimited )

I use this instructions to get list of mails vía cli: https://support.plesk.com/hc/en-us/...k-usage-via-a-command-line-interface-in-Plesk

So, plesk backend show all values fine but plesk database show all as -1

I run plesk repair mail without any changes.

But, I check psa.mail table and mbox_quota have correct values in bytes.

I run Plesk 18.0.46

So the querry appers to be incorrect in new plesk versions.

 

[Ohya]

Ok I not know why but query provided take MailBox Limit from psa_limit

There is no limit for mbox, the limit is in mail.mbox_quota

I modify the query and works fine now:

plesk db "SELECT concat(mail.mail_name,'@',domains.name) AS 'Email address',mn_param.val AS 'Mailbox usage',mail.mbox_quota AS 'Mailbox limit' FROM mail LEFT JOIN mn_param ON mail.id=mn_param.mn_id LEFT JOIN domains ON mail.dom_id=domains.id LEFT JOIN Subscriptions ON domains.id=Subscriptions.object_id LEFT JOIN SubscriptionProperties ON Subscriptions.id=SubscriptionProperties.subscription_id LEFT JOIN Limits ON SubscriptionProperties.value=Limits.id WHERE mn_param.param='box_usage' AND Subscriptions.object_type='domain' AND SubscriptionProperties.name='limitsId' AND Limits.limit_name='mbox_quota'"

 

[MiguelAbansys]

Hi eveyone,

Has anyone discovered a good way to search for an email account with mailbox limit exceeded?

I have modified the SQL query but it doesn't work properly.

My code is the following:

SELECT concat(mail.mail_name,'@',domains.name)
AS 'Email address',mn_param.val AS 'Mailbox usage',Limits.value AS 'Mailbox limit'
FROM mail LEFT JOIN mn_param ON mail.id=mn_param.mn_id
LEFT JOIN domains ON mail.dom_id=domains.id
LEFT JOIN Subscriptions ON domains.id=Subscriptions.object_id
LEFT JOIN SubscriptionProperties ON Subscriptions.id=SubscriptionProperties.subscription_id
LEFT JOIN Limits ON SubscriptionProperties.value=Limits.id
WHERE mn_param.param='box_usage'
AND Subscriptions.object_type='domain'
AND SubscriptionProperties.name='limitsId'
AND Limits.limit_name='mbox_quota'
AND 'box_usage' > 'mbox_quota';

There are accounts with the limit exceeded but it doesn't show me anything. In fact, the query itself is not precise and I don't know why.

It would be interesting also to search it by subscription.

I hope that someone can help with this. It should work for both Linux and Windows servers.

 

[Ohya]

Hi eveyone,

Has anyone discovered a good way to search for an email account with mailbox limit exceeded?

Hi

I do some awk piped output for plesk db query, for me works fine

You can run it from cli:

plesk db "SELECT concat(mail.mail_name,'@',domains.name) AS 'Email address',mn_param.val AS 'Mailbox usage',mail.mbox_quota AS 'Mailbox limit' FROM mail LEFT JOIN mn_param ON mail.id=mn_param.mn_id LEFT JOIN domains ON mail.dom_id=domains.id LEFT JOIN Subscriptions ON domains.id=Subscriptions.object_id LEFT JOIN SubscriptionProperties ON Subscriptions.id=SubscriptionProperties.subscription_id LEFT JOIN Limits ON SubscriptionProperties.value=Limits.id WHERE mn_param.param='box_usage' AND Subscriptions.object_type='domain' AND SubscriptionProperties.name='limitsId' AND Limits.limit_name='mbox_quota'" | awk '{if ($6 != -1 && $4 >= $6) {printf "%s: Quota Limit Exceded (%d >= %d)\n", $2, $4, $6}}'

The output will be

[email protected] Quota Limit Exceded (8648917934 >= 5368709120)

 

[MiguelAbansys]

Hi

I do some awk piped output for plesk db query, for me works fine

You can run it from cli:

plesk db "SELECT concat(mail.mail_name,'@',domains.name) AS 'Email address',mn_param.val AS 'Mailbox usage',mail.mbox_quota AS 'Mailbox limit' FROM mail LEFT JOIN mn_param ON mail.id=mn_param.mn_id LEFT JOIN domains ON mail.dom_id=domains.id LEFT JOIN Subscriptions ON domains.id=Subscriptions.object_id LEFT JOIN SubscriptionProperties ON Subscriptions.id=SubscriptionProperties.subscription_id LEFT JOIN Limits ON SubscriptionProperties.value=Limits.id WHERE mn_param.param='box_usage' AND Subscriptions.object_type='domain' AND SubscriptionProperties.name='limitsId' AND Limits.limit_name='mbox_quota'" | awk '{if ($6 != -1 && $4 >= $6) {printf "%s: Quota Limit Exceded (%d >= %d)\n", $2, $4, $6}}'

The output will be

[email protected] Quota Limit Exceded (8648917934 >= 5368709120)

Hi Ohya,

Thank you very much!!

It seems that it works fine, but unfortunately I don't have any server with email accounts with the limit exceeded right now.

I'll update with the result when I do an actual test.

Nevertheless, any manner to do it on Windows? I am a hosting technician who manages both Windows and Linux servers.

 

[Ohya]

Hi Ohya,

Thank you very much!!

It seems that it works fine, but unfortunately I don't have any server with email accounts with the limit exceeded right now.

I'll update with the result when I do an actual test.

Nevertheless, any manner to do it on Windows? I am a hosting technician who manages both Windows and Linux servers.

Finally I modify the original query now you can see only accounts over quota, this will works in windows and linux

plesk db "SELECT CONCAT(mail.mail_name, '@', domains.name) AS 'Email address', mn_param.val AS 'Mailbox usage', mail.mbox_quota AS 'Mailbox limit' FROM mail LEFT JOIN mn_param ON mail.id = mn_param.mn_id LEFT JOIN domains ON mail.dom_id = domains.id LEFT JOIN Subscriptions ON domains.id = Subscriptions.object_id LEFT JOIN SubscriptionProperties ON Subscriptions.id = SubscriptionProperties.subscription_id LEFT JOIN Limits ON SubscriptionProperties.value = Limits.id WHERE mn_param.param = 'box_usage' AND Subscriptions.object_type = 'domain' AND SubscriptionProperties.name = 'limitsId' AND Limits.limit_name = 'mbox_quota' AND mn_param.val >= mail.mbox_quota AND mail.mbox_quota <> -1;"

 

[MiguelAbansys]

Finally I modify the original query now you can see only accounts over quota, this will works in windows and linux

plesk db "SELECT CONCAT(mail.mail_name, '@', domains.name) AS 'Email address', mn_param.val AS 'Mailbox usage', mail.mbox_quota AS 'Mailbox limit' FROM mail LEFT JOIN mn_param ON mail.id = mn_param.mn_id LEFT JOIN domains ON mail.dom_id = domains.id LEFT JOIN Subscriptions ON domains.id = Subscriptions.object_id LEFT JOIN SubscriptionProperties ON Subscriptions.id = SubscriptionProperties.subscription_id LEFT JOIN Limits ON SubscriptionProperties.value = Limits.id WHERE mn_param.param = 'box_usage' AND Subscriptions.object_type = 'domain' AND SubscriptionProperties.name = 'limitsId' AND Limits.limit_name = 'mbox_quota' AND mn_param.val >= mail.mbox_quota AND mail.mbox_quota <> -1;"

Hi Ohya,

Thank you very much again!!

This is very kind from you.

I'll save that query for the future!

Greetings.