Power usage and cpu load of server export to csv

[JeeWee]

I am supporting a Green IT pilot with a lot of big companies in The Netherlands.

We want to investigate impact of power setting on servers to look for baseline and changes when altering BIOS and power settings in vSphere.

Is there anyone here that likes to help out and make a script that takes a measurement every 15 minutes that can run for a week and puts the output into a csv file.

We like to capture 4 things in the output file

• Timestamp
• Hostname
• Total power usage of the host at timestamp
• Total CPU load in % of the host.

Who can help us out here and provide a working example that we can share with the customers so they can alter it to their environment and run it.

Along with easy stept to get the script running and the requirements for it.

Thanks in advance to help and make the world a bit greener.

[T180985]

You could use something like this... Just run it as a scheduled task every 15 minutes

get-vmhost | Select name,

@{N=“Time“;E={$(get-date -f yyyy-MM-dd-hhmm)}},

@{N=“PowerAverage“;E={$_ | Get-stat -stat power.power.average -realtime -MaxSamples 1}},

@{N=“CPUusage“;E={$_ | Get-stat -stat cpu.usage.average -realtime -MaxSamples 1 | Where {$_.Instance -eq ""}}} | Export-csv -path "C:\Temp\CPUPowerUsage.csv" -Append

Im sure there is a more elegant way of doing it though

[LucD]

Can that script run continuously for a week?

Or do you want to schedule something that run every 15 minutes?

If the latter, can it use Task Scheduler on a Windows box for that?

[JeeWee]

Yes it is possible to use windows server in the customer environment to run a scheduled task that is executed every 15 min.

[LucD · Accepted solution]

First you need to save  the actual script.
I assume that is saved in folder C:\Scripts as file collect.ps1.

Don't forget to update the user, password, folder and starttime.

Then you create the scheduled task.

$user = 'domain\user'

$pswd = 'VMware1!'

$folder = 'C:\Scripts'

$script = "$folder\collect.ps1"

$start = Get-Date '14/12/2019 08:00'

$interval = New-TimeSpan -Minutes 15

$duration = New-TimeSpan -Days 7

$psScript = New-ScheduledTaskAction -Execute 'powershell.exe' -Argument "-NonInteractive -NoLogo -NoProfile -File $script" -WorkingDirectory $folder

$trigger = New-ScheduledTaskTrigger -At $start -Once -RepetitionInterval $interval -RepetitionDuration $duration

$psTask = New-ScheduledTask -Action $psScript -Trigger $trigger

Register-ScheduledTask -TaskName 'Perf data collection' -InputObject $psTask -User $user -Password $pswd

Further, the script assumes that this user (domain\user) has created a VICredentialstoreItem for the VCSA.

With something like this

New-VICredentialStoreItem -Host 'vcsa.domain' -User 'user' -Password 'VMware1!'

And finally you save the actual script in that folder under that name (C:\Scripts\collect.ps1)

$vcName = 'vcsa.domain'

$report = '.\stats.csv'

Connect-VIServer -Server $vcName

$stat = 'power.power.average','cpu.usage.average'

Get-VMHost | Sort-Object -Property Name |

Get-Stat -Stat $stat -Realtime -MaxSamples 1 -Instance '' |

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

ForEach-Object -Process {

    New-Object PSObject -Property ([ordered]@{

        VMHost = $_.Name

        Time = $_.Group[0].Timestamp

        PowerUsageWatt = ($_.Group | where{$_.MetricId -eq 'power.power.average'}).Value

        CpuUsagePerc = ($_.Group | where{$_.MetricId -eq 'cpu.usage.average'}).Value

    })

} | Export-Csv -Path .\report.csv -NoTypeInformation -UseCulture -Append