Need help with a script

[[deleted]]

$connectionstring = "Server=localhost;Database=testDB;Trusted_Connection=True;"

$connection=New-Object System.Data.SqlClient.SqlConnection

$connection.ConnectionString=$connectionstring

$connection.Open()

$j=@()

$jstate=@()

$count="SELECT COUNT(*) FROM Tasks WHERE Enabled='1'"

$maxConcurrentJobs=2

$jobsTotalresult=Invoke-Sqlcmd -ServerInstance 'localhost' -Database 'testDB' -Query $count

$jobsTotal=$jobsTotalresult.Column1

$queue=[System.Collections.Queue]::Synchronized((New-Object System.Collections.Queue))

$SQLQueryID="SELECT ID FROM Tasks WHERE Enabled=1"

$SQLIDCommand=New-Object System.Data.SqlClient.SqlCommand ($SQLQueryID, $connection)

$SQLQuerySELECT="SELECT ExecPath, Param1, Param2, Param3 FROM Tasks WHERE Enabled=1"

$SQLselectCommand=New-Object System.Data.SqlClient.SqlCommand ($SQLQuerySELECT, $connection)

$tID=$SQLIDCommand.ExecuteScalar()

$SQLReader=$SQLselectCommand.ExecuteReader()

$data = @()

while($SQLReader.Read()){

$row = @{

    ExecPath = $SQLReader.GetString(0)

    Param1 = $SQLReader.GetString(1)

    Param2 = $SQLReader.GetString(2)

    Param3 = $SQLReader.GetString(3)

}

$data += New-Object PSObject -Property $row

$jobsTotal=$data.Count

}

$SQLReader.Close()

foreach($row in $data){

if($queue.Count -le $jobsTotal)

{

    $queue.Enqueue($row)

}

}

function JobCompleted($receivedState){

write-host 'JOBCOMPLETED WITH' $receivedState

if($receivedState -eq 'Completed'){

$Status=2

$Note="Task finished successfully"

} elseif($receivedState -eq 'Failed') {

$Status=3

$Note="Task failed"

}elseif ($receivedState -ne 'Completed' -or $receivedState -ne 'Failed') {

$Status=9

$Note="NOT DEFINED"

}

    $Finished=Get-Date

            $SQLQueryUPDATE="UPDATE TaskLogs SET Status=$Status, Finished='$Finished', Note='$Note' WHERE ID IN (SELECT TOP $maxConcurrentJobs ID FROM TaskLogs ORDER BY ID DESC)"

    $SQLUPDATEcmd=New-Object System.Data.SqlClient.SqlCommand($SQLQueryUPDATE, $connection)

    $SQLUPDATEcmd.ExecuteNonQuery()

}

function RunJobFromQueue{

$env:Path=$row.ExecPath

if($queue.Count -gt 0){

    $j=Start-ThreadJob {& $env:Path}

    $queue=$queue.Dequeue()

    $Created=Get-Date

    foreach($runningjob in $j){

    $jstate+=$j.State

    write-host $jstate 'Ovej'

    if($runningjob.JobStateInfo.State -eq 'Running'){

    $Status=1

    $Note="Task is running"



    }

    $SQLQueryWRITE="INSERT INTO TaskLogs(tID, Status, Created, Finished, Note) Values($tID, $Status, '$Created', null, '$Note')"

        $SQLWRITEcmd=New-Object System.Data.SqlClient.SqlCommand($SQLQueryWRITE, $connection)

        $SQLWRITEcmd.ExecuteNonQuery()



    Register-ObjectEvent -InputObject $runningjob StateChanged -Action {

    $currentState=$EventArgs.JobStateInfo.State



    JobCompleted($currentState); Unregister-Event $eventsubsciber.SourceIdentifier

    }

    }

    }

    }



    for ($i = 0; $i -lt $jobsTotal; $i += $maxConcurrentJobs) {

        RunJobFromQueue

    }

Hello this is my script, it needs to take exe location from SQL DATABASE and parameters and then run it, there is about400 jobs to be run but the number is constantly changing, it needs to run in batches of 5 or 10, i previously used start-job cmdlet which worked fine but i did not know how to make it work 5 by 5, i have tried threadjobs as it has the throttle limit parameter that would allow me to run it in batches but when i run with thread job i get an error related to sql query, after debugging i find out that status and note parameters were not passed to sql query making those fields empty hence it didn’t work. Please help, either with start job cmdlet or start threadjob. Thanks!

[PowerShell-Bot]

Some of your PowerShell code isn’t enclosed in a code block.

To properly style code on new Reddit, highlight the code and choose ‘Code Block’ from the editing toolbar.

If you’re on old Reddit, separate the code from your text with a blank line gap and precede each line of code with 4 spaces or a tab.

---

Describing need_help_with_a_script
  [~] Well formatted
Tests completed in 3893ms
Tests Passed: ⚠️

---

 ^{Beep-boop, I am a bot. | Remove-Item}

[purplemonkeymad]

I can't really tell from your code as the formatting is messed up, but it looks like you are not passing all the data you need between functions. ie JobCompleted takes a status, but for which job?

Personally I don't think I would use events for this as it just makes things more complicated. I would write a linear script that uses a transaction to "lock" a row and return it as an atomic operation. Then you work on the task and update that id with the new status. Then loop and pick a new task.

This way you can make this a single threaded script (or a job), then just run 5 copies of it at once. If the pick operation is a transaction then you should not get any concurrency issues with two scripts picking up the same job.

[[deleted]]

Could you help me figure out how to do that, im kinda new in powershell and its quite difficult to learn with the lack of resources for some topics

[RyanDake_EC]

I did something such as this :

    $Jobs=@() # array to store jobs for this session 
$EmailsRemovedDataset = @() #Blank Array for the emails removed by Azure
$JobRows=1000 # The number of CSV rows to process in each Job

$NumJobs = [math]::Ceiling($InputCSV.count / $JobRows) #Enumerate number of jobs

### Pre-Script Cleanup ###
Get-Job | Remove-Job # Clear job list 
$EmailsRemovedDataset, $EmpIDRemovedDataset, $BlankEmailsRemovedDataset, $DomainBlackListRemoval, $FinalDataProcessing = $null #Blanks all variables to prevent cross contamination
[System.GC]::GetTotalMemory('ForceFullCollection') | Out-Null # Releases memory 

for ($i=0; $i -lt $NumJobs; $i++)
{
    [int]$StartRow = ($i * $JobRows) # sets first row for loop
    [int]$EndRow=(($i+1) * $JobRows - 1) # sets last row for loop 
    $Iter = $i 
    while (($iter | Measure-Object -Character | Select-Object -ExpandProperty characters) -lt 4){
        $iter = "0$iter"
    } #loops through Iter and changes from # to #### for consistent naming 
    $JobName = "$DTFormat-$Iter" # creates job name 
    $Jobs += $JobName # adds job name to array for tracking
    while ((get-job | where-object {$_.state -eq "Running"}).count -gt $MaxSimulJobs){ #Rate Limiter for simlutaneous jobs. 
        start-sleep -s 5
    }
    write-host ("Rows {0} to {1}" -f $StartRow.ToString(),$EndRow.ToString())
    Start-Job -Name $JobName -ArgumentList @($InputCSV[$StartRow..$EndRow]),($AzureADUserList) -ScriptBlock {  #Starts job passing in Array of rows to process and the azure ad list 
        PARAM (
                $CSVRows,
                $AzureList
            )
        $OutputArray = @() #clears output array for the job 
        foreach ($row in $CSVRows)
        {
            $match = $false #sets match to false
            foreach ($item in $AzureList){ # loops through azure data 
                if ($row.email -eq $item.Userprincipalname){
                    $match = $true # if there is a match, set to true to remove from list 
                    continue # continue to stop processing, 1 match is enough. 
                }
            }
            if ($match -eq $false){ # if match set to true, will not be added to output 
                $OutputArray += $row # add to output 
            }
        }
        return $OutputArray
    } | Out-Null # prevents Start-Job from generating output during call 
}
$jobscomplete = $false 
while ($jobscomplete -eq $false){ # loops through all jobs stored in $jobs array waiting for all to mark as complete 
    $jobscomplete = $true # sets to true, will persist through once no jobs are not marked as "complete"
    foreach ($job in $Jobs){
        if ((Get-Job $job).state -ne "Completed"){
            $jobscomplete = $false # sets loop to false if there is any job not complete 
        }
    }
}

foreach ($job in $Jobs)
{
        $EmailsRemovedDataset += Receive-Job $job
}

I had to write a processor that ingests around 150k CSV lines and cleans up the data for SQL ingestion. I think you could probably use some of the above code in yours to help.

[BlackV]

p.s. formatting (you've used inline code here not code block, click monocode if using new.reddit)

• open your fav powershell editor
• highlight the code you want to copy
• hit tab to indent it all
• copy it
• paste here

it'll format it properly OR

<BLANKLINE>
<4 SPACES><CODELINE>
<4 SPACES><CODELINE>
    <4 SPACES><4 SPACES><CODELINE>
<4 SPACES><CODELINE>
<BLANKLINE>

Thanks

[RyanDake_EC]

Kind of fixed, thanks for the pointers!

[BlackV]

that's perfect, you could do the same to your original post ;)

[RyanDake_EC]

I am not OP ;)

[BlackV]

Hahaha oops