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 [MySQL] psa database to get a list of all domains expiration dates?
Ok, a little more progress, by enabling & tailing the mysql query log I found these queries that will get the info I need:
select `id`, `name`, `displayName`, `cr_date`, `status`, `webspace_status`, `dns_zone_id`, `htype`, `cl_id`, `vendor_id`, `webspace_id`, `parentDomainId`, `cert_rep_id`, `real_size`, `guid`, `external_id`, `adminDescription`, `resellerDescription`, `description`, `overuse` from `domains` where `id`=110; SELECT `Subscriptions`.* FROM `Subscriptions` AS `Subscriptions` WHERE (`object_type` = 'domain' AND `object_id` = 110); SELECT `SubscriptionProperties`.* FROM `SubscriptionProperties` AS `SubscriptionProperties` WHERE (`subscription_id` = '70'); SELECT `Limits`.* FROM `Limits` AS `Limits` WHERE (`id` = '77');
The last little bit, id 77, comes from a limitsid value in the SubscriptionProperties table, how can I join all these queries into one so I can create a report?
I’m working on something like this, but not making any headway:
select s.* , d.name, l.* from Subscriptions s left join domains d on s.object_id = d.id left join SubscriptionProperties sp on sp.subscription_id = s.id left join Limits l on l.id = (select SubscriptionProperties.value from SubscriptionProperties where subscription_id = s.id and name = 'limistid');