VMware Cloud Community
Hetfield84
Enthusiast
Enthusiast
Jump to solution

Script to count host build numbers

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

pastedImage_1.png

If I could spit out counts like in the table below, I'd be able to create a Pie Chart like this:

pastedImage_4.png

  

ESXi BuildCount
6.0.0-460094410
6.0.0-50505933
6.0.0-52249345
6.0.0-55726564
6.0.0-67650622
6.0.0-69213843
6.0.0-750463764

Thoughts?

1 Solution

Accepted Solutions
LucD
Leadership
Leadership
Jump to solution

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

View solution in original post

Reply
0 Kudos
10 Replies
jrodsguitar
Enthusiast
Enthusiast
Jump to solution

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
Blog: https://powershell.house/
Reply
0 Kudos
LucD
Leadership
Leadership
Jump to solution

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

Reply
0 Kudos
jrodsguitar
Enthusiast
Enthusiast
Jump to solution

LucD Off Topic. How do you post syntax highlighted PowerShell code? Sorry I'm very new to this forum. Smiley Happy

Blog: https://powershell.house/
Reply
0 Kudos
LucD
Leadership
Leadership
Jump to solution

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

jrodsguitar
Enthusiast
Enthusiast
Jump to solution

Thank you so much! I only use ISE so I had to install a different editor but PowerShell Plus works.

Blog: https://powershell.house/
Reply
0 Kudos
LucD
Leadership
Leadership
Jump to solution

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

Reply
0 Kudos
Hetfield84
Enthusiast
Enthusiast
Jump to solution

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'

Reply
0 Kudos
LucD
Leadership
Leadership
Jump to solution

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

chart.jpg


Blog: lucd.info  Twitter: @LucD22  Co-author PowerCLI Reference

Hetfield84
Enthusiast
Enthusiast
Jump to solution

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! Smiley Happy

Reply
0 Kudos
bernz
Enthusiast
Enthusiast
Jump to solution

Cool, how do we convert this to function and export it to html report.
Reply
0 Kudos