Advertisement
Guest User

Untitled

a guest
Apr 4th, 2016
90
0
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
text 3.43 KB | None | 0 0
  1. Function Start-PollForCommands
  2. {
  3. while ($true)
  4. {
  5. ####
  6. # Section 1: Obtain Scrape Commands
  7. ####
  8. # Build up SQL query
  9. [string] $Server = ""
  10. [string] $Database = "Main_DB"
  11. [string] $UserSqlQuery= "SELECT
  12. FROM X.Y
  13. WHERE XYZ"
  14. [string] $SQLUser = "Redacted"
  15. [string] $SQLPW = "Redacted"
  16.  
  17. function ExecuteSqlQuery ($Server, $Database, $SQLQuery)
  18. {
  19. $Datatable = New-Object System.Data.DataTable
  20. $Connection = New-Object System.Data.SQLClient.SQLConnection
  21. $Connection.ConnectionString = "server='$Server';database='$Database';User ID ='$SQLUser';Password='$SQLPW';"
  22. #Write-host $Connection.ConnectionString
  23. $Connection.Open()
  24. $Command = New-Object System.Data.SQLClient.SQLCommand
  25. $Command.Connection = $Connection
  26. $Command.CommandText = $SQLQuery
  27. $Reader = $Command.ExecuteReader()
  28. $Datatable.Load($Reader)
  29. $Connection.Close()
  30.  
  31. return $Datatable
  32. }
  33.  
  34. $resultsDataTable = New-Object System.Data.DataTable
  35. $resultsDataTable = ExecuteSqlQuery $Server $Database $UserSqlQuery
  36.  
  37. if ($resultsDataTable -eq $null)
  38. {
  39. $output = "no rows returned!"
  40. exit 2
  41. }
  42.  
  43. # UPDATE SQL and set the RequestDate = GetDate()
  44. $rowID = $resultsDataTable.ForEach("RowID")
  45. foreach ($r in $rowID)
  46. {
  47. $UpdateQuery = "UPDATE Database.Table
  48. SET RequestDate = GETDATE()
  49. WHERE RowID = {0}" -f $r
  50. ExecuteSQLQuery $Server $Database $UpdateQuery
  51. }
  52.  
  53. $threads = $resultsDataTable.Count
  54. $array = 1..$threads # we can set number of threads 1..1 number of commands
  55. $scrapeCommand = $resultsDataTable.ForEach("CmdToInvoke")
  56.  
  57. ####
  58. # Section 2 run scrape commands in parallel
  59. ####
  60. # This script block will run the psexec command
  61. $ScriptBlock = {
  62. Param (
  63. [string]$scrapeCommand
  64. )
  65. $PSExec = "D:pstoolsPsExec.exe"
  66. $scrapeCommand = $scrapeCommand.ToString().Substring(18) # keep command args only
  67. Start-Process -FilePath $PSExec -ArgumentList $scrapeCommand
  68. }
  69.  
  70. # Create session state
  71. $myString = "this is session state!"
  72. $sessionState = [System.Management.Automation.Runspaces.InitialSessionState]::CreateDefault()
  73. $sessionstate.Variables.Add((New-Object -TypeName System.Management.Automation.Runspaces.SessionStateVariableEntry -ArgumentList "myString" ,$myString, "example string"))
  74.  
  75. # Create runspace pool consisting of $Threads runspaces
  76. $RunspacePool = [RunspaceFactory]::CreateRunspacePool(1, $threads, $sessionState, $Host)
  77. $RunspacePool.Open()
  78.  
  79. $Jobs = @()
  80. $array | % {
  81. $scrapeCommand = $scrapeCommand[$array]
  82. $rowID = $rowID[$array]
  83. $Job = [powershell]::Create().AddScript($ScriptBlock).AddParameter("scrapeCommand", $scrapeCommand)
  84. $Job.RunspacePool = $RunspacePool
  85. $Jobs += New-Object PSObject -Property @{
  86. RunNum = $_
  87. Job = $Job
  88. Result = $Job.BeginInvoke()
  89. }
  90. }
  91. Start-Sleep 1
  92. }
  93. }
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement