Performance reports..

[VMSavvy]

I've created a script to pull out monthly performance for ESX Hosts and VMs. This works perfectly fine. I have few things that I thought to change and would be better.

Changes that I'm looking for -

1. My esx hosts are like "abcresx12.corp.xx.com". Is it possible to remove the domain suffix? corp.xx.com? and just print the "abcresx12"?

2. Can I get the host name listed with Upper case "ABCRESX12"?

3. Can I add CPU peak metric for the report.

Script is attached and would be great if someone can help!!

Regards,

VMSavvy.

[VMSavvy]

I've sorted out the first two points myself.. atleast looks like..

LucD or anyone else...does this work for removing the domain suffix and printing uppercase?

$vms.VMName = $_.Name.ToUpper() -replace 'corp.xx.com' -or 'xx.local' , ' '

If yes, please help me with the 3 rd point - adding CPU peak utilization metric to the report.

Thank you,

VMSavvy..

[LucD · Accepted solution]

Try something like this

$vcservers = "myvc01" 
$portvc="443"
Add-PSsnapin VMware.VimAutomation.Core
connect-VIServer $vcservers -port $portvc
$ErrorActionPreference = "SilentlyContinue" 

$prevmonth = (get-date).AddMonths(-1).ToString("Y")

function statavg ($vmImpl, $StatStart, $StatFinish, $statId) {
   $stats = $vmImpl | get-stat -Stat $statId -intervalmin 1440 -Maxsamples 360 `
                         -Start $StatStart -Finish $StatFinish
    New-Object PSObject -Property @{
       Average = "{0,9:#.00}" -f ($stats | Measure-Object value -average).average
       Maximum = "{0,9:#.00}" -f ($stats | Measure-Object value -Maximum).maximum
   }
}

$DaysBack = 30 # Number of days to go back
 $DaysPeriod = 30 # Number of days in the interval
$MonthStart = (Get-Date).Date.adddays(- $DaysBack)
$MonthFinish = (Get-Date).Date.adddays(- $DaysBack + $DaysPeriod).addminutes(-1)


$Report = @()

Get-VMHost | ForEach-Object {
    $host = Get-View $_.ID
    $statCpu = statavg $_ $MonthStart $MonthFinish "cpu.usage.average"
    $statMem = statavg $_ $MonthStart $MonthFinish "mem.usage.average"
    $hosts = "" | Select-Object HostName, "Cluster Name", "DataCenter Name", MonthAvgCpuUsage, MonthMaxCpuUsage, MonthAvgMemUsage
    $hosts.HostName = ($_.Name.Split('.')[0]).ToUpper()
    $hosts."Cluster Name" = (Get-Cluster -VMHost $_).Name
    $hosts."DataCenter Name" = (Get-Datacenter -VMHost $_).Name
    $hosts.MonthAvgCpuUsage = $statCpu.Average
    $hosts.MonthMaxCpuUsage = $statCpu.Maximum
    $hosts.MonthAvgMemUsage = $statMem.Average
    $Report += $hosts
}
$Report | Export-Csv ("C:\VirtualMaintenance\Monthly\HostStats\"+ "ESXStats"+"-"+ $prevmonth +".csv") -NoTypeInformation
$VMReport = @()

Get-VM | ForEach-Object {
    $vm = Get-View $_.ID
    $statCpu = statavg $_ $MonthStart $MonthFinish "cpu.usage.average"
    $statMem = statavg $_ $MonthStart $MonthFinish "mem.usage.average"
    $vms = "" | Select-Object VMName, "Cluster Name", "DataCenter Name", CPU, MonthAvgCpuUsage, MonthMaxCpuUsage, Memory, MonthAvgMemUsage
    $vms.VMName = $_.Name
    $vms."Cluster Name" = (Get-Cluster -VM $_).Name
    $vms."DataCenter Name" = (Get-Datacenter -VM $_).Name
    $vms.CPU = $_.NumCPU
    $vms.MonthAvgCpuUsage = $statCpu.Average
    $vms.MonthMaxCpuUsage = $statCpu.Maximum
    $vms.Memory = $_.MemoryMB
    $vms.MonthAvgMemUsage = $statMem.Average
    $VMReport += $vms
}
$VMReport | Export-Csv ("C:\VirtualMaintenance\Monthly\VMStats\"+ "VMStats"+"-"+ $prevmonth +".csv") -NoTypeInformation
Disconnect-VIServer * -Confirm:$false

[VMSavvy]

That was just perfect!! Thanks Luc.

One more query please...

Can I combine both these reports into one CSV. Its ok to have Hosts listed first and then VMs list in just one sheet. I'll have similar columns for both of them.

Thanks in advance!!

VMSavvy..

[LucD]

You can combine them in 1 CSV file but then all the columns need to be exactly the same.

Perhaps a better solution is to have 2 worksheets in 1 XLS file.

Have a look at my Beyond Export-Csv: Export-Xls post.

[VMSavvy]

Hi Luc,

Can you pls let me know how I can merge the 2 CSV files into one. I have similar column names. I've tried this. It creates  afile but with no data in it. Something wrong I did in this?

$csvFiles = Get-ChildItem ("C:\VirtualMaintenance\Monthly\Stats\Temp\") -Filter *.csv
$content = @()

foreach($csv in $csvFiles)
{
    $content += Import-Csv $csv
}

$content | Export-CSV -Path ("C:\VirtualMaintenance\Monthly\Stats\"+ "Performance Metrics"+"-"+ $prevmonth +".csv") -NoTypeInformation

Thanks in advance!!

VMSavvy

[LucD]

You can try it like this, but the resulting CSV file will only make sense if the columns of all he CSV files you merge are identical.

$csvFiles = Get-ChildItem ("C:\VirtualMaintenance\Monthly\Stats\Temp\") -Filter *.csv
$content = @()

foreach($csv in $csvFiles)
{
    $content += Import-Csv $csv.FullName -UseCulture
}

$content | Export-CSV -Path ("C:\VirtualMaintenance\Monthly\Stats\"+ "Performance Metrics"+"-"+ $prevmonth +".csv") -NoTypeInformation -UseCulture

[VMSavvy]

Thanks for the tip Luc.

I was able to somehow get it work by adding "+" to the line  $content += @()..

Thanks again..

VMSavvy