Resolved Get list of domains and their webroot path in one query?

[gbotica]

Hi,

I note this useful query to get all domains and their IP addresses from https://kb.plesk.com/en/116917

# mysql -uadmin -p`cat /etc/psa/.psa.shadow` -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"

Returns:

+----+-------------------------------+-------------+------------+
| 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   |
+------------------------------------+-------------+------------+

Could anyone rewrite this to select all domains and the webroot path in a space delimited list (or something...)

domain1.tld /var/www/vhosts/domain1.tld/httpdocs
domain2.tld /var/www/vhosts/domain2.tld/httpdocs
.. etc

That would be really useful for looping through in BASH scripts. Currently I use:

/usr/local/psa/bin/domain --list

and then loop through each domain, executing:

/usr/local/psa/bin/domain --info

and then grep the result to get the wwwroot path.

A single DB query would be so much easier ... is this possible?

Thanks.

 

[IgorG]

Try to use something like:

# plesk db "SELECT dom.id, dom.name, h.www_root FROM domains dom LEFT JOIN DomainServices d ON (dom.id = d.dom_id AND d.type = 'web') LEFT JOIN hosting h ON h.dom_id = dom.id"

 

[gbotica]

Try to use something like:

# plesk db "SELECT dom.id, dom.name, h.www_root FROM domains dom LEFT JOIN DomainServices d ON (dom.id = d.dom_id AND d.type = 'web') LEFT JOIN hosting h ON h.dom_id = dom.id"

Fantastic, thanks!

 

[gbotica]

Try to use something like:

# plesk db "SELECT dom.id, dom.name, h.www_root FROM domains dom LEFT JOIN DomainServices d ON (dom.id = d.dom_id AND d.type = 'web') LEFT JOIN hosting h ON h.dom_id = dom.id"

Can I format the output of

plesk db

? (links to 12.0 CLI docs seem to be broken...)

I need to use -s and -N for format the result so I can loop over it in a BASH script.

 

[IgorG]

You can use the same

# mysql -uadmin -p`cat /etc/psa/.psa.shadow` -Dpsa -e "select..."

with all necessary format options.