Link to home
Start Free TrialLog in
Avatar of contactsam
contactsam

asked on

Powershell script to extract contents of html attachment to be send as email

Hi,

We are trying to generate Oracle tablespace usage report, save it in common folder as html, and then email to selected recipients from the contents of html attachments.
There are 3 parts to this script.

a) Windows Batch file to invoke sql script : This works fine along with correct html file generated from the Oracle sql plus
b) The saved attachment generated from sql server, DB_Report_Tablespace.html is appended with date time stamp using below power shell script. The original saved file attachment has size of 8 kb, but after appneding with date time stamp it comes to size of 1 kb with no data. Script is attached below
c) Email is sent out correctly to recipients but body of email returns blank.
Can you please advise if any tweak can be done to the below ps script?
#>
$date = Get-Date
$d = $date.day
$m = $date.month
$y = $date.year
Outputreport | out-file .\Reports\DB_Report_Tablespace_$d-$m-$y.html
##Invoke-Expression .\Reports\Server_Healthcheck_Report_$d-$m-$y.html
##Send email functionality from below line, use it if you want  
$smtpServer = "Smtp.xxx.yyy.zzzz"
$smtpFrom = "dba.Oracle.prod@zzzzzz"
$smtpTo = "tom@ccc.ddd.yyy"
$messageSubject = "Tablespace Uage report"
$message = New-Object System.Net.Mail.MailMessage $smtpfrom, $smtpto
$message.Subject = $messageSubject
$message.IsBodyHTML = $true
$message.Body = @"
Attached is the Tablespace Usage Report for the $datestring please check and review issues.
"@
$message.Body += Get-Content .\Reports\DB_Report_Tablespace_$datestring.html
$message.Body += "<head><pre>$style</pre></head>"
$smtp = New-Object Net.Mail.SmtpClient($smtpServer)
$smtp.Send($message)
Avatar of oBdA
oBdA
Since PS 2.0, you can use the Send-MailMessage cmdlet.
Try it like this:
$timeStamp = Get-Date -Format 'yyyy-MM-dd'
$reportPath = ".\Reports\DB_Report_Tablespace_$($timeStamp).html"

$sendMailParams = @{
	From = 'dba.Oracle.prod@acme.com'
	To = 'tom@acme.com'
	Subject = 'Tablespace Uage report'
	SmtpServer = 'smtp.acme.com'
	Attachments = $reportPath
	BodyAsHtml = $true
}

$body = @"
<head>
	<title>Tablespace Uage report</title>
	<pre>$($style)</pre>
</head>
<body>
Attached is the Tablespace Usage Report for the $datestring please check and review issues.<br />
<br />
$(Get-Content -Path $reportPath)
</body>
"@

Outputreport | Set-Content -Path $reportPath
# Invoke-Item $reportPath
Send-MailMessage @sendMailParams -Body $body

Open in new window

Avatar of contactsam
contactsam

ASKER

Hi,
Thank you for the update, The file DB_Report_Tablespace_2019-02-14.html does not exists, however the original file with name DB_Report_Tablespace_2019-02-14.html is present in that directory location.

After running the script it returned with below error message

PS D:\software\scripts> D:\software\scripts\tbsmail.ps1
Get-Content : Cannot find path 'D:\software\scripts\Reports\DB_Report_Tablespace_2019-02-14.html' because it does not exist.
At D:\software\scripts\tbsmail.ps1:19 char:3
+ $(Get-Content -Path $reportPath)
+   ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo          : ObjectNotFound: (D:\software\scr...2019-02-14.html:String) [Get-Content], ItemNotFoundException
    + FullyQualifiedErrorId : PathNotFound,Microsoft.PowerShell.Commands.GetContentCommand
 
Outputreport : The term 'Outputreport' is not recognized as the name of a cmdlet, function, script file, or operable program. Check the spelling of the name, or if a path was included,
verify that the path is correct and try again.
At D:\software\scripts\tbsmail.ps1:22 char:1
+ Outputreport | Set-Content -Path $reportPath
+ ~~~~~~~~~~~~
    + CategoryInfo          : ObjectNotFound: (Outputreport:String) [], CommandNotFoundException
    + FullyQualifiedErrorId : CommandNotFoundException
 
Send-MailMessage : The specified string is not in the form required for an e-mail address.
At D:\software\scripts\tbsmail.ps1:24 char:1
+ Send-MailMessage @sendMailParams -Body $body
+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo          : InvalidType: (Smtp.xxx.aaa.cccc:String) [Send-MailMessage], FormatException
    + FullyQualifiedErrorId : FormatException,Microsoft.PowerShell.Commands.SendMailMessage
Avatar of oBdA
oBdA
The line that generates the report slipped into the wrong position (line 25 above).
But you need to fix your output report generation (line 25 above, 13 below).
The "Outputreport" invocation was taken verbatim from your original script.
$timeStamp = Get-Date -Format 'yyyy-MM-dd'
$reportPath = ".\Reports\DB_Report_Tablespace_$($timeStamp).html"
$reportPath = ".\DB_Report_Tablespace_$($timeStamp).html"

$sendMailParams = @{
	From = 'dba.Oracle.prod@acme.com'
	To = 'tom@acme.com'
	Subject = 'Tablespace Uage report'
	SmtpServer = 'smtp.acme.com'
	Attachments = $reportPath
	BodyAsHtml = $true
}
Outputreport | Set-Content -Path $reportPath

$body = @"
<head>
	<title>Tablespace Uage report</title>
	<pre>$($style)</pre>
</head>
<body>
Attached is the Tablespace Usage Report for the $datestring please check and review issues.<br />
<br />
$(Get-Content -Path $reportPath)
</body>
"@

# Invoke-Item $reportPath
Send-MailMessage @sendMailParams -Body $body

Open in new window

Avatar of contactsam
contactsam

ASKER

Apologize for the inconvenience.
Can you please clarify the statement"But you need to fix your output report generation (line 25 above, 13 below)."

Based on the revised changes it still ran into errors with the following message
PS D:\software\scripts> D:\software\scripts\tbsmail.ps1
Outputreport : The term 'Outputreport' is not recognized as the name of a cmdlet, function, script file, or operable program. Check the spelling of the name, or if a path was included,
verify that the path is correct and try again.
At D:\software\scripts\tbsmail.ps1:13 char:1
+ Outputreport | Set-Content -Path $reportPath
+ ~~~~~~~~~~~~
    + CategoryInfo          : ObjectNotFound: (Outputreport:String) [], CommandNotFoundException
    + FullyQualifiedErrorId : CommandNotFoundException
 
Get-Content : Cannot find path 'D:\software\scripts\DB_Report_Tablespace_2019-02-14.html' because it does not exist.
At D:\software\scripts\tbsmail.ps1:23 char:3
+ $(Get-Content -Path $reportPath)
+   ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo          : ObjectNotFound: (D:\software\scr...2019-02-14.html:String) [Get-Content], ItemNotFoundException
    + FullyQualifiedErrorId : PathNotFound,Microsoft.PowerShell.Commands.GetContentCommand
 
Send-MailMessage : The specified string is not in the form required for an e-mail address.
At D:\software\scripts\tbsmail.ps1:28 char:1
+ Send-MailMessage @sendMailParams -Body $body
Avatar of oBdA
oBdA
The line that is throwing the first error is based on your original script line (except I replace the Out-File with Set-Content):
Outputreport | out-file .\Reports\DB_Report_Tablespace_$d-$m-$y.html
You gave no specific information about how the tablespace HTML report is generated, so I'm assuming that "Outputreport" is some function/program/script/whatever that does this.
If not, you either need to fix the output generation yourself and make sure it's written to the path defined in $reportPath, or you need to provide more information about how the HTML content is generated.
The second error is a result from the first one - there's no report, so obviously Get-Content will fail.
The third error (Send-MailMessage) is because you didn't specify a proper address in either the To or From arguments defined in lines 6/7.
Avatar of contactsam
contactsam

ASKER

Thank you,

The file DB_Report_Tablespace.html is generated through sql program and that file exists in the folder D:\software\scripts\reports.

Now I find there are 2 specific issues after running the script

In the D:\software\scripts\reports, there are 2 files generated one with
DB_Report_Tablespace.html 8 Kb
DB_Report_Tablespace_--.html (This is new file generated with 1 kb that is absolutely blank)

Lastly as you mentioned in your previous update
"You gave no specific information about how the tablespace HTML report is generated, so I'm assuming that "Outputreport" is some function/program/script/whatever that does this."
As mentioned output report is already generated and present in that folder.
Is there anything else that needs to be fixed from my end.


Outputreport : The term 'Outputreport' is not recognized as the name of a cmdlet, function, script file, or operable program. Check the spelling of the name, or if a path was
included, verify that the path is correct and try again.
At D:\software\scripts\tbsmail.ps1:13 char:1
+ Outputreport | out-file .\Reports\DB_Report_Tablespace_$d-$m-$y.html
+ ~~~~~~~~~~~~
    + CategoryInfo          : ObjectNotFound: (Outputreport:String) [], CommandNotFoundException
    + FullyQualifiedErrorId : CommandNotFoundException

Send-MailMessage : The specified string is not in the form required for an e-mail address.
At D:\software\scripts\tbsmail.ps1:28 char:1
+ Send-MailMessage @sendMailParams -Body $body
+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo          : InvalidType: (Smtp.dot.gov.abudhabi:String) [Send-MailMessage], FormatException
    + FullyQualifiedErrorId : FormatException,Microsoft.PowerShell.Commands.SendMailMessage
Avatar of oBdA
oBdA
Then try this:
$reportPath = 'D:\software\scripts\reports\DB_Report_Tablespace.html'
$timeStamp = Get-Date -Format 'yyyy-MM-dd'
$attachPath = "${ENV:Temp}\DB_Report_Tablespace_$($timeStamp).html"

$sendMailParams = @{
	From = 'dba.Oracle.prod@acme.com'
	To = 'tom@acme.com'
	Subject = 'Tablespace Uage report'
	SmtpServer = 'smtp.acme.com'
	Attachments = $attachPath
	BodyAsHtml = $true
}

$body = @"
<head>
	<title>Tablespace Uage report</title>
	<pre>$($style)</pre>
</head>
<body>
Attached is the Tablespace Usage Report for the $datestring please check and review issues.<br />
<br />
$(Get-Content -Path $reportPath)
</body>
"@

Copy-Item -Path $reportPath -Destination $attachPath
Invoke-Item $reportPath
Send-MailMessage @sendMailParams -Body $body
Remove-Item -Path $attachPath

Open in new window

Avatar of contactsam
contactsam

ASKER

Thank you, solution shared worked fine to perfection
Before we close this request, we would like to retain copy of html file with today's date in that folder as 'D:\software\scripts\reports\DB_Report_Tablespace_140219.html
Any thoughts on the same is highly appreciated.
ASKER CERTIFIED SOLUTION
Avatar of oBdA
oBdA
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of contactsam
contactsam

ASKER

Thank you, The revised script worked fine. You may close the request
Avatar of oBdA
oBdA
You need to close your question by accepting the comment(s) that helped you; see here:
How do I close my question?
http://support.experts-exchange.com/customer/en/portal/articles/2527982-how-do-i-close-my-question-