Today I learned a nice little trick one of our MSP partners uses in their PowerShell scripts. Provisior lets you define PowerShell parameters on a global level, so you can reuse them in different PowerShell templates you define. To do this, you link the parameters to the template and define them in the “Param” section of the scripts. This involves some manual steps when configuring a PowerShell template.

But, as I like to automate as much as possible, I was very pleased to see this partner did exactly that! Instead of linking the parameters to the template, they use this script to retrieve the parameters from the database and create PowerShell variables for them on the fly.

$SQLQuery = "USE $SQLDatabase SELECT * FROM $PowerShellGlobalParameters"
$SQLOutput = Invoke-Sqlcmd -query $SQLQuery -ServerInstance $SQLInstance

foreach ($Param in $SQLOutput){
    $Name = $Param.Name
    $Value = $Param.Value
    New-Variable -Name $Name
    Set-Variable -Name $Name -value $Value
}

Note: you need the SQL Server PowerShell module (sqlps) loaded on the Provisior server as we use Invoke-SQLcmd.