Question Query for "perfect" overview?

[Calvin Aachen]

Hello guys!

I've been fiddling around with the webinterface and the "CSV Extension". Unluckily it is nearly impossible to merge multiple CSV sheets, which I extracted from the webinterface, into one big CSV which contains EVERY info about ServicePlan,Subscription,Domain.

I dont see any other way than that. I need to connect per SSH and use "plesk db".

Is anybody here willing to help me make this query? It is very important to me to deliver a good product and this is one step I'm currently struggeling with to achieve this.

For interested readers, I would like to get an output like this:

Domainname, Subscriber, ServicePlan, CreationDateOfDomain, UsedDiskSpace, BookedDiscSpace, UsedTraffic, BookedTraffic

I really tried my best to understand the database scheme but at one point I couldnt see any connections anymore.

Thank you for reading my question. And hopefully there is a way to achieve a solution for my problem!

 

[Brujo]

well maybe this would be a starting point for you and the query need some adjustments for your specified needs

How to export a domains list with status, service plan, disk usage, traffic and expiration date to Excel file?

 

[carini]

This can be a good starting point, I take from examples from this forum and added something on my own ...

SELECT `cli`.`account_id`, `cli`.`parent_id`, `cli`.`cname`, `cli`.`pname`, `dom`.`name`, `dom`.`status`, `sub`.`locked`, `sub`.`synchronized`, `tpl`.`name`
FROM `clients` `cli`
INNER JOIN `domains` `dom` ON `cli`.`id` = `dom`.`cl_id`
INNER JOIN `Subscriptions` `sub` ON (`dom`.`id` = `sub`.`object_id` AND `sub`.`object_type` = 'domain')
INNER JOIN `PlansSubscriptions` `pla` ON `sub`.`id` = `pla`.`subscription_id`
INNER JOIN `Templates` `tpl` ON `pla`.`plan_id` = `tpl`.`id`;