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 }
`