Almero
Enthusiast
Enthusiast

Defeated by Sort-Object in Hash Table

Jump to solution

Hi Guys , I need somebody that is smarter than me to help please , suck for 3 days now .

The Script below uses a CSV as a database to pull total VMs in my vcenter into a Bar chart , showing growth trend . . My boss wants this asap .

I cannot however get the variable $HT to be sorted by date ( sort and sort-object does seem not work , sorting NAME or  WEEK )

Please help , I am stumped .

Note , some stuff cut off when I pasted this  , like }'s and #'s .

# Created by ELMO

 

Add-pssnapin VMware.VimAutomation.Core -ErrorAction SilentlyContinue

$Date = Get-Date -Format yyyy/MM/dd

$VCRUN = "My Blanked out vCenter"

$Report = @()

$USER = "My Blanked out user"

$PWD = "My Blanked out pwd"

Write-Host "Connecting to $VCRUN"

Disconnect-VIserver -Confirm:$false

Connect-VIServer -Server $VCRUN -User $USER -Password $PWD -wa 0

$VMsON = Get-VM | Where-Object {$_.PowerState -eq "PoweredOn"}

$VMsTotal = $VMsON.count

 

   $Row = "" | Select Week, VMsTotal

   $Row.Week = $Date

   $Row.VMsTotal = $VMsTotal

   $Report += $Row

   $ReportView = $Report | Export-Csv -Path C:\PS\Output\VMTrendDB.csv -Append -NoTypeInformation # -Append puts data at bottom of existing file

$MyData = Import-Csv C:\PS\Output\VMTrendDB.csv | Select-Object Week ,VMsTotal

 

$HT = @{}

foreach ($Data in $MyData) {

$HT.Add($Data.Week,$Data.VMsTotal)

$HT = $HT | Sort # Read on Technet that Sort-object does not change the actual array , but only the output . So used only sort . Triedd $HT = HT | Sort=Object Name AND Sort Ojbect Week , they dont change my Array on order of dates

 

Function Create-Chart() {

 

Param(

  [String]$ChartType,

[String]$ChartTitle,

  [String]$FileName,

[String]$XAxisName,

  [String]$YAxisName,

[Int]$ChartWidth,

[Int]$ChartHeight,

[HashTable]$DataHashTable

[void][Reflection.Assembly]::LoadWithPartialName("System.Windows.Forms")

[void][Reflection.Assembly]::LoadWithPartialName("System.Windows.Forms.DataVisualization")

 

#Create our chart object

$Chart = New-object System.Windows.Forms.DataVisualization.Charting.Chart

$Chart.Width = $ChartWidth

$Chart.Height = $ChartHeight

$Chart.Left = 10

$Chart.Top = 10

#Create a chartarea to draw on and add this to the chart

$ChartArea = New-Object System.Windows.Forms.DataVisualization.Charting.ChartArea

$Chart.ChartAreas.Add($ChartArea)

[void]$Chart.Series.Add("Data")

 

$Chart.ChartAreas[0].AxisX.Interval = "1" #Set this to 1 (default is auto) and allows all X Axis values to display correctly

$Chart.ChartAreas[0].AxisX.IsLabelAutoFit = $false;

$Chart.ChartAreas[0].AxisX.LabelStyle.Angle = "-45"

#Add the Actual Data to our Chart

$Chart.Series["Data"].Points.DataBindXY($DataHashTable.Keys, $DataHashTable.Values)

 

if (($ChartType -eq "Pie") -or ($ChartType -eq "pie")) {

$ChartArea.AxisX.Title = $XAxisName

$ChartArea.AxisY.Title = $YAxisName

$Chart.Series["Data"].ChartType = [System.Windows.Forms.DataVisualization.Charting.SeriesChartType]::Pie

$Chart.Series["Data"]["PieLabelStyle"] = "Outside"

$Chart.Series["Data"]["PieLineColor"] = "Black"

$Chart.Series["Data"]["PieDrawingStyle"] = "Concave"

($Chart.Series["Data"].Points.FindMaxByValue())["Exploded"] = $true

$Chart.Series["Data"].Label = "#VALX = #VALY\n" # Give an X & Y Label to the data in the plot area (useful for Pie graph) (Display both axis labels, use: Y = #VALY\nX = #VALX)

elseif (($ChartType -eq "Bar") -or ($ChartType -eq "bar")) {

#$Chart.Series["Data"].Sort([System.Windows.Forms.DataVisualization.Charting.PointSortOrder]::Descending, "Y")

$ChartArea.AxisX.Title = $XAxisName

$ChartArea.AxisY.Title = $YAxisName

# Find point with max/min values and change their colour

$maxValuePoint = $Chart.Series["Data"].Points.FindMaxByValue()

$maxValuePoint.Color = [System.Drawing.Color]::Red

$minValuePoint = $Chart.Series["Data"].Points.FindMinByValue()

$minValuePoint.Color = [System.Drawing.Color]::Green

# make bars into 3d cylinders

$Chart.Series["Data"]["DrawingStyle"] = "Cylinder"

$Chart.Series["Data"].Label = "#VALY" # Give a Y Label to the data in the plot area (useful for Bar graph)

else {

Write-Host "No Chart Type was defined. Try again and enter either Pie or Bar for the ChartType Parameter. The chart will be created as a standard Bar Graph Chart for now." -ForegroundColor Cyan

 

#Set the title of the Chart to the current date and time

$Title = new-object System.Windows.Forms.DataVisualization.Charting.Title

$Chart.Titles.Add($Title)

$Chart.Titles[0].Text = $ChartTitle

#Save the chart to a file

$FullPath = ($FileName + ".png")

$Chart.SaveImage($FullPath,"png")

Write-Host "Chart saved to $FullPath" -ForegroundColor Green

return $FullPath

 

Create-Chart -ChartType bar -ChartTitle "VMware VMs in DC1" -FileName C:\inetpub\ELMO\Graphic\VMsTrendChart -XAxisName "Date" -YAxisName "Number of VMs" -ChartWidth 800 -ChartHeight 800 -DataHashTable $HT

0 Kudos
1 Solution

Accepted Solutions
sajal1
VMware Employee
VMware Employee

While defining the variable $HT, you are defining it as a Hash Table, Sort or Sort-Object does not work on Hash Tables, they work on Arrays. Your definition is

$HT = @{}


Use $HT = @()

Instead which will create an array and then you would be able to define the objects and use Sort-Object there. With Hash Tables, since they do not hold Objects you can not sort them using Sort-Object.

View solution in original post

6 Replies
sajal1
VMware Employee
VMware Employee

While defining the variable $HT, you are defining it as a Hash Table, Sort or Sort-Object does not work on Hash Tables, they work on Arrays. Your definition is

$HT = @{}


Use $HT = @()

Instead which will create an array and then you would be able to define the objects and use Sort-Object there. With Hash Tables, since they do not hold Objects you can not sort them using Sort-Object.

Almero
Enthusiast
Enthusiast

AWESOME MAN , will go test asap .

Amazing how something as simple a bracket type can make you waste 3 days .:smileysilly:

Almero
Enthusiast
Enthusiast

Ok , I think you put me on the right path . The information is however not being passed to $HT any more , will try and figure out .

Do you know of a mechanism to sort hash tables ? If not , I will have to rework some stuff and use arrays .

0 Kudos
Almero
Enthusiast
Enthusiast

Turns out you can sort Hash Tables by using this >

$HT.GetEnumerator() | Sort -Property Name

Sadly , when the bar Chart is made , it still uses its own sorting .

This can be manipulated by this , but still don't know how to do it on the Data/X axis .

$Chart.Series["Data"].Sort([System.Windows.Forms.DataVisualization.Charting.PointSortOrder]::Descending, "Y")

Thank you anyway , you still helped a lot

InNeedOfSupport
Contributor
Contributor

HI, Did you every solved this ? Also struggeling with this

0 Kudos
InNeedOfSupport
Contributor
Contributor

Found it 

you need to use "AxisLabel". Found it by puting in wrong input. Error message give me the following:

Exception calling "Sort" with "2" argument(s): "DataPointComparer - Invalid data point sorting value specified. Use a value such as  'X', 'Y', 'Y2', 'Y3', 'AxisLabel' ...

 

This is what worked for me:

$Chart.Series['Series1'].Sort([System.Windows.Forms.DataVisualization.Charting.PointSortOrder]::Ascending, "AxisLabel")
0 Kudos