I have a script that talks to VMware vCenter then exports information into Excel and creates charts based on specific VM info. I’m having difficulty getting the charts to use specific data for the charts can anybody advise?
Ive attached the excel report output and script (unfinished). The storage info is fine but the cpu chart should reference the data located in columns CV & CW.
In the script comment (# Create CPU Stats Chart) attempt to create the chart.
Any information or assistance is appreciated.
Thanks Mr G
.. The script
cls
Connect-VIServer -Server server.domain -WarningAction:SilentlyContinue
$VM = Get-VM -Name “some vm”
Set date. Todays date minus 30 days for report
$start = (Get-Date).AddDays(-30)
#store Storage stats
$PSGB = [decimal]::round($VM.ProvisionedSpaceGB)
$USGB = [decimal]::round($VM.UsedSpaceGB)
#Store CPU Stats
$CpuAvg = Get-Stat -Entity $VM -Stat cpu.usage.average -Start $start -IntervalMins 7200
#$CpuMhzAvg = Get-Stat -Entity $VM -Stat cpu.usagemhz.average -Start $start -IntervalMins 7200
#Store MEM Stats
$MemAvg = Get-Stat -Entity $VM -Stat mem.usage.average -Start $start -IntervalMins 7200
$MemBal = Get-Stat -Entity $VM -Stat mem.vmmemctl.average -Start $start -IntervalMins 7200
#Store Network Stats
$Net = Get-Stat -Entity $VM -Stat net.usage.average -Start $start -IntervalMins 7200
Create New Excel Object
$xl = New-Object -comobject Excel.Application
Show Excel Workbook . Change to $False to hide
$xl.Visible = $True
$b = $xl.Workbooks.Add()
$c = $b.Worksheets.Item(1)
$c.Name=$VM
Formatting
$c.Cells.Item(1,1).Font.Bold = $true
$c.Cells.Item(1,1).Font.Size = 9
$c.Cells.Item(1,2).Font.Bold = $true
$c.Cells.Item(1,2).Font.Size = 9
$c.Cells.Item(1,3).Font.Bold = $true
$c.Cells.Item(1,3).Font.Size = 9
$c.Cells.Item(1,4).Font.Bold = $true
$c.Cells.Item(1,4).Font.Size = 9
$c.Cells.Item(1,5).Font.Bold = $true
$c.Cells.Item(1,5).Font.Size = 9
$c.Cells.Item(1,6).Font.Bold = $true
$c.Cells.Item(1,6).Font.Size = 9
$c.Cells.Item(1,7).Font.Bold = $true
$c.Cells.Item(1,7).Font.Size = 9
$c.Cells.Item(1,8).Font.Bold = $true
$c.Cells.Item(1,8).Font.Size = 9
More Formatting ColumnWidth
$c.columns.Item(“A:A”).ColumnWidth = 28
$c.Columns.Item(“B:B”).ColumnWidth = 12
$c.Columns.Item(“C:C”).ColumnWidth = 38
$c.Columns.Item(“D:D”).ColumnWidth = 8
$c.Columns.Item(“E:E”).ColumnWidth = 9
$c.Columns.Item(“F:F”).ColumnWidth = 16
$c.Columns.Item(“G:G”).ColumnWidth = 16
$c.Columns.Item(“H:H”).ColumnWidth = 13
Center all text 1st Row
$c.Cells.Item(1, 1).HorizontalAlignment = -4108
$c.Cells.Item(1, 2).HorizontalAlignment = -4108
$c.Cells.Item(1, 3).HorizontalAlignment = -4108
$c.Cells.Item(1, 4).HorizontalAlignment = -4108
$c.Cells.Item(1, 5).HorizontalAlignment = -4108
$c.Cells.Item(1, 6).HorizontalAlignment = -4108
$c.Cells.Item(1, 7).HorizontalAlignment = -4108
$c.Cells.Item(1, 8).HorizontalAlignment = -4108
Center all text 2nd row
$c.Cells.Item(2, 1).HorizontalAlignment = -4108
$c.Cells.Item(2, 2).HorizontalAlignment = -4108
$c.Cells.Item(2, 3).HorizontalAlignment = -4108
$c.Cells.Item(2, 4).HorizontalAlignment = -4108
$c.Cells.Item(2, 5).HorizontalAlignment = -4108
$c.Cells.Item(2, 6).HorizontalAlignment = -4108
$c.Cells.Item(2, 7).HorizontalAlignment = -4108
$c.Cells.Item(2, 8).HorizontalAlignment = -4108
Fill out Excel Sheet
$c.Cells.Item(1,1) = “Virtual Machine Name (vCenter Name)”
$c.Cells.Item(2,1) = $VM.Name
$c.Cells.Item(1,2) = “VM DNS name”
$c.Cells.Item(2,2) = $VM.Guest.HostName
$c.Cells.Item(1,3) = “Guest Operating System”
$c.Cells.Item(2,3) = $VM.Guest.OSFullName
$c.Cells.Item(1,4) = “Cpu Count”
$c.Cells.Item(2,4) = $VM.NumCpu
$c.Cells.Item(1,5) = “Memory GB”
$c.Cells.Item(2,5) = $VM.MemoryGB
$c.Cells.Item(1,6) = “VMware Tools Status”
$c.Cells.Item(2,6) = $VM.ExtensionData.Guest.ToolsStatus.ToString()
$c.Cells.Item(1,7) = “Storage Allocated GB”
$c.Cells.Item(2,7) = $PSGB
$c.Cells.Item(1,8) = “Storage Used GB”
$c.Cells.Item(2,8) = $USGB
Insert CPU stats into Excel ## These are created in columns CV , CW
$c.Cells.Item(1, 100) = “Time”
$c.Cells.Item(1, 101) = “Usage %”
#$c.Cells.Item(1, 102) = “Usage in MHz”
Insert CPU% Average data. Column CW
$cells = $c.Cells
$row=1
$col=100
foreach ($obj in $CpuAvg) {
$row++
$col=101
$cells.item($Row,$col) = [decimal]::round($obj.Value)
}
Insert CPU Time data. Column CV
$cells = $c.Cells
$row=1
$col=100
foreach ($obj in $CpuAvg) {
$row++
$col=100
$cells.item($Row,$col)=$obj.Timestamp
}
Chart info
$xlConditionValues=[Microsoft.Office.Interop.Excel.XLConditionValueTypes]
$xlTheme=[Microsoft.Office.Interop.Excel.XLThemeColor]
$xlChart=[Microsoft.Office.Interop.Excel.XLChartType]
$xlIconSet=[Microsoft.Office.Interop.Excel.XLIconSet]
$xlDirection=[Microsoft.Office.Interop.Excel.XLDirection]
Create VM Storage Chart
$chart1=$c.Shapes.AddChart().Chart
$chart1.chartType=$xlChart::xlBarClustered
$chart1.ChartTitle.Text = “Storage Utilization”
$c.shapes.item(“Chart 1”).top=45
$c.shapes.item(“Chart 1”).left=10
Create CPU Stats Chart
$chart2=$c.Shapes.AddChart().Chart
$chart2.chartType=$xlChart::xl3DLine
$chart2.ChartTitle.Text = “CPU Usage Average”
Set CPU Chart data range
$range2 = $xl.Range(“CV:CW”)
$range2.activate
$chart2.SetSourceData($range2)
$c.shapes.item(“Chart 2”).top=45
$c.shapes.item(“Chart 2”).left=420
Create MEM Stat Chart
$chart3=$c.Shapes.AddChart().Chart
$chart3.chartType=$xlChart::xlBarClustered
$chart3.ChartTitle.Text = “RAM Usage Average & Balloning”
$c.shapes.item(“Chart 3”).top=300
$c.shapes.item(“Chart 3”).left=10
Create Network Average Stat Chart
$chart4=$c.Shapes.AddChart().Chart
$chart4.chartType=$xlChart::xlLine
$chart4.ChartTitle.Text = “Network Usage Average”
$c.shapes.item(“Chart 4”).top=300
$c.shapes.item(“Chart 4”).left=420
Save worksheet to users desktop
#$b.SaveAs “C:\Users$([Environment]::UserName)\Desktop\CR.xlsx”
#$xl.Quit()