Issue while exporting output to CSV in PowerShell Parallel Workflow

Question

I am using below script to get the details about port status of multiple remote servers.

Workflow Test-OpenPortWF
{
    [CmdletBinding()]
    param
    (
        [Parameter(Position=0)]
        [String[]]$Target,

        [Parameter(Mandatory=$true, Position=1)]
        [int]$Port
    )

    If(Test-Path -Path C:\Temp\Results.csv -ErrorAction SilentlyContinue){ Remove-Item -Path C:\Temp\Results.csv -Force }
    If(Test-Path -Path C:\Temp\Report.csv -ErrorAction SilentlyContinue){ Remove-Item -Path C:\Temp\Report.csv -Force }

    foreach -parallel -throttle 50 ($t in $Target)
    {
        Sequence
        {
            $Out = Test-NetConnection -ComputerName $t -Port $Port -WarningAction SilentlyContinue | Select ComputerName,RemoteAddress,RemotePort,@{N="PortTestSucceeded"; E={$_.tcpTestSucceeded}}
            Start-Sleep -Milliseconds 100
            $Out | Export-Csv -Path C:\Temp\Results.csv -NoTypeInformation -Append 
        }
    }

    InlineScript
    {
        Import-Csv c:\Temp\Results.csv | Select ComputerName,RemoteAddress,RemotePort,PortTestSucceeded  | Export-Csv c:\Temp\Report.csv -NoTypeInformation
        Remove-Item c:\Temp\Results.csv -Force
        Write-Host "Execution completed! Check Report.csv for output."
    }
}


# Example use for multiple servers for one port 5985 and export results to CSV file.
# Assuming all target servers are found in c:\temp\Servers.txt (new line separated)
#
# PS C:\Temp> Test-OpenPortWF -Target (Get-Content .\Servers.txt) -Port 5985

Mostly it is working but it is not able to give complete results because since we are running this as a parallel workflow, if two servers complete the processing at the same time it will try to write the results to the CSV file at once for both the servers which is resulting in below error. And around 6% results are missing in the CSV file:

Microsoft.PowerShell.Utility\Write-Error : The process cannot access the file 'C:\Temp\Results.csv' because it is being used by another process. At Test-OpenPortWF:54 char:54 + + CategoryInfo : NotSpecified: (:) [Write-Error], CmdletInvocationException + FullyQualifiedErrorId : System.Management.Automation.CmdletInvocationException,Microsoft.PowerShell.Commands.WriteErrorCommand + PSComputerName : [localhost]

How can we get around this problem?

you can get around the problem by not doing anything that requires writing to the same file from multiple sources. [grin] ///// i would drop the workflow method and use Invoke-Command with a list of target systems and a scriptblock. that would send the results back to the calling system, gather them into a $Var, and then write that out to the CSV. some of the benefits are [1] parallel operation [as with a workflow], [2] doing the work on each target instead of on the calling system. — Lee_Dailey
– Lee_Dailey, Commented May 25, 2020 at 7:24

BigJoe · Accepted Answer · 2018-08-10 11:45:22Z

1

Because you use parallel processing, there may be conflicts when multiple threads try to output to your csv file (you run into file locks judging by the error)

Instead, try to output to single temporary files (with unique names) and at the end merge those files into one single report (and delete the temp files)

For example add a counter ($x) in the foreach loop that increments with every iteration ( $x++ ) , then output the results to -Path "C:\Temp\Results_$x.csv"

answered Aug 10, 2018 at 11:45

BigJoe

1312 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

SavindraSingh Over a year ago

That will make it difficult to handle, since we have approx 18000 servers. Even if I throttle for 500 (which is too high limit) it will create 36 files. Any better solution ?

Ansgar Wiechers Over a year ago

@SavindraSingh Concurrent access is concurrent. You need to either handle the concurrency yourself or use something that will handle the concurrency for you (e.g. a database).

SavindraSingh Over a year ago

@AnsgarWiechers: I agree what you say. It seems there no other solution to this. I have to accept the fact :-)

Tolga · Accepted Answer · 2020-05-25 04:27:25Z

1

You need to use a mutex to lock the file I/O operation. Modify your Sequence as such:

    Sequence
    {
        $Out = Test-NetConnection -ComputerName $t -Port $Port -WarningAction SilentlyContinue | Select ComputerName,RemoteAddress,RemotePort,@{N="PortTestSucceeded"; E={$_.tcpTestSucceeded}}
        $mutex = New-Object System.Threading.Mutex $false, 'NetConnectionTest'
        $mutex.WaitOne() > $null;
        $Out | Export-Csv -Path C:\Temp\Results.csv -NoTypeInformation -Append 
        $mutex.ReleaseMutex();
    }

answered May 25, 2020 at 4:27

Tolga

3,8101 gold badge32 silver badges22 bronze badges

Collectives™ on Stack Overflow

Issue while exporting output to CSV in PowerShell Parallel Workflow

2 Answers 2

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related