Introduction
- Run PowerCLI script using Windows Task Scheduler everyday 6am and generate a .csv file
- Load .csv generated by PowerCLI script from the web portal
webCommander was released several months ago and decided to replace the whole reports from the web portal into this. However, I found that it is only able to run the actual script to generate an output. One of the problems was that if the user wanted to review the whole virtual machines, it took a several hours to generate the output.
To overcome this, I was looking for a way of outputting a report without running a script and finally got it.
In this post, I will be going through what & how to configure in order to generate a static report on webCommander. I recommend you to read this article in advance as I will be using Google Table for the report.
webCmd.php
One Assumption I made was that the name of the script must start with “Report-.*”. This could be changed to your script naming convention.
In webCmd.php, if the script name starts with Report-, it will load webcommander_static xsl template which will bypass parameter checking. This will be discussed later in this post. The modification I made is underlined and attached below:
if ( $command == “") {
…...
## Static Page Start ##
} elseif ( preg_match ("/^Report\-/", $command) ) {
$xml = simplexml_import_dom($dom);
$query = '/webcommander/command[@name="' . $command . '"]';
$target = $xml->xpath($query);
if (count($target) == 0){
$xmloutput .= "<script>alert('Could not find command \"" . $command . "\"!')</script>";
$xmloutput .= "<script>document.location.href='webcmd.php'</script>";
} else {
$target = $target[0];
header("Content-type:text/xml");
$xmloutput .= '<?xml version="1.0" encoding="utf-8" ?>';
$xmloutput .= '<?xml-stylesheet type="text/xsl" href="webCmd.xsl"?>';
$xmloutput .= '<webcommander_static cmd="' . $command . '" developer="' . $target["developer"] .'">';
$scriptName = (string)$target->script;
$psPath = realpath('../powershell/');
chdir($psPath);
$cmd = "powershell .\\" . $scriptName . ".ps1";
callPs1($cmd);
$xmloutput .= '</webcommander_static>';
}
echo $xmloutput;
## Static Page Ends ##
} else {
……
}
echo $xmloutput;
webCmd.xml
<command name="Report-Cluster" description="[vSphere] Report - Clusters" developer="s.kang">
<script>Report-Cluster</script>
</command>
As stated above, the name of script starts with “Report-.*”. In this case, it’s called “Report-Cluster”.
webCmd.xsl
- webcommander_static
- static
webcommander_static is to replace the original webcommander template. The difference between these two is, as expected, it doesn’t define parameters.Instead of using “result” template to output result, I defined a new one called “static”. The reason is that customisation becomes easier in future, i.e. divide the page into two columns, one for table and the another for chart.
For now, I only put Google Table in:
<!-- Static Page Starts!-->
<xsl:template match="webcommander_static">
<html>
<head>
<title>webCommander</title>
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8" />
<link href="webCmd.css" rel="stylesheet" type="text/css" />
<link rel="stylesheet" href="https://code.jquery.com/ui/1.9.2/themes/base/jquery-ui.css" />
<script src="https://code.jquery.com/jquery-1.8.3.js"></script>
<script src="https://code.jquery.com/ui/1.9.2/jquery-ui.js"></script>
<script src="webCmd.js"></script>
</head>
<body>
<xsl:call-template name="header"/>
<xsl:call-template name="returnCode"/>
<xsl:if test="/webcommander/annotation">
<xsl:call-template name="annotation"/>
</xsl:if>
<div id="content">
<xsl:call-template name="static"/>
</div>
</body>
</html>
</xsl:template>
<!-- Static Page Ends !-->
<!-- Static Result Starts !-->
<xsl:template name="static">
<h2>Result</h2>
<div id="result">
<xsl:if test="result/table">
<head>
<script type='text/javascript' src='https://www.google.com/jsapi'></script>
<script type='text/javascript'>
google.load('visualization', '1', {packages:['table']});
google.setOnLoadCallback(drawTable);
function drawTable() {
var data = new google.visualization.DataTable();
<xsl:value-of select="result/table/listcolumns" />
<xsl:value-of select="result/table/listrows" />
var table = new google.visualization.Table(document.getElementById('table_div'));
table.draw(data, {showRowNumber: true});
}
</script>
</head>
<div id='table_div'></div>
</xsl:if>
</div>
</xsl:template>
<!-- Static Result Ends !-->
PowerCLI Script
- Generate-xxx.ps1
- Report-xxx.ps1
Generate-xxx.ps1 will be ran under Windows Task Scheduler to generate a .csv file daily basis and the result will be exported with today’s date. An example is attached below:
Connect-VIServer -Server “Your vCenter server” -User “Username” -Password “Password"
$attributes = "vCenterServer,Cluster,NumberOfESXi,ActiveVM,AvailableCPUMHz,TotalCPUMHz,AvailableMemoryGB,TotalMemoryGB,Version4.x,Version5.x"
$result = '' | select vCenterServer,Cluster,NumberOfESXi,ActiveVM,AvailableCPUMHz,TotalCPUMHz,AvailableMemoryGB,TotalMemoryGB,"Version4.x","Version5.x"
$result = foreach ($cluster in Get-Cluster | Sort Name) {
$esxi = Get-VMHost -Location $cluster | Sort Name
$total_cpu = ($esxi | %{$_.CpuTotalMHz} | Measure-Object -Sum).Sum
$available_cpu = $total_cpu - (($esxi | %{$_.CPUUsageMHz} | Measure-Object -Sum).Sum)
$total_memory = ($esxi | %{$_.MemoryTotalGB} | Measure-Object -Sum).Sum
$avaialble_memory = $total_memory - (($esxi | %{$_.MemoryUsageGB} | Measure-Object -Sum).Sum)
$count = 0
$esxi | Foreach-Object { if ($_.Version -match "^4.*") { $count++ } }
$count = 0
$esxi | Foreach-Object { if ($_.Version -match "^5.*") { $count++ } }
$result.vCenterServer = $cluster.ExtensionData.Client.ServiceUrl -replace "https://" -replace "/sdk"
$result.Cluster = $cluster.Name
$result.NumberOfESXi = ($cluster.ExtensionData.Host.Value | Measure-Object).Count
$result.ActiveVM = ($esxi | Get-VM | where {$_.PowerState -eq "PoweredOn"} | Measure-Object).Count
$result.TotalCPUMHz = $total_cpu
$result.AvailableCPUMHz = $available_cpu
$result.TotalMemoryGB = "{0:N2}" -f $total_memory
$result.AvailableMemoryGB = "{0:N2}" -f $avaialble_memory
$result."Version4.x" = $count
$result."Version5.x" = $count
$result | select *
}
$today = Get-Date | %{[string]$_.Day + "." + $_.Month + "." + $_.Year}
$filename = "generate-cluster-“ + $today + ".csv"
$result | sort vCenterServer,Cluster | Export-Csv E:\Cluster\$filename
Disconnect-VIServer * -Confirm:$false
Report-Cluster.ps1
Report-xxx.ps1 is where it loads the .csv file and present it on webCommander. An example is attached below:
. .\objects.ps1
$attributes = "vCenterServer,Cluster,Description,NumberOfESXi,ActiveVM,AvailableCPUMHz,TotalCPUMHz,AvailableMemoryGB,TotalMemoryGB,Version4.x,Version5.x"
$today = Get-Date | %{[string]$_.Day + "." + $_.Month + "." + $_.Year}
$filename = "generate-cluster" + $today + ".csv"
$result = Import-CSV E:\Cluster\$filename
Google-Table $attributes $result
Result
Wrap Up
I think this is not the best way of creating a static page. I believe Jerry (@9whirls) could develop it in a better way, maybe in the next release. In future, it will be possible to create few more functions such as download the output as .csv, send it via email…etc
Another work could be done is that instead of exporting/importing .csv file, using a proper database could be a possible option. I used IBM DB2 to store historical data using PowerCLI in the past and I am sure it could be used in this case as well.
Hope you enjoy and ping me for any questions.
Steven Kang 