Loop through Plesk dbclient output using Powershell

Plesk’s PSA database holds all of the information the panel uses to manage the server. We can use this to our advantage when we want to automate a task for many items at once. Automation saves us time and ultimately money (you know, because time is money, right?).

Here is the code we will be looking at:

$sql = "select login, home from sys_users" &
"${env:plesk_bin}\dbclient.exe" --direct-sql --sql="$sql" | select-string -Not -Pattern "^$" | select -skip 1 | % {    
     $line = $_ -replace "\s+", " "    
     $parts = $line.split(' ')  
    $user = $parts[0];  
     $home = $parts[1];    
    echo "Home: $home, User: $user";  
}

---

I will explain it in parts:

$sql = "select login, home from sys_users" &
"${env:plesk_bin}\dbclient.exe" --direct-sql --sql="$sql"

First, we have the actual dbclient command, the dbclient executable can be found in %plesk_bin%. In powershell, environment variables like this are accessed as “$env:plesk_bin”. We use the ${env:plesk_bin} format to ensure there is conflict with how we are calling the variable. I have used an intermediate $sql variable to make it a little easier to read.

select-string -Not -Pattern "^$"

The dbclient output cannot be used right out of the gate, we need to format it first. To do this, we must first pipe the output into select-string -Not -Pattern "^$" so we can strip out any empty lines. This code simply means “Ignore all lines that start and then end immediately” (ie, empty line).

select -skip 1

This line simply skips the first line of output which will be the column headers. We don’t want this information in our loop, so we can just skip it.

Now we take this formatted output and pipe it into a for loop (% ). “%” is a shortcut command for “for-each”. It allows us to run commands against each line output.

$line = $_ -replace "\s+", " "

The first line in the loop ensures that there are no double spaces. It will shrink any number of sequential spaces down to a single space and saves it to $line. $_ is a reserved variable that contains each line in the for loop.

$parts = $line.split(' ')

This split command is where the magic happens. Here, we split the $line from the previous step in to $parts so we can access the data found in each individual column. $parts is an array of this data. $parts[0] is the first column, $parts[1] is the second, etc depending on how many columns you requested in the SELECT query.

$user = $parts[0];
$home = $parts[1];

Using the $parts variable, we assign more human readable variables per the previous paragraph. From here, we can now easily utilize the data from the dbclient output:

echo "Home: $home, User: $user";

The echo line at the end is just used as a proof of concept, but this process can be incredibly powerful.