This post demonstrates some Drush @sites usage, but perhaps better shows where the Drush @sites feature is at.

Tonight on a multisite installation, I wanted to run a query against every database. I had a play with Drush 3 HEAD, and found that I could get what I needed quite easily, and in a way that I will probably re-use.

Quick @sites example with variable-get

Say that you want to know the value of a particular variable on all of your sites. The first stop is to try the “variable-get” command.

# Navigate to the multisite’s root (or use the -r option)
drush @sites variable-get preprocess_css

The output is pretty clear, except you can’t identify the site that each variable is associated with.

preprocess_css: “1”

preprocess_css: “0”

preprocess_css: “1”

preprocess_css: “1”

…

I expect the smart guys working on Drush will fine-tune this over time because clean output will be required for other scripting goodness. But in my case this is not a problem and is easily worked around.

Execute a query

My solution was to construct a query that shows me the value of preprocess_css plus the site name. This is done by joining the variable table on itself with no ON clause. Then the WHERE clause removes rows we don’t want.

This looks a bit tricksy, but remember it’s just drush @sites sqlq "MY QUERY". The back slashes wrap the command over multiple lines so you could paste this into your bash prompt as is.


#The command is long, use a backslash to separate the command over a few lines.
drush @sites sqlq "SELECT v1.value AS site, v2.value AS val \
FROM {variable} v1, {variable} v2 \
WHERE v1.name = 'site_name' AND v2.name = 'preprocess_css'"

This does the job. I can now see which sites have CSS preprocessing turned off.

site val
s:11:”Hobbs.id.au”; s:1:”1″;

site val
s:9:”Eu no Rio”; s:1:”0″;

site val
s:28:”Patterson Lakes Netball Club”; s:1:”1″;

…

Another query example

Just another example of that query. In this example let’s find all the user/1 email addresses. I’ve done it here in raw SQL with explicit syntax.

SELECT v.value AS site, u.mail AS admin_mail
FROM users AS u
JOIN variable AS v
WHERE u.uid = 1
AND v.name = ‘site_name’

Putting that onto a drush command (remember to add {} around your table names if applicable!) you get something like:

site admin_mail
s:18:”Example Site”; [email protected]

site admin_mail
s:21:”Foo Site”; [email protected]

site admin_mail
s:15:”Bar Site”; [email protected]

Wrapping up

None of this I would consider best practice, but hopefully gives you an insight into how amazing and stable these tools are becoming! Good luck drushing.