webCommander Tip #2

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.