VMware Cloud Community
Gr33nEye
Enthusiast
Enthusiast
Jump to solution

VM Stats Export Issues.

Hi Guys, I am trying to export some metrics for VMs however when the export goes to Excel it only exports a single line. As in the latest metric and nothing else. The idea is to have all the metrics in different columns on the same Excel Tab. So for VM = X, it has CPU, memory, Disk, Nic ect all on the same tab just under different columns.

I've tried to export to CSv and all i get is the System.Object[] in each field, and again only the single line of data.

Any ideas would be appreciated.

$vm= "VM

$Xls = @()

foreach ($vmz in $vm) {

$Export = "" | Select-Object "Mem MetricId","Mem Timestamp","Mem Value","Mem Unit","CPU U MetricId","CPU U Timestamp","CPU U Value","CPU U Unit","CPU R MetricId","CPU R Timestamp","CPU R Value","CPU R Unit","Disk U MetricId","Disk U Timestamp","Disk U Value","Disk U Unit","Disk L MetricId","Disk L Timestamp","Disk L Value","Disk L Unit","Net MetricId","Net Timestamp","Net Value","Net Unit"

$memU = Get-Stat -Entity $vmz -Stat mem.usage.average -Start (Get-Date).AddDays(-20) -IntervalMins 30 -Instance  ""

$cpUU = Get-Stat -Entity $vmz -Stat cpu.usage.average -Start (Get-Date).AddDays(-20) -IntervalMins 30 -Instance  "" 

$cpUR = Get-Stat -Entity $vmz -Stat cpu.ready.summation  -Start (Get-Date).AddDays(-20) -IntervalMins 30 -Instance  "" 

$DiskU = Get-Stat -Entity $vmz -Stat disk.usage.average -Start (Get-Date).AddDays(-20) -IntervalMins 30 

$DisL = Get-Stat -Entity $vmz -Stat disk.maxTotalLatency.latest -Start (Get-Date).AddDays(-20) -IntervalMins 30 

$net = Get-Stat -Entity $vmz -Stat net.usage.average  -Start (Get-Date).AddDays(-20) -IntervalMins 30 

$Export."Mem MetricId" =  $memU.MetricId

$Export."Mem Timestamp" = $memU.Timestamp

$Export."Mem Value" = $memU.Value

$Export."Mem Unit" =  $memU.Unit

$Export."CPU U MetricId" =  $cpUU.MetricId

$Export."CPU U Timestamp" = $CpUU.Timestamp

$Export."CPU U Value" = $CpUU.Value

$Export."CPU U Unit" =  $CpUU.Unit

$Export."CPU R MetricId" =  $cpUR.MetricId

$Export."CPU R Timestamp"= $cpUR.Timestamp

$Export."CPU R Value" = $cpUR.Value

$Export."CPU R Unit"=  $cpUR.Unit

$Export."Disk U MetricId" =  $DiskU.MetricId

$Export."Disk U Timestamp" = $DiskU.Timestamp

$Export."Disk U Value" = $DiskU.Value

$Export."Disk U Unit" =  $DiskU.Unit

$Export."Disk L MetricId" =  $DisL.MetricId

$Export."Disk L Timestamp" = $DisL.Timestamp

$Export."Disk L Value" = $DisL.Value

$Export."Disk L Unit" =  $DisL.Unit

$Export."Net MetricId" =  $net.MetricId

$Export."Net Timestamp" = $net.Timestamp

$Export."Net Value" = $net.Value

$Export."Net Unit" =  $net.Unit

$xls += $Export

$xls | Export-Excel -Path C:\Data\Tst-xpt.xlsx -WorkSheetname $vmz

}

*Edit. Fixed typo in some var. Still broken tho.

Reply
0 Kudos
1 Solution

Accepted Solutions
LucD
Leadership
Leadership
Jump to solution

Try like this (uses a different table name for each VM)
Use the SeriesHeader parameter to have the text in the legend changed.

$stats = "cpu.ready.summation","cpu.usage.average", "mem.usage.average","disk.usage.average","disk.maxTotalLatency.latest","net.usage.average" 

$vm= Get-VM -Name (Import-Csv -Path .\vmnames.csv -UseCulture | Select -ExpandProperty Name)

Get-Stat -Entity $vm -Stat $stats -Start (Get-Date).AddDays(-20) -IntervalMins 30 -Instance  ""  |

Group-Object -Property {$_.Entity.Name} |

ForEach-Object -Process {

    $sChart = @{

        Title = "$($_.Name) CPU"

        ChartType = 'Line'

        XRange = "$($_.Name)VMStats[TimeStamp]"

        YRange = @("$($_.Name)VMStats[cpu.ready.summation]","$($_.Name)VMStats[cpu.usage.average]")

        SeriesHeader = @("cpu.ready.summation","cpu.usage.average")

    }

    $chartMem = New-ExcelChart @sChart

    $sExcel = @{

        Path = 'C:\Data\tst-Tst-xpt.xlsx'

        WorksheetName = "$($_.Name)Stats"

        TableName = "$($_.Name)VMStats"

        AutoSize = $true

        ExcelChartDefinition = $chartMem

    }

    $obj1 = [hashtable]::new()

    $obj1.Add('VM',$_.Name)

    $_.Group | Sort-Object -Property Timestamp | Group-Object -Property Timestamp |

    ForEach-Object -Process {

        $obj2 = $obj1.Clone()

        $obj2.Add('Timestamp',$_.Name)

        $_.Group | Group-Object -Property MetricId |

        ForEach-Object -Process {

            $obj2.Add($_.Name,$_.Group[0].Value)

        }

        $sSelect = @{

            Property = 'VM','Timestamp' + $stats

        }

        New-Object -TypeName PSObject -Property $obj2 | Select-Object @sSelect

    } |

    Export-Excel @sExcel

}


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

View solution in original post

Reply
0 Kudos
16 Replies
LucD
Leadership
Leadership
Jump to solution

You are exporting inside the ForEach loop, meaning you will overwrite the Excel file in each loop.

And you will end up with only the last VM in he file.

I assumed that the $vm variable at the beginning would contain a number of VM objects.

Note that you can do this in one Get-Stat call with the all the metrics in that one call.

Which would make the script faster.

$vm= Get-VM -Name 'vm1','vm2','vm3'

$Xls = @() 

foreach ($vmz in $vm) { 

    $Export = "" | Select-Object "Mem MetricId","Mem Timestamp","Mem Value","Mem Unit","CPU U MetricId",

        "CPU U Timestamp","CPU U Value","CPU U Unit","CPU R MetricId","CPU R Timestamp","CPU R Value",

        "CPU R Unit","Disk U MetricId","Disk U Timestamp","Disk U Value","Disk U Unit","Disk L MetricId",

        "Disk L Timestamp","Disk L Value","Disk L Unit","Net MetricId","Net Timestamp","Net Value","Net Unit" 

 

    $memU = Get-Stat -Entity $vmz -Stat mem.usage.average -Start (Get-Date).AddDays(-20) -IntervalMins 30 -Instance  ""  

    $cpUU = Get-Stat -Entity $vmz -Stat cpu.usage.average -Start (Get-Date).AddDays(-20) -IntervalMins 30 -Instance  ""   

    $cpUR = Get-Stat -Entity $vmz -Stat cpu.ready.summation  -Start (Get-Date).AddDays(-20) -IntervalMins 30 -Instance  ""   

    $DiskU = Get-Stat -Entity $vmz -Stat disk.usage.average -Start (Get-Date).AddDays(-20) -IntervalMins 30   

    $DisL = Get-Stat -Entity $vmz -Stat disk.maxTotalLatency.latest -Start (Get-Date).AddDays(-20) -IntervalMins 30   

    $net = Get-Stat -Entity $vmz -Stat net.usage.average  -Start (Get-Date).AddDays(-20) -IntervalMins 30   

 

 

    $Export."Mem MetricId" =  $memU.MetricId 

    $Export."Mem Timestamp" = $memU.Timestamp 

    $Export."Mem Value" = $memU.Value 

    $Export."Mem Unit" =  $memU.Unit 

 

 

    $Export."CPU U MetricId" =  $cpUU.MetricId 

    $Export."CPU U Timestamp" = $CpUU.Timestamp 

    $Export."CPU U Value" = $CpUU.Value 

    $Export."CPU U Unit" =  $CpUU.Unit 

 

 

    $Export."CPU R MetricId" =  $cpUR.MetricId 

    $Export."CPU R Timestamp"= $cpUR.Timestamp 

    $Export."CPU R Value" = $cpUR.Value 

    $Export."CPU R Unit"=  $cpUR.Unit 

 

 

    $Export."Disk U MetricId" =  $DiskU.MetricId 

    $Export."Disk U Timestamp" = $DiskU.Timestamp 

    $Export."Disk U Value" = $DiskU.Value 

    $Export."Disk U Unit" =  $DiskU.Unit 

 

 

    $Export."Disk L MetricId" =  $DisL.MetricId 

    $Export."Disk L Timestamp" = $DisL.Timestamp 

    $Export."Disk L Value" = $DisL.Value 

    $Export."Disk L Unit" =  $DisL.Unit 

 

 

    $Export."Net MetricId" =  $net.MetricId 

    $Export."Net Timestamp" = $net.Timestamp 

    $Export."Net Value" = $net.Value 

    $Export."Net Unit" =  $net.Unit 

    $xls += $Export 

}

$xls | Export-Excel -Path C:\Data\Tst-xpt.xlsx -WorkSheetname $vmz 

  


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

Reply
0 Kudos
Gr33nEye
Enthusiast
Enthusiast
Jump to solution

Hi LucD,

Yep, the $Vm = import from CVS.

If i put the export after the loop i only get the one tab, with all the different VMs info on it. I do need the tabs per VM to be separate. I am trying to get historical data for a bunch of VMs and then to get it auto create a chart on export.

Similar to

VMware PowerCLI Forum - VMware {code} .

I did move the export after the loop with 2 vms selected and all it created were 2x lines in excel.

Reply
0 Kudos
LucD
Leadership
Leadership
Jump to solution

You mean like this?

# Expected layout CSV

# Name

# vm1

# vm2

#

$vm= Import-Csv -Path .\vmnames.csv -UseCulture | Select -ExpandProperty Name

foreach ($vmz in Get-VM -Name $vm) { 

    $Export = "" | Select-Object "Mem MetricId","Mem Timestamp","Mem Value","Mem Unit","CPU U MetricId","CPU U Timestamp","CPU U Value","CPU U Unit","CPU R MetricId","CPU R Timestamp","CPU R Value","CPU R Unit","Disk U MetricId","Disk U Timestamp","Disk U Value","Disk U Unit","Disk L MetricId","Disk L Timestamp","Disk L Value","Disk L Unit","Net MetricId","Net Timestamp","Net Value","Net Unit" 

     

    $memU = Get-Stat -Entity $vmz -Stat mem.usage.average -Start (Get-Date).AddDays(-20) -IntervalMins 30 -Instance  ""  

    $cpUU = Get-Stat -Entity $vmz -Stat cpu.usage.average -Start (Get-Date).AddDays(-20) -IntervalMins 30 -Instance  ""   

    $cpUR = Get-Stat -Entity $vmz -Stat cpu.ready.summation  -Start (Get-Date).AddDays(-20) -IntervalMins 30 -Instance  ""   

    $DiskU = Get-Stat -Entity $vmz -Stat disk.usage.average -Start (Get-Date).AddDays(-20) -IntervalMins 30   

    $DisL = Get-Stat -Entity $vmz -Stat disk.maxTotalLatency.latest -Start (Get-Date).AddDays(-20) -IntervalMins 30   

    $net = Get-Stat -Entity $vmz -Stat net.usage.average  -Start (Get-Date).AddDays(-20) -IntervalMins 30   

     

     

    $Export."Mem MetricId" =  $memU.MetricId 

    $Export."Mem Timestamp" = $memU.Timestamp 

    $Export."Mem Value" = $memU.Value 

    $Export."Mem Unit" =  $memU.Unit 

     

     

    $Export."CPU U MetricId" =  $cpUU.MetricId 

    $Export."CPU U Timestamp" = $CpUU.Timestamp 

    $Export."CPU U Value" = $CpUU.Value 

    $Export."CPU U Unit" =  $CpUU.Unit 

     

     

    $Export."CPU R MetricId" =  $cpUR.MetricId 

    $Export."CPU R Timestamp"= $cpUR.Timestamp 

    $Export."CPU R Value" = $cpUR.Value 

    $Export."CPU R Unit"=  $cpUR.Unit 

     

     

    $Export."Disk U MetricId" =  $DiskU.MetricId 

    $Export."Disk U Timestamp" = $DiskU.Timestamp 

    $Export."Disk U Value" = $DiskU.Value 

    $Export."Disk U Unit" =  $DiskU.Unit 

     

     

    $Export."Disk L MetricId" =  $DisL.MetricId 

    $Export."Disk L Timestamp" = $DisL.Timestamp 

    $Export."Disk L Value" = $DisL.Value 

    $Export."Disk L Unit" =  $DisL.Unit 

     

     

    $Export."Net MetricId" =  $net.MetricId 

    $Export."Net Timestamp" = $net.Timestamp 

    $Export."Net Value" = $net.Value 

    $Export."Net Unit" =  $net.Unit 

     

    $Export |

    Export-Excel -Path C:\Data\Tst-xpt.xlsx -WorkSheetname "$($vmz.Name)" -AutoSize

}


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

Reply
0 Kudos
Gr33nEye
Enthusiast
Enthusiast
Jump to solution

For some reason it's not combining the values when they are all been rolled up into the the $Export or the $Xls.

If i do a straight export form the get-stat it exports all the data.It seems when I try to roll it up into the $Export and rename the fields, it only remembers the 1 line.

So i combined all the stats into one Get-Stat but the problem now is that If I would like to create the Charts, i need the values and metrics in different Columns. This just exports them all in one row. As in MetricId in A, Values in B, ect,,

$stats = "cpu.ready.summation","cpu.usage.average", "mem.usage.average","disk.usage.average","disk.maxTotalLatency.latest","net.usage.average"

$memU = Get-Stat -Entity $vmz -Stat $stats -Start (Get-Date).AddDays(-20) -IntervalMins 30 -Instance  ""  | Select MetricId,Timestamp,Value,Unit

$memU | Export-Excel C:\Data\tst-Tst-xpt.xlsx -WorkSheetname  $vmz+"Mem" -AutoSize

Reply
0 Kudos
LucD
Leadership
Leadership
Jump to solution

Try like this

$stats = "cpu.ready.summation","cpu.usage.average", "mem.usage.average","disk.usage.average","disk.maxTotalLatency.latest","net.usage.average" 

$vm= Get-VM -Name (Import-Csv -Path .\vmnames.csv -UseCulture | Select -ExpandProperty Name)

Get-Stat -Entity $vm -Stat $stats -Start (Get-Date).AddDays(-20) -IntervalMins 30 -Instance  ""  |

Group-Object -Property {$_.Entity.Name} |

ForEach-Object -Process {

    $obj1 = [hashtable]::new()

    $obj1.Add('VM',$_.Name)

    $_.Group | Sort-Object -Property Timestamp | Group-Object -Property Timestamp |

    ForEach-Object -Process {

        $obj2 = $obj1.Clone()

        $obj2.Add('Timestamp',$_.Name)

        $_.Group | Group-Object -Property MetricId |

        ForEach-Object -Process {

            $obj2.Add($_.Name,$_.Group[0].Value)

        }

        $sSelect = @{

            Property = 'VM','Timestamp' + $stats

        }

        New-Object -TypeName PSObject -Property $obj2 | Select-Object @sSelect

    } |

    Export-Excel C:\Data\tst-Tst-xpt.xlsx -WorkSheetname "$($_.Name)Stats" -AutoSize

}


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

Reply
0 Kudos
Gr33nEye
Enthusiast
Enthusiast
Jump to solution

Not sure what is happening in this script but it works, awesome.

Just one question, how do i specify the columns for the -ExcelChartDefinition.

At the moment I'm just selecting the entire column to 9999 but that kinda messes up the charts, and is there a way to set the X Axis as text. from the $chart ?

$chart = New-ExcelChart -Title "$Vm+Mem" -ChartType Line  -NoLegend ` -XRange 'B2:B9999' -YRange 'C2:C9999', 'D2:D9999'

Also, where in the loop do i need to put this in. If I put in in the same section as the hashtable creation it names the Charts with all the VMs names. The others dont seem to populate the $chart value for the export so it doesn't create the chart.

pastedImage_1.png

Reply
0 Kudos
Gr33nEye
Enthusiast
Enthusiast
Jump to solution

Tried

$chart = New-ExcelChart -Title "$_.Name+Mem" -ChartType Line   -XRange 'Timestamp[$B:$B]' -YRange 'cpu.ready.summation[$C:$C]', 'cpu.usage.average[$D:$D]'

To name the legend and limit the selection, didn't name anything and with the [] it does not do the selection.

Reply
0 Kudos
LucD
Leadership
Leadership
Jump to solution

That skewed graph is due to large amount of empty cells in that range.

Try like this (don't mind the TableName warning)
Note that you have to be aware of the scaling of your data. If one of the metric is in a different range, you might see the 2nd one as a flat line

$stats = "cpu.ready.summation","cpu.usage.average", "mem.usage.average","disk.usage.average","disk.maxTotalLatency.latest","net.usage.average" 

$vm= Get-VM -Name (Import-Csv -Path .\vmnames.csv -UseCulture | Select -ExpandProperty Name)

Get-Stat -Entity $vm -Stat $stats -Start (Get-Date).AddDays(-20) -IntervalMins 30 -Instance  ""  |

Group-Object -Property {$_.Entity.Name} |

ForEach-Object -Process {

    $sChart = @{

        Title = "$($_.Name) CPU"

        ChartType = 'Line'

        NoLegend = $true

        XRange = 'VMStats[TimeStamp]'

        YRange = @('VMStats[cpu.ready.summation]','VMStats[cpu.usage.average]')

    }

    $chartMem = New-ExcelChart @sChart

    $sExcel = @{

        Path = 'C:\Data\tst-Tst-xpt.xlsx'

        WorksheetName = "$($_.Name)Stats"

        TableName = 'VMStats'

        AutoSize = $true

        ExcelChartDefinition = $chartMem

    }

    $obj1 = [hashtable]::new()

    $obj1.Add('VM',$_.Name)

    $_.Group | Sort-Object -Property Timestamp | Group-Object -Property Timestamp |

    ForEach-Object -Process {

        $obj2 = $obj1.Clone()

        $obj2.Add('Timestamp',$_.Name)

        $_.Group | Group-Object -Property MetricId |

        ForEach-Object -Process {

            $obj2.Add($_.Name,$_.Group[0].Value)

        }

        $sSelect = @{

            Property = 'VM','Timestamp' + $stats

        }

        New-Object -TypeName PSObject -Property $obj2 | Select-Object @sSelect

    } |

    Export-Excel @sExcel

}


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

Reply
0 Kudos
Gr33nEye
Enthusiast
Enthusiast
Jump to solution

Found a slight issue with that, the second tabs chart. Its not able to create the table as the table 'VMStats' is already used on the previous VMs tab. So the charts for all the successive VMs get there data from the first Vms Table.

Need to set it based on the VMs Name or something unique  "$($_.Name)VMStats". Does not work in there. It create the unique tables just no data on the Chart.

Also is there a way to set the Legend from the Y Range Name?

$sChart = @{

        Title = "$($_.Name)"

        ChartType = 'Line'

        NoLegend = $True

        XRange = '"$($_.Name)VMStats"[TimeStamp]'

        YRange = @('"$($_.Name)VMStats"[cpu.ready.summation]','"$($_.Name)VMStats"[cpu.usage.average]')

    }

   $chartMem = New-ExcelChart @sChart

    $sExcel = @{

        Path = 'C:\Data\tst-Tst-xpt.xlsx'

        WorksheetName = "$($_.Name)Stats"

        TableName = "$($_.Name)VMStats"

        AutoSize = $true

        ExcelChartDefinition = $chartMem

Reply
0 Kudos
LucD
Leadership
Leadership
Jump to solution

Try like this (uses a different table name for each VM)
Use the SeriesHeader parameter to have the text in the legend changed.

$stats = "cpu.ready.summation","cpu.usage.average", "mem.usage.average","disk.usage.average","disk.maxTotalLatency.latest","net.usage.average" 

$vm= Get-VM -Name (Import-Csv -Path .\vmnames.csv -UseCulture | Select -ExpandProperty Name)

Get-Stat -Entity $vm -Stat $stats -Start (Get-Date).AddDays(-20) -IntervalMins 30 -Instance  ""  |

Group-Object -Property {$_.Entity.Name} |

ForEach-Object -Process {

    $sChart = @{

        Title = "$($_.Name) CPU"

        ChartType = 'Line'

        XRange = "$($_.Name)VMStats[TimeStamp]"

        YRange = @("$($_.Name)VMStats[cpu.ready.summation]","$($_.Name)VMStats[cpu.usage.average]")

        SeriesHeader = @("cpu.ready.summation","cpu.usage.average")

    }

    $chartMem = New-ExcelChart @sChart

    $sExcel = @{

        Path = 'C:\Data\tst-Tst-xpt.xlsx'

        WorksheetName = "$($_.Name)Stats"

        TableName = "$($_.Name)VMStats"

        AutoSize = $true

        ExcelChartDefinition = $chartMem

    }

    $obj1 = [hashtable]::new()

    $obj1.Add('VM',$_.Name)

    $_.Group | Sort-Object -Property Timestamp | Group-Object -Property Timestamp |

    ForEach-Object -Process {

        $obj2 = $obj1.Clone()

        $obj2.Add('Timestamp',$_.Name)

        $_.Group | Group-Object -Property MetricId |

        ForEach-Object -Process {

            $obj2.Add($_.Name,$_.Group[0].Value)

        }

        $sSelect = @{

            Property = 'VM','Timestamp' + $stats

        }

        New-Object -TypeName PSObject -Property $obj2 | Select-Object @sSelect

    } |

    Export-Excel @sExcel

}


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

Reply
0 Kudos
Gr33nEye
Enthusiast
Enthusiast
Jump to solution

Awesome, that works perfectly.

Thank you.

Reply
0 Kudos
Gr33nEye
Enthusiast
Enthusiast
Jump to solution

Is it possible to specify the Chart Style number?

pastedImage_0.png

Reply
0 Kudos
LucD
Leadership
Leadership
Jump to solution

Afaik, not in the current release.

Just opened an Issue for that :smileycool:


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

Reply
0 Kudos
LucD
Leadership
Leadership
Jump to solution

If you want to experiment with a development branch of ImportExcel, Doug just implemented a ChartStyle keyword.
See the issue I mentioned earlier.


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

Gr33nEye
Enthusiast
Enthusiast
Jump to solution

Awesome, that looks good. I guess the only thing left would be the secondary Y axis scale.

Reply
0 Kudos
LucD
Leadership
Leadership
Jump to solution

There is already an issue open for that :smileygrin:

See Chart legend with Multiple Axis · Issue #206 · dfinke/ImportExcel · GitHub


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

Reply
0 Kudos