Plesk Question posted in
The official documentation for Plesk Obsidian can be found here.

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

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?

-thanks

UPDATE

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');
Tags:

3

Answers


  1. Chosen as BEST ANSWER
    0

    Solved:

    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;

  2. 0

    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
    Login or Signup to reply.
  3. 0

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

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search