Generate report with all domains by client and plan (plesk)

plesk.sql

SELECT domains.name as domain, Templates.name as plan, domains.status, clients.type, clients.login, clients.pname, clients.email, clients.external_id, Subscriptions.locked, Subscriptions.synchronized, Subscriptions.custom
FROM domains
LEFT JOIN clients ON domains.cl_id = clients.id
LEFT JOIN Subscriptions ON domains.id = Subscriptions.object_id AND Subscriptions.object_type = 'domain'
LEFT JOIN PlansSubscriptions ON Subscriptions.id = PlansSubscriptions.subscription_id
LEFT JOIN Templates ON PlansSubscriptions.plan_id = Templates.id
WHERE clients.type = 'client'
AND clients.parent_id = '1'
AND domains.parentDomainId = '0'
INTO OUTFILE '/tmp/domains-client.csv';


# Reseller
SELECT Templates.name as plan, clients.status, clients.type, clients.login, clients.pname, clients.email, clients.external_id, Subscriptions.locked, Subscriptions.synchronized, Subscriptions.custom
FROM `clients`
LEFT JOIN Subscriptions ON clients.id = Subscriptions.object_id AND Subscriptions.object_type = 'client'
LEFT JOIN PlansSubscriptions ON Subscriptions.id = PlansSubscriptions.subscription_id
LEFT JOIN Templates ON PlansSubscriptions.plan_id = Templates.id
WHERE clients.type = 'reseller'
INTO OUTFILE '/tmp/reseller-client.csv';