2
\$\begingroup\$

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
    }
}
\$\endgroup\$

1 Answer 1

2
\$\begingroup\$

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()
}
\$\endgroup\$
1
  • \$\begingroup\$ 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. \$\endgroup\$
    – kafka
    Apr 7, 2016 at 9:18

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.