Author Posts

October 24, 2014 at 2:21 am

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()

October 30, 2014 at 2:48 pm

You may want to post your question in StackOverflow; I'm seeing a pattern of Excel related questions going unanswered here because everyone is desperately trying to move away from programming Excel. It's awkward, and is a very old COM interface. I've been moving to SSRS, myself. Much nicer, and I can still use PowerShell to populate the data.

November 3, 2014 at 7:27 am

Thanks Don,

Totally agree, trying to program Excel has been a nightmare! I'll look into SSRS.

Many Thanks

Gordon Grant