List of suspend domains

[KrunoslavV]

Hello,

I need to get a list of suspend domains from mysql. The domains are suspend because they exceeded quota.

So far I have done this query:

#############

select domains.id,
domains.name as domena,
Round(((sum(domains.real_size))/1048576),2) realMB,

(select Round((limits.value/1048576),2) from limits left join (domains, subscriptions, subscriptionproperties) on (domains.id=subscriptions.object_id and subscriptions.id=subscriptionproperties.subscription_id and subscriptionproperties.value=limits.id) where subscriptionproperties.name='limitsId' and limits.limit_name='disk_space' and domains.name=domena) quotaMB

from domains

where domains.status='2' and
(select (sum(domains.real_size)))>0

group by domains.cl_id;

#############

but I didnt managed to get only those that exceed the quota.

Can someone help me?

Thank you,
Kruno

 

[KrunoslavV]

I think I managed to do

#########
select domains.id, domains.name as domena, Round(((sum(domains.real_size))/1048576),2) realMB, (select Round((limits.value/1048576),2) from limits left join (domains, subscriptions, subscriptionproperties) on (domains.id=subscriptions.object_id and subscriptions.id=subscriptionproperties.subscription_id and subscriptionproperties.value=limits.id) where subscriptionproperties.name='limitsId' and limits.limit_name='disk_space' and domains.name=domena) quotaMB from domains where domains.status='2' and (select (sum(domains.real_size)))>(select limits.value from limits where limits.id=(select subscriptionproperties.value from subscriptionproperties where subscriptionproperties.subscription_id=(select subscriptions.id from subscriptions where subscriptions.object_id=domains.id) and subscriptionproperties.name='limitsId') and limits.limit_name='disk_space') group by domains.cl_id;
#########

 

[KrunoslavV]

no good

 

[KrunoslavV]

select tab.min_id, tab.displayname, Round(tab.velicina/1048576,2) real_size, Round(l.value/1048576,2) kvota from (select min(id) min_id, cl_id,displayname, sum(real_size) velicina from domains d where status=2 group by cl_id) tab, subscriptionproperties sp, limits l, subscriptions sb where tab.min_id=sb.object_id and sb.id=sp.subscription_id and name='limitsId' and sp.value=l.id and l.limit_name='disk_space' and tab.velicina > l.value;

done