How to get list of all domains or customers owned by resellers via Plesk CLI

Question

How to get a list of all domains or clients owned by resellers via CLI?

Answer

The information about domains or clients owned by resellers can be retrieved from Plesk database:

1. Connect to the server via SSH/RDP.

2. Access Plesk database (in Windows, start Command Prompt first):

   # plesk db

3. List of reseller logins and their domains:

   select clients.login, domains.name from domains,clients where domains.cl_id=clients.id and type='reseller';
   +------------+-------------------+
   | login | name |
   +------------+-------------------+
   | example | example.com |
   +------------+-------------------+

4. List of reseller logins, reseller names and their domains:

   select clients.login, pname, domains.name from domains,clients where domains.cl_id=clients.id and type='reseller';
   +---------+---------+--------------+
   | login | pname | name |
   +----------+-------+---------------+
   | example | John Doe| example.com |
   +----------+-------+---------------+

5. List of domains belonging to a specific reseller:

   select clients.pname, domains.name from domains,clients where domains.cl_id=clients.id and type='reseller'and pname='John Doe';
   +---------+--------------+
   | pname | name |
   +---------+--------------+
   | John Doe| example.com |
   +---------+--------------+

6. List of customers belonging to a specific reseller:

   select group_concat(c.pname) as 'Clients',c1.pname as 'Reseller' from clients c left join clients c1 on c.vendor_id = c1.id where c.vendor_id > 1 and c1.pname = 'John Doe' group by c.vendor_id;
   +-----------+----------+
   | Clients | Reseller |
   +-----------+----------+
   | example,example1 | John Doe |
   +-----------+----------+
   1 row in set (0.00 sec)