Possible to retrieve the combine total CPU and Memory of all VMs that are in a given VLAN/vSwitch?

[G0nz0UK]

Hello,

We have various network as you would expect in vCenter that our many VMs are placed into.  For example in one of our Distributed Switches we have a network named VLAN119 that is a dev VLAN with about 40 VMs in it.

I've been asked to output the the combined GHz CPU these VMs use every 2mins and their total RAM to csv or to an Influx DB so it can be graphed in Grafana.

Has anyone tried a PS script like this before as I'm not sure it's even possible?  So if you are in this network called 'VLAN123' then give me the combined CPU in GHz and memory.

We can then work out what teams use what amount of CPU and memory out of the total for that cluster.

DEV (VLAN119) CPU = xGHz

DEV (VLAN119) Mem = xRAM

PROD (VLAN123) etc etc

I hope that made sense.

[G0nz0UK]

No problem, I'll do some research.

[G0nz0UK]

Hi @LucD 

I've got me InfluxDB server working again, so I thought I'd give this one more try.  What am I doing wrong here with this error do you think?

$pgName = 'WHT_VLAN108','WHT_VLAN109'
Get-VDPortgroup -Name $pgName -PipelineVariable vdpg |

$vms = Get-VDPortgroup -Name $pgName | Get-VM | Sort-Object -Property Name
$sStat = @{
  Entity = $vms.Where{$_.PowerState -eq 'PoweredOn'}
  Stat = 'cpu.usageMhz.average', 'mem.granted.average'
  Realtime = $true
  Start = (Get-Date).AddMinutes(-2)
  Instance = ''
}
$tabVM = @{}
Get-Stat @SStat | Group-Object -Property {$_.Entity.Name} |
ForEach-Object -Process {
  $tabVM.Add($_.Name,$_.Group)
}
$result = $vms | ForEach-Object -Process {
  $obj = New-Object -TypeName PSObject -Property ([ordered]@{
      VM = $_.Name
      'Provisioned Space' = [math]::Round($_.ProvisionedSpaceGB, 2)
      'Used Space' = [math]::Round($_.UsedSpaceGB, 2)
      CPUMHz = 0
      MemGB = 0
  })
  if($tabVm.ContainsKey($_.Name)){
      $obj.CPUMHz = [math]::Round(($tabVM[$_.Name].Where{ $_.MetricId -eq 'cpu.usageMhz.average' }.Value | Measure-Object -Average).Average, 0)
      $obj.MemGB = [math]::Round(($tabVM[$_.Name].Where{ $_.MetricId -eq 'mem.granted.average' }.Value | Measure-Object -Average).Average / 1MB, 2)
  }
  $obj
} | Measure-Object -Property 'Provisioned Space','Used Space',CPUMHz,MemGB -Sum

$metrics = @{
    Portgroup = $pgName
    'Provisioned Space' = $result.Where{ $_.Property -eq 'Provisioned Space' }.Sum
    'Used Space' = $result.Where{ $_.Property -eq 'Used Space' }.Sum
    CPUMHz = $result.Where{ $_.Property -eq 'CPUMHz' }.Sum
    MemGB = $result.Where{ $_.Property -eq 'MemGB' }.Sum
}

Write-Influx -Measure CorpCombinedStats -Tags @{host="Corp"} -Metrics $Metrics -Database "tempDB2" -Server http://10.1.2.5:8086 -Credential $credentialsinflux
    Start-Sleep -Seconds 10

Error

 At C:\Scripts\VMware\vmware-combined-stats-influx.ps1:16 char:1

+ $vms = Get-VDPortgroup -Name $pgName | Get-VM | Sort-Object -Property ...

+ ~~~~

Expressions are only allowed as the first element of a pipeline.

    + CategoryInfo          : ParserError: (:) [], ParentContainsErrorRecordException

    + FullyQualifiedErrorId : ExpressionsMustBeFirstInPipeline 

[LucD]

The 2nd line has a pipeline symbol at the end.
And I  thnk this 2nd line is not needed at all.

Get-VDPortgroup -Name $pgName -PipelineVariable vdpg |

[G0nz0UK]

Oh ok, I'll try that shortly thanks!

Regarding the other fantastic script where it pushes the data to CSV. I've managed to push the CSV into the InfluxDB, but it doesn't like the fact that I have no time column at the start for when the data was polled/pulled.

What would need to be added for that?

$pgName = 'WHT_VLAN108','WHT_VLAN109'
Get-VDPortgroup -Name $pgName -PipelineVariable vdpg |

ForEach-Object -Process {
    $vms = Get-VM -RelatedObject $vdpg | Sort-Object -Property Name
    $sStat = @{
        Entity = $vms.Where{ $_.PowerState -eq 'PoweredOn' }
        Stat = 'cpu.usageMhz.average', 'mem.granted.average'
        Realtime = $true
        Start = (Get-Date).AddMinutes(-2)
        Instance = ''
        ErrorAction = 'SilentlyContinue'
    }
    $tabVM = @{}
    Get-Stat <li-user uid="2039969" login="SStat"></li-user> | Group-Object -Property { $_.Entity.Name } |
    ForEach-Object -Process {
        $tabVM.Add($_.Name, $_.Group)
    }
    $result = $vms | ForEach-Object -Process {
        $obj = New-Object -TypeName PSObject -Property ([ordered]@{
                Portgroup = $vdpg.Name
                VM = $_.Name
                'Provisioned Space' = [math]::Round($_.ProvisionedSpaceGB, 2)
                'Used Space' = [math]::Round($_.UsedSpaceGB, 2)
                CPUMHz = 0
                MemGB = 0
            })
        if ($tabVm.ContainsKey($_.Name)) {
            $obj.CPUMHz = [math]::Round(($tabVM[$_.Name].Where{ $_.MetricId -eq 'cpu.usageMhz.average' }.Value | Measure-Object -Average).Average, 0)
            $obj.MemGB = [math]::Round(($tabVM[$_.Name].Where{ $_.MetricId -eq 'mem.granted.average' }.Value | Measure-Object -Average).Average / 1MB, 2)
        }
        $obj
    } | Measure-Object -Property 'Provisioned Space', 'Used Space', CPUMHz, MemGB -Sum

    New-Object -TypeName PSObject -Property ([ordered]@{
        Portgroup = $vdpg.Name
        'Provisioned Space' = $result.Where{ $_.Property -eq 'Provisioned Space' }.Sum
        'Used Space' = $result.Where{ $_.Property -eq 'Used Space' }.Sum
        CPUMHz = $result.Where{ $_.Property -eq 'CPUMHz' }.Sum
        MemGB = $result.Where{ $_.Property -eq 'MemGB' }.Sum
    })
} | Export-Csv -Path "C:\vmware\powercli\reports\vmware-stats.csv" -NoTypeInformation -UseCulture

Thanks!

[G0nz0UK]

Oh wow and on the InfluxDB PS script it does now go into InfluxDB, well done! 

However the 'Portgroup' names don't seem to be on separate lines

In Grafana I see 'Portgroup' appear like this, instead of 'where' as it should be listed under 'host' as an option, then after that I should be able to show select the portgroup/vlan, I can't think why it would be like this:

So close on both!

[LucD]

Are the values in the CSV for the Portgroup column correct?

[G0nz0UK]

Sure are:

It would be great to have the CSV show the timestamp in column 1 and also as another option got InfluxDB right.

So excited!  Crazy what can be done.

[LucD]

Not sure what datetime format Influx requires, but you can add a Timestamp like this

New-Object -TypeName PSObject -Property ([ordered]@{
  Timestamp = (Get-Date)
  Portgroup = $vdpg.Name
  'Provisioned Space' = $result.Where{ $_.Property -eq 'Provisioned Space' }.Sum
  'Used Space' = $result.Where{ $_.Property -eq 'Used Space' }.Sum
  CPUMHz = $result.Where{ $_.Property -eq 'CPUMHz' }.Sum
  MemGB = $result.Where{ $_.Property -eq 'MemGB' }.Sum
}) 

[G0nz0UK]

Thanks, well that certainly added the timestamp to the CSV but InfluxDB/Grafana doesn't seem to want to use it so I can't graph it, so I'll do some research on the Influx/Grafana site for that I think.

Up above you managed to get the data into InfluXDB so maybe that is the route to go, but there is a slight issue on how the PS script is sending it to InfluxDB where those 'PortGroups' need to be where the 'where' option like this below.  From the dropdown menu I get the option of 'host' and 'vcenter' from the script, but I was hoping it would list all the 'Portgroups' I have added to the script.

Do you think this is even possible?

[LucD]

It looks like InfluxDB wants the timestamp in epoch time.
Can you try this?

New-Object -TypeName PSObject -Property ([ordered]@{
    Timestamp = ([System.DateTimeOffset]::new((Get-Date))).ToUnixTimeMilliseconds()
    Portgroup = $vdpg.Name
    'Provisioned Space' = $result.Where{ $_.Property -eq 'Provisioned Space' }.Sum
    'Used Space' = $result.Where{ $_.Property -eq 'Used Space' }.Sum
    CPUMHz = $result.Where{ $_.Property -eq 'CPUMHz' }.Sum
    MemGB = $result.Where{ $_.Property -eq 'MemGB' }.Sum
  })

On why InfluxDB does not seem to take Portgroup as a valid Field in a Where query, I have no clue.
You should perhaps talk to the InfluxDB DBA.

[G0nz0UK]

I'll try that thanks.

Yeah there is no InfluxDB guy, I'm just using their Stack forum for help, the just sent me this and said don't worry about sending to CSV and use PS to send into INflux https://github.com/markwragg/PowerShell-Influx not too helpful.

[LucD]

Sorry, can't help you there.

But at least in one of the issues on that repo it seems to confirm that InfluxDB needs an epochtime.
My previous suggestion for the Timestamp should work as far as I can tell.

[G0nz0UK]

Hello,

This script you created is working so well (thanks!!), now I'm sure this is not possible, but can it exclude names of VM in the vDS port group.

For example if we have a portgroup with 200 VMs but there are 5 VMs we want excluded from the combined total is that possible?

This is the current script:

$pgName = 'WHT_VLAN108','WHT_VLAN109'
Get-VDPortgroup -Name $pgName -PipelineVariable vdpg |

ForEach-Object -Process {
    $vms = Get-VM -RelatedObject $vdpg | Sort-Object -Property Name
    $sStat = @{
        Entity = $vms.Where{ $_.PowerState -eq 'PoweredOn' }
        Stat = 'cpu.usageMhz.average', 'mem.granted.average'
        Realtime = $true
        Start = (Get-Date).AddMinutes(-2)
        Instance = ''
        ErrorAction = 'SilentlyContinue'
    }
    $tabVM = @{}
    Get-Stat @SStat | Group-Object -Property { $_.Entity.Name } |
    ForEach-Object -Process {
        $tabVM.Add($_.Name, $_.Group)
    }
    $result = $vms | ForEach-Object -Process {
        $obj = New-Object -TypeName PSObject -Property ([ordered]@{
                Portgroup = $vdpg.Name
                VM = $_.Name
                'Provisioned Space' = [math]::Round($_.ProvisionedSpaceGB, 2)
                'Used Space' = [math]::Round($_.UsedSpaceGB, 2)
                CPUMHz = 0
                MemGB = 0
            })
        if ($tabVm.ContainsKey($_.Name)) {
            $obj.CPUMHz = [math]::Round(($tabVM[$_.Name].Where{ $_.MetricId -eq 'cpu.usageMhz.average' }.Value | Measure-Object -Average).Average, 0)
            $obj.MemGB = [math]::Round(($tabVM[$_.Name].Where{ $_.MetricId -eq 'mem.granted.average' }.Value | Measure-Object -Average).Average / 1MB, 2)
        }
        $obj
    } | Measure-Object -Property 'Provisioned Space', 'Used Space', CPUMHz, MemGB -Sum

    New-Object -TypeName PSObject -Property ([ordered]@{
        Timestamp = (Get-Date)
        Portgroup = $vdpg.Name
        'Provisioned Space' = $result.Where{ $_.Property -eq 'Provisioned Space' }.Sum
        'Used Space' = $result.Where{ $_.Property -eq 'Used Space' }.Sum
        CPUMHz = $result.Where{ $_.Property -eq 'CPUMHz' }.Sum
        MemGB = $result.Where{ $_.Property -eq 'MemGB' }.Sum
    })
} | Export-Csv -Path "C:\vmware\powercli\reports\vmware-combined-stats.csv" -NoTypeInformation -UseCulture

Imagine the VMs could be Server1, Server2, Server3, LuCD1, LuCDx

Thanks

[LucD]

You could do something like this

$pgName = 'WHT_VLAN108','WHT_VLAN109'
$excludeVM = 'Server1', 'Server2', 'Server3', 'LuCD1', 'LuCDx'
Get-VDPortgroup -Name $pgName -PipelineVariable vdpg |

ForEach-Object -Process {
    $vms = Get-VM -RelatedObject $vdpg | 
        where{$_.Name -notin $excludeVM} |
        Sort-Object -Property Name

[G0nz0UK]

Amazing!

I don't suppose it does wildcards such as anything starting with Server* or containing *Server* etc

No worries if not.

[LucD]

You can combine the conditions in the Where-clause.
This also excludes names starting with "Server".
 (the ^ symbol indicates the start of the String in RegEx)

With -match and -notmatch you can use RegEx to specify the condition

ForEach-Object -Process {
    $vms = Get-VM -RelatedObject $vdpg | 
        where{$_.Name -notin $excludeVM -and $_.Name -notmatch "^Server" } |
        Sort-Object -Property Name

[G0nz0UK]

Do I need remove this line too as I no longer need to manually type each server with you Regex option?

$excludeVM = 'Server2', 'Server3', 'LuCD1', 'LuCDx'

Like this 

$pgName = 'WHT_VLAN108','WHT_VLAN109'
$excludeVM = 'Server2', 'Server3', 'LuCD1', 'LuCDx'
Get-VDPortgroup -Name $pgName -PipelineVariable vdpg |

ForEach-Object -Process {
    $vms = Get-VM -RelatedObject $vdpg | 
        where{$_.Name -notin $excludeVM -and $_.Name -notmatch "^Server" } |
        Sort-Object -Property Name

[LucD]

If you keep the -notin operator and not all VMs are filtered by the -match or -notmatch, then yes.

[G0nz0UK]

So if something like this if we want to exclude/filter out anything starting with Server?

$pgName = 'WHT_VLAN108','WHT_VLAN109'
Get-VDPortgroup -Name $pgName -PipelineVariable vdpg |

ForEach-Object -Process {
    $vms = Get-VM -RelatedObject $vdpg | 
        where{$_.Name -notin $excludeVM -and $_.Name -notmatch "^Server" } |
        Sort-Object -Property Name

[LucD]

Then you leave out the -notin condition

$pgName = 'WHT_VLAN108','WHT_VLAN109'
Get-VDPortgroup -Name $pgName -PipelineVariable vdpg |

ForEach-Object -Process {
    $vms = Get-VM -RelatedObject $vdpg | 
        where{$_.Name -notmatch "^Server" } |
        Sort-Object -Property Name