CODEWITHSUNDEEP
excelpowershell
Ross
Ross

Reputation: 2417

Struggling to export PowerShell job information to excel

I have a PowerShell script that is running a web request as part of a job. With the script block that is run by the job I am logging the Endpoint Uri and the response time. and then after all the jobs are finished but before I remove the jobs I am trying to export the result into excel.

PS Verion:

Major  Minor  Build  Revision
-----  -----  -----  --------
5      1      17763  503

Code:

$Code = {
    Param(
        [array]$Domain,
        [array]$Services,
        [array]$TestData,
        [string]$Path
    )

    $Log = @()

    ## Get Random School ##
    $Random = Get-Random -InputObject $TestData -Count 1

    ## Get Random Service ##
    $RandService = Get-Random -InputObject $Services

    ## Get Random Endpoint ## 
    $ServiceEndpoints = Get-Content "$Path\Service.Endpoints.json" | Out-String | ConvertFrom-Json
    $GatewayEndpoints = $ServiceEndpoints.Services.$RandService.Gateway
    $RandomEndpoint = Get-Random -InputObject $GatewayEndpoints

    $Headers = @{
        "Authorization" = "Bearer" + ' ' + $Random.Token
    }

    $Uri = 'https://' + $Domain + $RandomEndpoint

    Try {
        $TimeTaken = Measure-Command -Expression {
            $JsonResponse = Invoke-WebRequest -Uri $Uri -Headers $Headers -ContentType 'application/json' -Method Get -UseBasicParsing
        }
    }
    Catch {
    }

    $ResponseTime = [Math]::Round($TimeTaken.TotalMilliseconds, 1) 

    $LogItem = New-Object PSObject
    $LogItem | Add-Member -type NoteProperty -Name 'Endpoint' -Value $Uri
    $LogItem | Add-Member -type NoteProperty -Name 'Time' -Value $ResponseTime
    $Log += $LogItem 

    Write-Host $Log
}

#Remove all jobs
Get-Job | Remove-Job

#Start the jobs. Max 4 jobs running simultaneously.
foreach($Row in $TestData){
    While ($(Get-Job -state running).count -ge $MaxThreads){
        Start-Sleep -Milliseconds 3
    }
    Start-Job -Scriptblock $Code -ArgumentList $Domain, $Services, $TestData, $Path
}

#Wait for all jobs to finish.
While ($(Get-Job -State Running).count -gt 0) {
    start-sleep 1
}

$Log | Export-XLSX -Path .\Test.Results\Performance\Performance.Test.Log.xlsx -ClearSheet

#Get information from each job.
foreach($Job in Get-Job) {
    $Info = Receive-Job -Id ($Job.Id)
}

#Remove all jobs created.
Get-Job | Remove-Job

I cannot seem to get the endpoint uri and the response time out of the script block. When I try to export the $Log, all that happens is it creates an empty excel file.

Write-Host $Log

@{Endpoint=https://domain/customer/v1/years/2019/marks; Time=1233.3}
@{Endpoint=https://domain/customer/v1/years/2019/marks; Time=2131.7}

Upvotes: 1

Views: 334

Answers (3)

Moerwald
Moerwald

Reputation: 11304

You've to return $Log in your script block, since $Log lives in another scope. You can return $Log in your $Code script block, and finally, fetch it via Receive-Job.

Change your code to:

$Code = {
    ...
    $Log += $LogItem 

    Write-Host $Log
    $Log # return via pipeline to the caller
}

As Niraj Gajjar's answer suggests you can use Export-Csv cmdlet to create an Excel file:

#Get information from each job.
foreach($Job in Get-Job) {
    Receive-Job -Id ($Job.Id) | Export-CSV -Path .\Test.Results\Performance\Performance.Test.Log.xlsx -Append -NoTypeInformation
}

Upvotes: 1

Nirav Mistry
Nirav Mistry

Reputation: 989

You can use Export-Csv to open file in excel.

sample code with multiple jobs to CSV format : 

$jobs = @() # INITILIZING ARRAY
$jobs += Start-Job { appwiz.cpl } # START JOB 1 AND ADDING TO ARRAY
$jobs += Start-Job { compmgmt.msc } # START JOB 2 AND ADDING TO ARRAY
$jobs += Start-Job { notepad.exe } # START JOB 3 AND ADDING TO ARRAY

foreach ( $job in $jobs) # INTERATION OF JOBS
{
    Export-Csv -InputObject $job "C:\result.csv" -Append # SAVING TO FILE
}

enter image description here

Note : There are some internal properties of job which are not converted by CSV because depth is 1 but some basic properties are available.

Upvotes: 0

montonero
montonero

Reputation: 1761

Global variables inside of jobs aren't visible in the main script since job is running in a new session with its own global space.

Upvotes: 1

Related Questions