I need a script that will allow me to gather specific level 1 and Level 2 performance counters over a given period and export the information to excel via .csv is this possible using powercli and ESX 3.5 meaning by connecting to the virtual center using connect-viserver and specifying VC I will be able to either pull todays counters yeserdays counters or the past week counters for each ESX host and export to csv in a usable fortmat
Host Name, CPU total, CPU usage avg, Memory total, memory avg, disk avg, netowrk avg
I took the script you included in the other post.
The basic idea is sound but there were a few problems.
This should be some working code
Connect-VIServer "virtualcenter" Get-VMHost | where {$_.PowerState -eq "poweredOn"} | sort -Property Name | Select Name, Location, @{N="NumVM";E={($_ | Get-VM).Count}}, @{N="Cpu.UsageMhz.Average";E={[Math]::Round((($_ | Get-Stat -Stat cpu.usagemhz.average -IntervalMins 30 -MaxSamples 60 | Measure-Object -Property Value -Average).Average),2)}}, @{N="Mem.Usage.Average";E={[Math]::Round((($_ | Get-Stat -Stat mem.usage.average -IntervalMins 30 -MaxSamples (60) | Measure-Object -Property Value -Average).Average),2)}} | ` Export-Csv "C:\VM-stats.csv" -NoTypeInformation -UseCulture
Since there are some square brackets in the code I attached the script to avoid any problems.
I hope this example puts you on the right track. Let me know what other points are still unclear.
Blog: lucd.info Twitter: @LucD22 Co-author PowerCLI Reference
want to say thanks again for the help a few more questions what about the ForEach-Object command do I need that and also still trying to fully undestand the use of the interval in terms averagin and gathering data. If I want the average of a specific counter for the entire day before so for instance if I want the average cpu usage for yesterday would I accomplish that using the intervals and such
In this case you don't need the ForEach-Object cmdlet since the Select-Object cmdlet will take each object, coming through the pipe from Sort-Object cmdlet.
In the example script you posted, you specified the interval length (-IntervalMins) and the number of samples (-MaxSamples). That means there will be an implicit time range for the returned values.
The time range range will start with the most recent 30 minute interval that is available and from there you get 60 values. This means 30 x 60 = 1800 minutes or 30 hours back in time.
If you want the entire day before (midnight till midnight) you can use the trick I described in PowerCLI & vSphere statistics – Part 1 – The basics.
The script would then become something like this
Connect-VIServer "virtualcenter" $todayMidnight = (Get-Date -Hour 0 -Minute 0 -Second 0).AddMinutes(-1) Get-VMHost | where {$_.PowerState -eq "poweredOn"} | sort -Property Name | Select Name, Location, @{N="NumVM";E={($_ | Get-VM).Count}}, @{N="Cpu.UsageMhz.Average";E={[Math]::Round((($_ | Get-Stat -Stat cpu.usagemhz.average -Start $todayMidnight.AddDays(-1) -Finish $todayMidnight | Measure-Object -Property Value -Average).Average),2)}}, @{N="Mem.Usage.Average";E={[Math]::Round((($_ | Get-Stat -Stat mem.usage.average -Start $todayMidnight.AddDays(-1) -Finish $todayMidnight | Measure-Object -Property Value -Average).Average),2)}} | ` Export-Csv "C:\VM-stats.csv" -NoTypeInformation -UseCulture
Since you will fall automatically in Historical Interval 2, due to the specified time range, there is no more need to specify the -IntervalMins parameter. See the same blog entry from above to understand historical intervals and the interval durations.
Blog: lucd.info Twitter: @LucD22 Co-author PowerCLI Reference
Fantastic that is the exact script I wrote late last night after reading your blog several times!!! The only issueI was having is that sometimes it brings back no data or averages to zero for one or all but I noticed I had a
-Finish $todayMidnight.AddDays (-1) in my code and was wondering if that could cause my wierd results when running if like this
also how do I get the vmcount to list zeroes instead of blanks if there are no vm's on the host
thanks again 36 hours ago I would not have ever guessed that I would be really writing and uhderstanding the power of cli and powershell. My goal is to be able to automate this report and batch up to sharepoint site if possible but dont want to put the cart before the horse
It depends what you defined for the -Start parameter.
The problem here is that the Count is not zero (0) when there are no VMs present but the Count returns the $null value.
You can handle this with the code but the script will become a little less legible
Something like this for example
$todayMidnight = (Get-Date -Hour 0 -Minute 0 -Second 0).AddMinutes(-1) Get-VMHost | where {$_.PowerState -eq "poweredOn"} | sort -Property Name | Select Name, Location, @{N="NumVM";E={$t=($_ | Get-VM).Count;if($t -eq $null){0}else{$t}}}, @{N="Cpu.UsageMhz.Average";E={[Math]::Round((($_ | Get-Stat -Stat cpu.usagemhz.average -Start $todayMidnight.AddDays(-1) -Finish $todayMidnight | Measure-Object -Property Value -Average).Average),2)}}, @{N="Mem.Usage.Average";E={[Math]::Round((($_ | Get-Stat -Stat mem.usage.average -Start $todayMidnight.AddDays(-1) -Finish $todayMidnight | Measure-Object -Property Value -Average).Average),2)}} | ` Export-Csv "C:\VM-stats.csv" -NoTypeInformation -UseCulture
The trick is to store the Count result in a temporary variable and the test it with If.
If $null return o else return the Count.
With the ";" you can combine two instructions on one line.
Glad you're sold to PowerCLI and PowerShell.
What you experienced is in my opinion one the strengths of PowerShell; you can start with a few cmdlets and gradually expand your repertoire.
And another strength is that PowerShell code nearly reads like plain English.
Welcome to the club
Blog: lucd.info Twitter: @LucD22 Co-author PowerCLI Reference