4 Replies Latest reply on Sep 23, 2020 10:40 AM by LucD

    Host utilization report

    vmk2014 Expert

      Hi , All

       

      I'm trying to retrieve the Host utilization report with PowerCLI script to find out average CPU and Memory hosts utilization during period of time and push the data to BI tool, but it fails with an error for all the hosts. Looking for help.

       

       

      Thanks

       

      Import-Module VMware.VimAutomation.Core

      Connect-VIServer -Server Domain.com -user "Domain\user"

       

       

      $now = Get-Date

      $start = $now.AddDays(-1)

       

       

      $finish = $now

       

       

      $SStat = @{

       

       

         Entity = Get-VMHost

       

       

         Stat = 'cpu.usage.average', 'mem.usage.average'

       

       

         Start = $start

       

       

         Finish = $finish

       

       

         Instance = '*'

       

       

         ErrorAction = 'SilentlyContinue'

       

       

      }

       

       

      Get-Stat @sStat |

       

       

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

       

       

      ForEach-Object -Process {

       

       

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

       

       

         Host = $_.Name

       

       

         Start = $start

       

       

         Finish = $finish

       

       

         CpuAvgPerc = [math]::Round(($_.Group | where { $_.MetricId -eq 'cpu.usage.average' } | Measure-Object -Property Value -Average).Average, 1)

       

       

         MemAvgPerc = [math]::Round(($_.Group | where { $_.MetricId -eq 'mem.usage.average' } | Measure-Object -Property Value -Average).Average, 1)

       

       

         })

       

       

       

      $payload = @{

       

       

              "Date" = $Date

       

       

              "Host" = $Host.Name

       

       

              "CpuAvgPerc" = $CpuAvgPerc

       

       

              "MemAvgPerc" = $MemAvgPerc

       

       

          }

       

       

          Invoke-RestMethod -Method Post -Uri "$endpoint" -Body (ConvertTo-Json @($payload))

       

       

      }

       

       

       

       

      Disconnect-VIServer * -Confirm:$false

       

      $endpoint = "https://api.powerbi.com/beta/a1f1e214-7ded-45b6-81a1-9e8ae3459641/datasets/fa0310ec-1f92-4050-9b14-d0c3954e3f2a/rows?openReportSource=ReportInvitation&ctid=a1f1e214-7ded-45b6-81a1-9e8ae3459641&key=MZW9ghQtERK9HaIlDjfw%2FqyO2VBtJm9fXDjfiS58DNaQTI9C7K8fg0t7gyH4N5Rr98E4pc%2BgEbEHotA9eBlTOA%3D%3D"

      $payload = @{

      "Date" = $Date

      "Host" = $Host.Name

      "CpuAvgPerc" = $CpuAvgPerc

      "MemAvgPerc" = $MemAvgPerc

      }

      Invoke-RestMethod -Method Post -Uri "$endpoint" -Body (ConvertTo-Json @($payload))