Pass local variable within a remote session

This topic contains 2 replies, has 2 voices, and was last updated by Profile photo of Cyril Muffat Cyril Muffat 1 year, 9 months ago.

  • Author
    Posts
  • #30634
    Profile photo of Karthik Durairajan
    Karthik Durairajan
    Participant

    Hi,

    I have created a PS script which creates a remote session, runs a query, generates a CSV and converts it into a HTML body and sends email to whoever runs the job via Jenkins (String parameter). It all works beautifully if I hardcode the value but I am unable to set the value for 'To' within a remote session. Below is the code. please see what i have done wrong, thanks.
    `

    try
    {
    $ToEmail = $env:email
    $uname='domain\user'
    $ComputerName = "server1.domain.com"
    $saveServer = "server2.domain.com"
    $pwd='pass' | ConvertTo-SecureString -Force -AsPlainText
    $query = "
    USE [reporting]
    SELECT
    name,
    path,
    ID,
    ExeID1,
    type,
    [Format],
    [Params],
    TimeStart,
    TimeEnd,
    TimeDate,
    TimePro,
    TimeRend,
    ByteCount,
    [RowCount],
    SID
    FROM log
    where TimeEnd  > dateadd(minute, -3000, GetDate())
    ORDER BY TimeStart"
    $creds=New-object System.Management.Automation.PSCredential ($uname, $pwd)
    $session = New-PSSession -Name 'sess' -ComputerName $ComputerName -Credential $creds -ErrorAction SilentlyContinue
    $session
    Import-PSSession -Session $session -DisableNameChecking -Prefix RS -ErrorAction SilentlyContinue | Out-Null
    Invoke-Command -Session $session -ScriptBlock{param($Arg1,$Arg2,$Arg3);
    $html  = @"
    SSRS - Report
    
    BODY{background-color:#DBDBE9;color:#010153;font-family:Arial,sans-serif;font-size:15px;}
    TABLE {color: #333;font-family: Helvetica, Arial, sans-serif;width: 640px;border-collapse:collapse; border-spacing: 0;text-align: center;horizontal-align:top}
    TD, TH {border: 1px solid #CCC; height: 30px;}
    TH {background: #F3F3F3;font-weight: bold;}
    TD {background: #FAFAFA;text-align: center;}
    tr:nth-child(even) td {border-color: black;background: #F1F1F1;}  
    tr:nth-child(odd) td {border-color: black;background: #FEFEFE;} 
    tr td:hover {background: #666;color: #FFF;}
    
    "@
    $path = "\\$Arg2\C$\temp\"
    $file = "exec-$(get-date -Format yyyy-MM-dd-hh-mm).csv"
    $outfile = Join-Path $path $file
    $database = "ReportServerTempDB";$SQLInstance = "hostname\DB";`
    $connectionString ="server=$SQLInstance;trusted_connection=true;";`
    $connection = New-Object System.Data.SqlClient.SqlConnection;$connection.ConnectionString = $connectionString;`
    $connection.Open();$command = $connection.CreateCommand();$command.CommandText = $Arg1;`
    $result = $command.ExecuteReader();$table = new-object “System.Data.DataTable”;`
    $table.Load($result);$table | Export-Csv -Path $outfile;Write-Host "";`
    Write-Host -f green "complete: " $file;`
    Write-Host -f green "email is: " $ToEmail;`
    $EmailBody = Get-Content $outfile | convertFrom-Csv | convertTo-HTML -AS Table `
    -Head $html -PreContent "Execution History Report Generator" | Format-Table `
    -Wrap -AutoSize | Out-String -Width 4096; Send-MailMessage -To $ToEmail -From "noreply@test.domain.com" `
    -Subject "Output - $((Get-Date -Format "dd-MM-yyyy"))" `
    -Body $EmailBody -Attachment $outfile -SmtpServer 'stmp.domain.com' -BodyAsHtml} -ArgumentList $query,$saveServer,$ToEmail
    Write-Host "Email sent to:" $ToEmail
    Remove-PSSession -ComputerName "computer.domain.com" | where {$_.Name -eq 'sess'}
    }
    catch
    {
    Write-Host -f red "An error has occurred!"
    Write-Host "$($error[0].exception)"
    Write-Host -f red "Failed."
    $error.clear()
    exit $LASTEXITCODE
    }
    

    `

  • #30635
    Profile photo of Karthik Durairajan
    Karthik Durairajan
    Participant

    I guess I just need a little handholding on how to get the value of $ToEmail within the session 'sess'. The script works beautifully otherwise. Thanks.

  • #30640
    Profile photo of Cyril Muffat
    Cyril Muffat
    Participant
    -Wrap -AutoSize | Out-String -Width 4096; Send-MailMessage -To $ToEmail -From "noreply@test.domain.com" `

    i think it should be :

    -Wrap -AutoSize | Out-String -Width 4096; Send-MailMessage -To $Arg3 -From "noreply@test.domain.com" `

You must be logged in to reply to this topic.