Introduction
This time, I decided to integrate Google Table with webCommander. It will be very similar to what I’ve already written, Google Chart integration and it can be found here.
Two benefits of using Google Table is:
- Sort functionality is embedded into the table
- Allows easier writing/managing scripts
These will be discussed and explained in more depth through the blog.
Configuration
Let us look at an example of Google Table (link is here) first:
<html> <head> <scripttype='text/javascript'src='https://www.google.com/jsapi'></script> <scripttype='text/javascript'> google.load('visualization','1',{packages:['table']}); google.setOnLoadCallback(drawTable); function drawTable(){ var data =new google.visualization.DataTable(); data.addColumn('string','Name'); data.addColumn('number','Salary'); data.addColumn('boolean','Full Time Employee'); data.addRows([ ['Mike', {v:10000, f:'$10,000'},true], ['Jim', {v:8000, f:'$8,000'}, false], ['Alice',{v:12500, f:'$12,500'},true], ['Bob', {v:7000, f:'$7,000'}, true] ]); var table =new google.visualization.Table(document.getElementById('table_div')); table.draw(data,{showRowNumber:true}); } </script> </head> <body> <divid='table_div'></div> </body> </html>
Take a closer look at the underlined above, which are the structure of how columns and rows should be defined.
Same as last Google Chart integration, I defined a xsl:if instance in webCmd.xsl. I called it <xsl:if test=”result/table”>:
<xsl:if test="result/table"> <html> <head> <scripttype='text/javascript'src='https://www.google.com/jsapi'></script> <scripttype='text/javascript'> google.load('visualization','1',{packages:['table']}); google.setOnLoadCallback(drawTable); function drawTable(){ var data =new google.visualization.DataTable(); data.addColumn('string','Name'); data.addColumn('number','Salary'); data.addColumn('boolean','Full Time Employee'); data.addRows([ ['Mike', {v:10000, f:'$10,000'},true], ['Jim', {v:8000, f:'$8,000'}, false], ['Alice',{v:12500, f:'$12,500'},true], ['Bob', {v:7000, f:'$7,000'}, true] ]); var table =new google.visualization.Table(document.getElementById('table_div')); table.draw(data,{showRowNumber:true}); } </script> </head> <divid='table_div'></div></html> </xsl:if>
It’s time to replace underlined static values into variables. JavaScript and xsl works quite well that it is simple enough to define a JavaScript variable and value to be retrieved using xsl. However, I realised that JavaScript variables were not necessary to be defined. What I mean is:
<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>
It looks much cleaner, isn’t it?
Now, it is time to load columns and rows, i.e. list columns & listrows from PowerCLI. I wrote a simple PowerCLI function called Google-Table. What this function do is that it converts the result into columns & rows format that Google Table recognises. Bear in mind that the result I am referring here is a normal output, for example Get-VMHost | Select Name, NumCPU. This function could be inserted into objects.ps1 file and loaded from your script:
Update: added one more parameter, attributes. The reason was that the columns defined by the user was sorted automatically.
function Google-Table {
Param($attributes,$output)
Write-Output "<table>"
$property = Get-Member -InputObject $output | where {$_.MemberType -eq "NoteProperty"}
$listcolumns = ""
$listrows = "data.addRows(["
# Column
foreach ($a in ($attributes -split ",")) {
$definition = $property | where {$_.Name -eq $a} | %{$_.Definition}
if ($definition -match "System.Int32" -or $definition -match "System.Decimal") {
$listcolumns += "data.addColumn('number', '$a');"
} else {
$listcolumns += "data.addColumn('string', '$a');"
}
}
# Row
foreach ($row in $output) {
$listrows += "["
foreach ($a in ($attributes -split ",")) {
$definition = $property | where {$_.Name -eq $a} | %{$_.Definition}
if ($definition -match "System.Int32" -or $definition -match "System.Decimal") {
$listrows += $row | %{$_.($a)}
} else {
$listrows += "'"
$listrows += $row | %{$_.($a)}
$listrows += "'"
}
$listrows += ","
}
$listrows = $listrows -replace ",$"
$listrows += "],"
}
$listrows = $listrows -replace ",$"
$listrows += "]);"
Write-Output "<listcolumns>$listcolumns</listcolumns>"
Write-Output "<listrows>$listrows</listrows>"
Write-Output "</table>"
}
Time to run script. For the testing, I used NTP-Query script which can be found here:
. .\objects.ps1
$attributes = "Cluster,ESXi,NTPServer,Service,Policy,Running"
$result = '' | select Cluster,ESXi,NTPServer,Service,Policy,Running
$output = foreach ($esxi in (Get-Cluster -Name $cluster | Get-VMHost | Sort Name)) {
$service = Get-VMHostService -VMHost $esxi | Where {$_.Key -eq "ntpd"}
$policy = switch ($service.Policy) {
"off" { "Start and stop manually"}
"on" { "Start and stop with host" }
"automatic" { "Start automatically if any ports are open, and stop when all ports are closed" }
}
$ntp = Get-VMHostNtpServer -VMHost $esxi
$result.Cluster = $esxi.Parent
$result.ESXi = $esxi.Name
$result.NTPServer = $ntp
$result.Service = $service.Label
$result.Policy = $policy
$result.Running = $service.Running
}
Google-Table $attributes,$output
Let me re-cap one of the benefits I outlined above, “Allows easier writing/managing scripts”. Why? Here is an example of what I’ve been using:
. .\objects.ps1
$attributes = "Cluster,ESXi,NTPServer,Service,Policy,Running"
$result = '' | select Cluster,ESXi,NTPServer,Service,Policy,Running
$output = foreach ($esxi in (Get-Cluster -Name $cluster | Get-VMHost | Sort Name)) {
$service = Get-VMHostService -VMHost $esxi | Where {$_.Key -eq "ntpd"}
$policy = switch ($service.Policy) {
"off" { "Start and stop manually"}
"on" { "Start and stop with host" }
"automatic" { "Start automatically if any ports are open, and stop when all ports are closed" }
}
$ntp = Get-VMHostNtpServer -VMHost $esxi
$result.Cluster = $esxi.Parent
$result.ESXi = $esxi.Name
$result.NTPServer = $ntp
$result.Service = $service.Label
$result.Policy = $policy
$result.Running = $service.Running
}
foreach ($information in $output) { $cluster = $Cluster $esxi = $information.Name $ntpserver = $information.NTPServer $service = $information.NTPService $status = $information.Status write-output "<custom>" write-output "<cluster>$cluster</cluster>" write-output "<esxi>$esxi</esxi>" write-output "<ntpserver>$ntpserver</ntpserver>" write-output "<service>$service</service>" write-output "<status>$status</status>" write-output "</custom>" }
Do you see the difference (Bold and Underlined) between two? Instead of parsing xsl values to webCmd.xsl manually, i.e. Write-Output …… a simple Google-Table function could be loaded, no matter how many columns you define.
Everything is in place. Time to run the script via webCommander.
Looks pretty, isn’t it? Make sure you try sorting.
Future
In future, I will be trying to create a dashboard based on Google Chart and Table under a static page. This is going to be a tricky one but hopefully I make it happen.
Hope you guys have fun and always welcome to ping me for any corrections/questions 🙂
Steven Kang 
One thought on “webCommander and Google Table Integration”