Posted by humansky on August 11, 2015 at 2:33pm
I'm trying to get a list of user emails that belong to the role 'site administrators' from a remote multisite Drupal environment. When I run the following command:
drush @remote_site sql-query 'SELECT DISTINCT u.mail FROM users AS u LEFT JOIN users_roles AS ur ON u.uid=ur.uid LEFT JOIN role AS r ON ur.rid=r.rid WHERE r.name = "site administrator"'
The SQL query works, however, it only works for the "default" site. Is there anyway to run this command for all the sites in my environment? I tried adding "@sites" after the remote alias, but it still only points to the default folder. Any ideas?
Thank you,
Henry
Comments
More quotes
You can run remote drush commands and quote the command (including the @sites portion). You'll just have to escape the quotes necessary for the r.name string.
drush @remote "@sites sqlq 'SELECT DISTINCT u.mail FROM users AS u LEFT JOIN users_roles AS ur ON u.uid=ur.uid LEFT JOIN role AS r ON ur.rid=r.rid WHERE r.name = \"site administrator\"'"
That should work to get you what you need.
@eporama, I tried this exact
@eporama, I tried this exact command, and it sorta worked. It looped through the same number of sites I have, so I know @sites is working, but it spit out the same exact response for each site. After I added the "-d" attribute and noticed that for each loop, the "--uri" attribute defaults to "http://default" instead of the uri for the site. I'm currently hosting on Acquia, so I wonder if there is something different in the way the @sites alias works. Here is my -d output with sensitive info redacted:
I had to change my --root, since my Drupal codebase is not in the docroot folder, but other than that, it should be like any other Acquia Drupal install.
Any thought? Perhaps I should open a ticket with Acquia?
What a coincidence
Interestingly enough, I am a manager in the Acquia Support department ;-) So I would definitely suggest that a ticket could be warranted.
I would say that the remote aliases we provide don't modify much in terms of what is being passed. However, we do specify a --uri in the alias pointing to your *.acquia-sites.com domain. I'm going to see if I can replicate this issue on a multisite that I have on our infrastructure.
And for everyone else, if we do discuss this in an internal ticket, we'll be sure to post whatever relevant answers we can once this is figured out.
sql query
You might also get faster results from
SELECT u.mail FROM users u INNER JOIN users_roles ur ON u.uid=ur.uid INNER JOIN role r ON ur.rid=r.rid AND r.name = "site administrator";
If there are a lot of users/roles, then this will be a much faster result.
Alternate sandbox drush command
There's also a sandbox of a Drush command "user-list" that could be useful, but it's not in core drush.
https://www.drupal.org/sandbox/jbickar/2493771