Write-Output inside Invoke-Command scriptblock

Question

Below is a script I'm working on to get all SQL-jobs into a CSV-file. The script itself is working great but I have trouble with the error-handling. I can't figure out how to get the Out-File inside the Catch-block to print to the file on my local machine instead of the remote machine I'm running the Invoke-Command to. How do I accomplish this?

Thanks

PS. The script is written out fully as much as possible for non experienced co-workers convenience

$sqlServers = @("TEST1","TEST2")

$filePath = [Environment]::GetFolderPath("Desktop")

$dateToday = Get-Date -Format “yyMMdd HH:mm"
$dateTodayFile = Get-Date -Format “yyMMdd"

Write-Output "$dateToday $sqlServers" |
    Out-File "$filePath\Log$dateTodayFile.txt" -Append

$output = Invoke-Command -ComputerName $sqlServers -ScriptBlock{

    Try
    {
        Import-Module sqlserver -ErrorAction Stop
    }
    Catch
    {
        Write-Output "$dateToday ERROR $env:computername" |
            Out-File "$filePath\Log$dateTodayFile.txt" -Append
        Exit
    }
    $instances = $env:computername | Foreach-Object {Get-ChildItem -Path "SQLSERVER:\SQL\$_"}

    ForEach ($instance in $instances){

        Try
        {
        $instanceName = $instance.InstanceName
        Get-SqlAgentJob -ServerInstance "$env:computername\$instanceName" -ErrorAction Stop |
            Where-Object {$_.IsEnabled -eq "True" -and $_.LastRunDate -gt [DateTime]::Today.AddDays(-2) -and $_.OwnerLoginName -match "TEST"} |
                Select-Object @{Name=‘Job name‘;Expression={$_.Name}},
                    @{Name=‘Description‘;Expression={$_.Description}},
                    @{Name=‘Instance‘;Expression={$_.Parent -Replace '[][]'}},
                    @{Name=‘Run outcome‘;Expression={$_.LastRunOutcome}},
                    @{Name=‘Run date‘;Expression={$_.LastRunDate}},
                    @{Name=‘Run duration‘;Expression={$_.LastRunDuration}},
                    @{Name=‘Job creator‘;Expression={$_.OwnerLoginName}},
                    @{Name=‘Runs on a schedule‘;Expression={$_.HasSchedule}},
                    @{Name='Schedule Type';Expression={$_.JobSchedules -join ','}}
        }
        Catch
        {
            Write-Output "$dateToday ERROR $env:computername\$instanceName" |
                Out-File "$filePath\Log$dateTodayFile.txt" -Append
            Exit
        }
    }
}
$output | Select-Object -Property * -ExcludeProperty PSComputerName,RunSpaceID,PSShowComputerName |
    Sort-Object "Job name" |
        Export-Csv $filePath\SQLJobInvent$dateTodayFile.csv -NoTypeInformation -Delimiter ";" -Encoding UTF8

Write-Output "$dateToday $filePath" |
    Out-File "$filePath\Log$dateTodayFile.txt" -Append
Write-Output "----------------------------------------" |
    Out-File "$filePath\Log$dateTodayFile.txt" -Append

Try moving your try-catch so that it wraps your entire ForEach ($instance...) block instead? — LordPupazz
– LordPupazz, Commented Jun 2, 2020 at 15:11
you are not passing the $Vars into your scriptblock ... so you appear to have a scope problem. the usual simple solutions is to use $Using: for the $vars that you want to pass into the scriptblock. [grin] — Lee_Dailey
– Lee_Dailey, Commented Jun 2, 2020 at 15:52

retryW · Accepted Answer · 2020-06-02 23:48:25Z

2

Your primary issue is scope.

The $dateToday, $filePath and $dateTodayFile are all declared on the local machine, but you're trying to use them on the remote computer (script block) where they are undefined.

There are a few ways to get your variables passed to the remote computer, below are two:

# Add desired variable to ArgumentList and define it as a parameter
Invoke-Command -ComputerName $sqlServers -ArgumentList $dateToday,$filePath,$dateTodayFile -ScriptBlock {
  param(
    $folderPath,
    $filePath,
    $dateTodayFile
  )  
  # Do something with our injected variables
  Write-Output "$dateToday ERROR $env:computername" |
    Out-File "$filePath\Log$dateTodayFile.txt" -Append
}

OR

# In PS ver >= 3.0 we can use 'using'
Invoke-Command -ComputerName $serverName -ScriptBlock {Write-Output $using:dateToday}

answered Jun 2, 2020 at 23:48

retryW

6953 silver badges8 bronze badges

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

2 Comments

Salve Over a year ago

Thank you, lifesaver! Is there any performance differences between the two? I'm going to run it on a couple of houndred SQL-servers

retryW Over a year ago

Glad to have helped! I'm not sure about the performance benefits between the two sorry.

Collectives™ on Stack Overflow

Write-Output inside Invoke-Command scriptblock

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related