Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- 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:pstoolsPsExec.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
- }
- }
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement