Author Posts

October 11, 2015 at 5:54 pm

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
}

`

October 11, 2015 at 5:56 pm

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.

October 11, 2015 at 9:45 pm

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