This entry is part 2 of webCommander Tip blog series and those who have missed the first one, check it out here webCommander Tip #1
Introduction
By default, webCommander could output a result in a table format on a browser. What if the user wants to send the result to him/herself or to another via email to keep it? It could be achieved by adding/modifying few lines and functions.
Pre-requisites
Before going through the post, the users must meet the pre-requisites below:
- An email user that will be sending an email out, i.e. email sender
- SMTP server
Configuration
Send-Mail function
The below attached is the function called Send-Mail that has 4 arguments:
- $To => The user who wants to receive the result.
- $Subject => The subject of the email.
- $Body => The body of the email, this will be for the result.
- $Attachement => If the user wants to keep the result in .csv format, it can be sent as an attachment. For our case, this is by default even the $Attachments parameter is set to Mandatory=$false.
It uses a native PowerShell Send-MailMessage function (more details can be found by Get-Help Send-MailMessage) and written in the objects.ps1 file that can be loaded by any PowerCLI functions:
Function Send-Mail
{
param(
[Parameter(Mandatory=$true)]
[string[]]$To,
[string]$Subject,
[string]$Body,
[Parameter(Mandatory=$false)]
[string]$Attachments
)
$mail_sender = "sender@test.com"
$mail_smtp_server = "mailhost.test.com"
if ($Attachments)
{
Send-MailMessage -from $mail_sender -to $To -subject $Subject -BodyAsHTML -Body $Body -smtpServer $mail_smtp_server -Attachments $Attachments
}
else
{
Send-MailMessage -from $mail_sender -to $To -subject $Subject -BodyAsHTML -Body $Body -smtpServer $mail_smtp_server
}
}
webCmd.xml
Obviously, an input box (parameter) has to be provided to the users. I created an entity called Email that is optional:
<!ENTITY Email ' <parameter optional="1" name="EmailTo" description="Email address to send the report" /> '>
Now this can be added to any commands i.e. PowerCLI scripts, an example below:
<command name="Query-Powered-Off-VM" description="[vSphere] Query powered off virtual machines"> <script>Query-Powered-Off-VM</script> <parameters> &ConnectvCenter; &Cluster; &Email; </parameters> </command>
Actual Script
By now, the input parameter is created and Send-Mail function is added to objects.ps1. Let’s go through how to use them on the actual scripts.
The following is an example script that queries datastores under several conditions ($expression is the regular expression that I used for the filtering):
$output = foreach ($cluster in (Get-Cluster -Name $Cluster -Server $server.viserver -wa 0 -EA stop -ErrorVariable error_message)) {
$cluster | Get-VMHost | Get-Datastore | Where-Object {$_.Name -match $expression -and $_.FreespaceGB -ge $Freespace} | Sort Name | Select @{N="Cluster";E={$cluster.Name}}, Name, FreespaceGB, CapacityGB }
Once the report is saved to $output variable, it can be sent via email by converting the output into HTML as a string format:
$output_html = $output | ConvertTo-Html | Out-String Send-Mail -To $EmailTo -Subject "Query-Datastore Report" -Body $output_html
On your email, it will look like this:
| Cluster | Name | FreeSpaceGB | CapacityGB |
|---|---|---|---|
| Cluster1 | Datastore1 | 559.3359375 | 1023.75 |
| Cluster1 | Datastore2 | 525.53125 | 1023.75 |
| Cluster1 | Datastore3 | 394.1640625 | 1023.75 |
| Cluster1 | Datastore4 | 344.9453125 | 1023.75 |
| Cluster1 | Datastore5 | 236.609375 | 511.75 |
Now, let’s go through how to send the result as an attachment. One thing to consider is that when the output is saved as a .csv file on C: drive somewhere, depends on the amount of data the user queries, the size of the file might be big which will eventually fill up the drive. So, when attaching a file I decided to:
- Export the output as a .csv file
- Send an email with the attachment
- Delete the file
This way, the administrator doesn’t have to worry about filling up the drive 🙂 The script is attached below:
$output | Export-CSV "C:\script\output\Query-Datastore.csv" -NoTypeInformation -UseCulture $output_html = $output | ConvertTo-Html | Out-String Send-Mail -To $EmailTo -Subject "Query-Datastore Report" -Body $output_html -Attachments "C:\script\output\Query-Datastore.csv" Remove-Item -Path "C:\script\output\Query-Datastore.csv"
Summary
Not only outputting the result on a web browser, it can also be sent to the users via email. This way, people could save the report and utilise it using Excel…etc.
On the next series, I will be going through how to upload a .csv file to make changes to virtual machines.
Steven Kang 