BigFix Action Script for SQL Server and Powershell

My client had several domains, datacenters and networks. Opening ports across all networks and domains would be a security risk and until these days, every automation script deployed had to be run against individual network grouped servers.

I was then fortunate enough to have access on IBM BigFix automation software and the opportunity to explore it’s powers. The BigFix software already had it’s agents running on all servers across all networks. That sparked an idea in my mind to make use of those agents to execute scripts against all servers to help collect data or perform some action or maintenance.

Here is the script used to run a random powershell script against all instances on a list of servers. Please go through the script and I will explain about it below the script.

//============================================================================
//PowerShell Script... 
//
//1. Save old ExecutionPolicy value
parameter "PolicyExisted"="{exists value "ExecutionPolicy" of key "HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\PowerShell\1\ShellIds\Microsoft.PowerShell" of (if exists x64 registry then x64 registry else registry)}"
parameter "oldExecutionPolicy"="{if (parameter "PolicyExisted" as boolean) then (value "ExecutionPolicy" of key "HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\PowerShell\1\ShellIds\Microsoft.PowerShell" of (if exists x64 registry then x64 registry else registry) as string) else ""}"
//2. set to ExecutionPolicy=Unrestricted  and Pull PowerShell exe from registry... if 64bit then pull PowerShell x64
if {x64 of operating system}
    regset64 "[HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\PowerShell\1\ShellIds\Microsoft.PowerShell]" "ExecutionPolicy"="Unrestricted"
    parameter "PowerShellexe"="{value "Path" of key "HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\PowerShell\1\ShellIds\Microsoft.PowerShell" of x64 registry}"
else
	//we need to determine what the current execution policy is so we can put it back when we're done.
    regset "[HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\PowerShell\1\ShellIds\Microsoft.PowerShell]" "ExecutionPolicy"="Unrestricted"
    parameter "PowerShellexe"="{value "Path" of key "HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\PowerShell\1\ShellIds\Microsoft.PowerShell" of registry}"
endif
//3. Create PowerShell script and save to a ps1 file
if {not exists folder "c:\temp"}
waithidden cmd.exe /C mkdir c:\temp
else
endif

delete __createfile
delete c:\temp\script.ps1

createfile until END_OF_FILE
$instances = (Get-ItemProperty ‘HKLM:\Software\Microsoft\Microsoft SQL Server\’).InstalledInstances


foreach($instance in $instances) {
    Write-Output $instance
    if ($instance -eq "MSSQLSERVER")
    {
        $instance = "."
    }
    else
    {
        $instance = ".\$instance"
    }
    $results = Invoke-Sqlcmd -Query "
                select @@servername as InstanceName,
                SERVERPROPERTY('COMPUTERNAMEPHYSICALNETBIOS') as Node,
                SERVERPROPERTY('productversion') AS ProductVersion,
                SERVERPROPERTY ('productlevel') AS ProductLevel" -ServerInstance $instance
}

c:

Export-Csv -Path \\fileserver\DataCollector\BigFixFetch.csv -InputObject $results -Append -NoTypeInformation
END_OF_FILE

move __createfile c:\temp\script.ps1
//4. Execute PowerShell with ps1 script file
action uses wow64 redirection false 
waithidden "{parameter "PowerShellexe"}" -file "C:\temp\script.ps1"
action uses wow64 redirection {x64 of operating system}
//5. Restore ExecutionPolicy back
if {x64 of operating system}
    if {parameter "PolicyExisted" as boolean}
        regset64 "[HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\PowerShell\1\ShellIds\Microsoft.PowerShell]" "ExecutionPolicy"="{parameter "oldExecutionPolicy"}"
    else
        regdelete64 "[HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\PowerShell\1\ShellIds\Microsoft.PowerShell]" "ExecutionPolicy"
    endif
else
    if {parameter "PolicyExisted" as boolean}
        regset "[HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\PowerShell\1\ShellIds\Microsoft.PowerShell]" "ExecutionPolicy"="{parameter "oldExecutionPolicy"}"
    else
        regdelete "[HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\PowerShell\1\ShellIds\Microsoft.PowerShell]" "ExecutionPolicy"
    endif
endif
//============================================================================

//1. Save old ExecutionPolicy value: This step would check the target server powershell execution policy value. If it is set to restricted policy, the script execution would fail. Hence this step would check the policy and saved into a variable “PolicyExisted” for rollback after script execution

//2. set to ExecutionPolicy=Unrestricted and Pull PowerShell exe from registry… if 64bit then pull PowerShell x64: Sets the execution policy to Unrestricted and also finds the path for the powershell executable into variable “PowerShellexe”.

//3. Create PowerShell script and save to a ps1 file: Creates powershell script and saves to a local drive on the target server to execute in the next step. The contents in the file is simply pasted between the lines.
“createfile until END_OF_FILE” and “END_OF_FILE”.

//4. Execute PowerShell with ps1 script file: Executes the powershell script

//5. Restore ExecutionPolicy back: Restores the powershell execution policy stored in variable “PolicyExisted”