Powershell task to poll SQL database for psexec commands to run

Question

We have a requirement for a script which polls an SQL database for scrape commands which need to be run. These commands are added to the SQL database on an ad-hoc basis. The scrape commands themselves are in the form of psexec commands, and need to be run by the listener script which is constantly polling the database for updated commands. At the same time the commands are marked as having been run too.

The listener script is installed as a service on the server, which means that it starts automatically on server boot up, and also ensures that the script is constantly listening (with a gap of 1 second between listens).

Function Start-PollForCommands
{ 
 while ($true) 
 { 
 ####
 # Section 1: Obtain Scrape Commands
 ####
 # Build up SQL query 
 [string] $Server = ""
 [string] $Database = "Main_DB"
 [string] $UserSqlQuery= "SELECT 
 FROM X.Y
 WHERE XYZ"
 [string] $SQLUser = "Redacted"
 [string] $SQLPW = "Redacted" 
 function ExecuteSqlQuery ($Server, $Database, $SQLQuery) 
 {
 $Datatable = New-Object System.Data.DataTable 
 $Connection = New-Object System.Data.SQLClient.SQLConnection
 $Connection.ConnectionString = "server='$Server';database='$Database';User ID ='$SQLUser';Password='$SQLPW';"
 #Write-host $Connection.ConnectionString
 $Connection.Open()
 $Command = New-Object System.Data.SQLClient.SQLCommand
 $Command.Connection = $Connection
 $Command.CommandText = $SQLQuery
 $Reader = $Command.ExecuteReader()
 $Datatable.Load($Reader)
 $Connection.Close()
 return $Datatable
 }
 $resultsDataTable = New-Object System.Data.DataTable
 $resultsDataTable = ExecuteSqlQuery $Server $Database $UserSqlQuery 
 if ($resultsDataTable -eq $null) 
 {
 $output = "no rows returned!" 
 exit 2
 }
 # UPDATE SQL and set the RequestDate = GetDate() 
 $rowID = $resultsDataTable.ForEach("RowID")
 foreach ($r in $rowID)
 {
 $UpdateQuery = "UPDATE Database.Table 
 SET RequestDate = GETDATE()
 WHERE RowID = {0}" -f $r
 ExecuteSQLQuery $Server $Database $UpdateQuery
 } 
 $threads = $resultsDataTable.Count
 $array = 1..$threads # we can set number of threads 1..1 number of commands 
 $scrapeCommand = $resultsDataTable.ForEach("CmdToInvoke") 
 ####
 # Section 2 run scrape commands in parallel
 #### 
 # This script block will run the psexec command
 $ScriptBlock = {
 Param (
 [string]$scrapeCommand 
 )
 $PSExec = "D:\pstools\PsExec.exe" 
 $scrapeCommand = $scrapeCommand.ToString().Substring(18) # keep command args only 
 Start-Process -FilePath $PSExec -ArgumentList $scrapeCommand 
 } 
 # Create session state
 $myString = "this is session state!"
 $sessionState = [System.Management.Automation.Runspaces.InitialSessionState]::CreateDefault()
 $sessionstate.Variables.Add((New-Object -TypeName System.Management.Automation.Runspaces.SessionStateVariableEntry -ArgumentList "myString" ,$myString, "example string"))
 # Create runspace pool consisting of $Threads runspaces
 $RunspacePool = [RunspaceFactory]::CreateRunspacePool(1, $threads, $sessionState, $Host)
 $RunspacePool.Open()
 $Jobs = @() 
 $array | % {
 $scrapeCommand = $scrapeCommand[$array] 
 $rowID = $rowID[$array]
 $Job = [powershell]::Create().AddScript($ScriptBlock).AddParameter("scrapeCommand", $scrapeCommand)
 $Job.RunspacePool = $RunspacePool
 $Jobs += New-Object PSObject -Property @{
 RunNum = $_
 Job = $Job
 Result = $Job.BeginInvoke()
 }
 }
 Start-Sleep 1
 }
}

Answer 1

Pretty clean code. Not much do in the way of improvement but there are some things worth pointing out.

Magic Numbers

$scrapeCommand = $scrapeCommand.ToString().Substring(18)

Not sure how resilient that is but if you are only doing that to drop the application/executable from the string I would consider doing something different. If the path did not contain spaces then you could split on the first space. Not the best example since that creates another issue. Regex would probably a better idea but without knowing what the $scrapeCommand looks like I can only guess.

PSexec

Love that program and will continue to do so. However when it comes to PowerShell Invoke-Command is supposed to take over that responsibility. Now, I still use psexec in my environment as we have no configured PowerShell remoting on all systems so I still need it from time to time. Try Invoke-Command and perhaps you won't need to rely on 3rd-party utilities.

Master the pipeline

I see that you are building a $jobs array using +=. That is inefficient as it destroys the array and makes a new one with the new element each time. It would be better to let the pipeline handle it.

$jobs = $array | % {
 $scrapeCommand = $scrapeCommand[$array] 
 $rowID = $rowID[$array]
 $Job = [powershell]::Create().AddScript($ScriptBlock).AddParameter("scrapeCommand", $scrapeCommand)
 $Job.RunspacePool = $RunspacePool
 New-Object PSObject -Property @{
 RunNum = $_
 Job = $Job
 Result = $Job.BeginInvoke()
 }
}

If you have at least PowerShell 3.0 then you can use the type accelerator [pscustomobject]

[pscustomobject]@{
 RunNum = $_
 Job = $Job
 Result = $Job.BeginInvoke()
}

• kafka: Thanks for the feedback, will comment later. Reason I've stuck with PSexec in this instance is that didn't have PS Remoting on target system (time factor more than anything) but in future no reason why I can't use this.