Hi,
Our environment has a mixture of hosts on different build numbers. I'm trying to write a script that will show a count of how many hosts are running each build number. That way I can export it to Excel and make a pie graph to show the spread of versions.
Using the ImportExcel module, I can create a pie graph but it counts every single result as a new value to graph
This code "PieChart -title "Test" (Get-VMHost | Select Version, Build)" generates a graph like below
If I could spit out counts like in the table below, I'd be able to create a Pie Chart like this:
ESXi Build | Count |
6.0.0-4600944 | 10 |
6.0.0-5050593 | 3 |
6.0.0-5224934 | 5 |
6.0.0-5572656 | 4 |
6.0.0-6765062 | 2 |
6.0.0-6921384 | 3 |
6.0.0-7504637 | 64 |
Thoughts?
And with a somewhat simpler expression
Get-VMHost |
Group-Object -Property Version,Build |
select @{N="ESXi Build";E={$_.Name.Replace(', ','-')}},Count
Blog: lucd.info Twitter: @LucD22 Co-author PowerCLI Reference
This should give you what you want:
Get-VMHost |Group-Object -Property build,version |
select @{Label="ESXi Build";Expression={ $_.name |
foreach {($_ -split ',')[1] + '-' + ($_ -split ',')[0]} }}, count
And with a somewhat simpler expression
Get-VMHost |
Group-Object -Property Version,Build |
select @{N="ESXi Build";E={$_.Name.Replace(', ','-')}},Count
Blog: lucd.info Twitter: @LucD22 Co-author PowerCLI Reference
LucD Off Topic. How do you post syntax highlighted PowerShell code? Sorry I'm very new to this forum.
In short, you copy HTML code from your editor to the VMTN editor (switched to HTML).
I did a doc on that, see Some ways to enter PowerCLI code under the new forum SW
Blog: lucd.info Twitter: @LucD22 Co-author PowerCLI Reference
Thank you so much! I only use ISE so I had to install a different editor but PowerShell Plus works.
You can also do it from ISE with the Windows PowerShell V3 ISE: Copy As HTML Add-On
Also works in the ISE of PS v5.*
Blog: lucd.info Twitter: @LucD22 Co-author PowerCLI Reference
Thanks LucD and jrodsguitar you guys rock. This worked, also thanks for the tips to paste syntax correctly in the forums.
Are either of you familiar with how to stop the PieChart cmdLet (Import-Excel) from opening a new Excel document each time and instead adding it to a spreadsheet on a specified worksheet?
I tried the below but it just keeps popping up with a new Excel document window, and creating a blank Excel document called chart.xlsx with a worksheet named Graph.
PieChart -title "ESXi Version and Build" -showpercent (Get-VMHost | Group-Object -Property Version,Build | select @{N="ESXi Build";E={$_.Name.Replace(', ',' - ')}},Count) | Export-Excel "$PSScriptRoot\chart.xlsx" -WorksheetName 'Graph'
I normally insert the chart on the worksheet.
Something like this
$filePath = 'c:\Temp\pie.xlsx'
$esxVersion = Get-VMHost | Group-Object -Property Version,Build |
select @{N="ESXi Build";E={$_.Name.Replace(', ',' - ')}},Count
$chart = New-ExcelChart -Title 'Versions' `
-ChartType Pie3D -Header "Versions" `
-XRange "Versions[ESXi Build]" `
-YRange "Versions[Count]" `
-Width 400 -Height 400
$esxVersion |
Export-Excel -Path $filePath -WorkSheetname 'ESXi Versions' -TableName 'Versions' -FreezeTopRow -AutoSize -ExcelChartDefinition $chart
That produces something like this
Blog: lucd.info Twitter: @LucD22 Co-author PowerCLI Reference
Hi,
Thanks LucD. You are a genius, this worked flawlessly and I was able to add an additional worksheet which lists each host and their patch level to the document.
This has made me very happy!