How to get the expiration dates of all domains from a [MySQL] Plesk database?

[BigBlock]

I used to have this neat little report that ran on a dashboard I have for my daily stuff that would report on the expiration dates of all my hosted plesk domains [Plesk 9]

It looked like this:

select d.name, l.* from Limits l 
left join domains d on l.id =d.id 
where l.limit_name = "expiration" and d.name != ""
order by value

Very simple, I could just peek at it at the beginning of the month and the code I used to sort it and make human readable dates highlighted all the ones to be billed in the current month. Great.

I updated to Plesk 12, this is now broken as they seem to have changed the schema, for the life of me I can't seem to figure it out.

I've tried something like this:

 select d.name, l.* from Limits l 
    left join clients c on c.pool_id = l.id
    left join domains d on c.id = d.cl_id 
    where l.limit_name = "expiration" and d.name != ""
    order by value ASC;

This does not seem to work either, just taking a sample of the results, a few domains show the expiration to be -1, but viewing them in Plesk show expiration dates. [expiration dates that match my last billing]


Does anyone know how to query the psa database to get a list of all domains expiration dates?

-thanks

 

[abdi]

Get a billing application such as www.G7Bill.com

Otherwise, start by accessing the psa database through PhpMyAdmin and look at the table fields for anything that might changed so you update your own script too ...

You can by navigating to Tools & Settings -> Database server management -> DB Admin (or related)

 

[IgorG]

Use something like:

# for i in `mysql -uadmin -p\`cat /etc/psa/.psa.shadow\` psa -Ns -e "select name from domains"`; do /usr/local/psa/bin/domain -i $i; done | grep -B4 -i expir

 

[BigBlock]

@IgorG - I see you found the question on stackexchange as well. I have not tested your solution, it may work fine, but I'm running the report from a replicated copy of the psa database [I run a nightly backup of the production environment to my development environment] & the plesk scripts/tools are not available.

 

[IgorG]

You can find expiration date for domains subscription ID in Limits table of psa database.

 

[BigBlock]

I don't see anything in the domains table indicating a subscription ID, the domain id and cl_id do not match the limits table. I don't see any tables indicating they might be a subscription.

Where does the subscription ID come from & how does it relate to the domain & limit table?

Sorry - nope, there I see a subscription table now... it was hiding.. !

 

[IgorG]

Actually in Plesk you can set up expiration date for subscription but not for domains. If you mean expiration date of domain according their registrar - Plesk does not maintain this metric.

 

[BigBlock]

still does not match up.

select s.id, s.object_id, d.name, l.* from Subscriptions s
left join domains d on d.id = s.object_id
left join Limits l on l.id = s.id
where l.limit_name = "expiration"
order by d.name DESC;

the object id seems to match the domain id, but not the limits... whether you want to call it a domain or a subscription is either way to me, I just need to know when a users domain/subscription is expiring so I can bill them

 

[BigBlock]

Found the solution:

select d.`name`, l.* from SubscriptionProperties sp
left join Subscriptions s on s.id = sp.subscription_id
left join domains d on d.id = s.object_id
left join Limits l on l.id = sp.value
where sp.`name` = 'limitsId' and l.limit_name = 'expiration'
order by value;

the SubscriptionProperties table holds the id for the records in the limits table.

 

[Ed_Greenberg]

If I understand correctly, a value of 2d31 is unlimited, and a value other than that has an expiration date. Correct?

 

[BigBlock]

Looking at my tables, -1 is an expiration with no limit and the rest are unix times.

 

[Ed_Greenberg]

Sounds right, although my (plesk supplied) version of phpMyAdmin is doing something funny with character sets there. No worries.

This is handy for checking servers, where we don't use the expiration feature.

Thanks.