Question

How to get list of domains and their IP addresses in one query?

Answer

Run the following command from command line:

# MYSQL_PWD=`cat /etc/psa/.psa.shadow` mysql -u admin -Dpsa -e"SELECT dom.id, dom.name, ia.ipAddressId, iad.ip_address FROM domains dom LEFT JOIN DomainServices d ON (dom.id = d.dom_id AND d.type = 'web') LEFT JOIN IpAddressesCollections ia ON ia.ipCollectionId = d.ipCollectionId LEFT JOIN IP_Addresses iad ON iad.id = ia.ipAddressId"

It will result output as below:

+----+-------------------------------+-------------+------------+
| id | name                          | ipAddressId | ip_address |
+----+-------------------------------+-------------+------------+
| 21 | domain1.tld                   |           1 | 10.0.0.1   |
| 36 | domain2.tld                   |           1 | 10.0.0.1   |
| 38 | domain3.tld                   |           2 | 10.0.0.2   |
| 26 | domain4.tld                   |           2 | 10.0.0.2   |
+------------------------------------+-------------+------------+

For Windows version see article #124159 .