Persisting vSphere metrics without increasing statistic levels.

Products

• VMware vCenter Server 8.0
• VMware vCenter Server 7.0
• VMware vSphere ESXi 7.0
• VMware vSphere ESXi 8.0

Issue/Introduction

A vSphere administrator may need to collect metrics over an extended time period that are only available when increasing the statistic levels to 3 or 4. Running vCenter at statistic levels 3 or 4 for an extended time is highly discouraged by VMware due to the potential performance impact on the vpxd service. An alternative must be used to collect the required metrics over an extended period that will not cause potential interruption to vCenter. This KB provides an alternative way of persisting the real-time metrics without increasing statistic levels.



Broadcom Techdoc - Statistic Levels
When the statistics levels, level 3 or level 4 are used beyond the default value, it may cause one particular process, vpxd, to sustain memory growth, if it cannot save the statistics information to the database as quickly as required. If the usage limit of these statistics levels is not monitored closely, it may cause vpxd to grow out of memory and eventually crash.

Collection Levels:

Level 1    
    - Cluster Services (VMware Distributed Resource Scheduler) – all metrics
    - CPU – cpuentitlement, totalmhz, usage (average), usagemhz
    - Disk – capacity, maxTotalLatency, provisioned, unshared, usage (average), used
    - Memory – consumed, mementitlement, overhead, swapinRate, swapoutRate, swapused, totalmb, usage (average), vmmemctl (balloon), totalbandwidth (DRAM or PMem)
    - Network – usage (average), IPv6
    - System – heartbeat, uptime
    - Virtual Machine Operations – numChangeDS, numChangeHost, numChangeHostDS
Use for long-term performance monitoring when device statistics are not required.
Level 1 is the default Collection Level for all Collection Intervals.

Level 2    
    - Includes all level 1 metrics
    - CPU – idle, reservedCapacity
    - Disk – All metrics, excluding numberRead and numberWrite.
    - Memory – All metrics, excluding memUsed, maximum and minimum rollup values, read or write latency (DRAM or PMem).
    - Virtual Machine Operations – All metrics
Use for long-term performance monitoring when device statistics are not required but you want to monitor more than the basic statistics.

Level 3    
    - Includes all level 1 and level 2 metrics
    - Metrics for all counters, excluding minimum and maximum rollup values.
    - Device metrics
Use for short-term performance monitoring after encountering problems or when device statistics are required.

Level 4    
   - All metrics supported by the vCenter Server, including minimum and maximum rollup values.    
Use for short-term performance monitoring after encountering problems or when device statistics are required.

 

Environment

vCenter Server

Esxi

Cause

Requirement to persist vSphere metrics over an extended period of time without increasing statistic levels. 

Resolution

• Review the "METRIC_LIST.csv" excel file attached to this KB for a list of metrics and their descriptions (this is not an exhaustive list but should cover the vast majority of requirements). If you know the name of the metrics you want to query, then skip this step.
  
  For the purposes of this KB the Virtual Machine disk metrics "numberRead" and "numberWrite" are used as examples, these metrics are only written to the vCenter database at statistic level 3.
  
  
  
• Install PowerCLI on a system that has connectivity to the vCenter Server/ESXi host.
  PowerCLI Installation Guide
  
  
• NOTE: For the commands hereafter the following values must be changed:
  
  VC/ESXi.example.com   ==  The FQND of the vCenter Server or ESXi you want to connect to.
  [email protected]  ==  A vCenter Server administrator user.  For ESXi it would be "root"
  Your-Password-Here  ==  Password for the vCenter Server administrator or ESXi root account used for the connection.
  
  
  Connect to the vCenter Server/ESXi host system with PowerCLI:
  
  Connect-VIServer -Server VC/ESXi.example.com  -User [email protected]  -Password "Your-Password-Here"
  
  
  
• Optional step to verify metric properties:
  
  Typically, a vSphere metric will have at least the Timestamp,Entity,MetricId,Value and Unit properties, this is not always the case, to check a metrics properties run the following command on PowerCLI:
  
  Get-Stat -Realtime -Start (Get-Date).AddSeconds(-20) -Stat disk.numberRead.summation -Entity TEST-VM  | fl
  
  Note: In the above example the command to retrieve the metric disk.numberRead.summation for virtual machine TEST-VM was run. As this is just a query to get the metric properties you can use any powered on virtual machine.
  
  Example output of command:
  
  Value        : 290
  Timestamp    : 3/9/2025 5:48:40 AM
  MetricId     : disk.numberread.summation
  Unit         : number
  Description  : Number of disk reads during the collection interval
  Entity       : TEST-VM
  EntityId     : VirtualMachine-vm-19
  IntervalSecs : 20
  Instance     : naa.658####000000################
  
  
  
• Using the desired property names (Timestamp, Entity, MetricId, Value and Unit properties are usually sufficient for most cases) run the following command on PowerCLI:
  
  Get-Stat -Realtime -Stat disk.numberRead.summation -Entity TEST-VM  | select Timestamp,Entity,MetricId,Value,Unit |  Export-Csv -Append -NoClobber -NoTypeInformation -Path C:\vSphere_Metrics\metrics.csv
  
  The above command retrieves the disk.numberRead.summation metrics for virtual machine TEST-VM. It selects the Timestamp,Entity,MetricId,IntervalSecs,Value,Unit properties and exports them out to a csv file named metrics.csv in the location C:\vSphere_Metrics\
  
  
  To specify a group of two or more VMs a variable can be defined as such:
  
  $VMS = "TEST-VM","WINDOWS-VM","LINUX-VM"
  
  to get a list of powered on virtual machines that can be used in the above command run:
  
  Get-VM | where {$_.PowerState -eq "PoweredOn"} | select Name
  
  To get metrics for all powered on VMs in the ESXi/vCenter inventory the following variable can be defined:
  
  $VMS = Get-VM | where {$_.PowerState -eq "PoweredOn"}
  
  There are many ways to specify the target virtual machines that the metrics are required for. That is beyond the scope of this KB. See PowerCLI users guide for more examples.
  
  
  Multiple metrics can also be defined, as such:
  
  $METRICS = "disk.numberwrite.summation","disk.numberread.summation"
  
  The following example retrieves the disk.numberwrite.summation, disk.numberread.summation,disk.numberReadAveraged.average and disk.numberWriteAveraged.average metrics for all powered on virtual machines and outputs them to csv file:
  
  $VMS= Get-VM | where {$_.PowerState -eq "PoweredOn"} 
  
  $METRICS = "disk.numberwrite.summation","disk.numberread.summation","disk.numberReadAveraged.average","disk.numberWriteAveraged.average"
  
  Get-Stat -Realtime -Stat $METRICS -Entity $VMS | select Timestamp,Entity,MetricId,Value,Unit |  Export-Csv -Append -NoClobber -NoTypeInformation -Path C:\vSphere_Metrics\metrics.csv
  
  
  IMPORTANT NOTE: The above command can be run manually and the file C:\vSphere_Metrics\metrics.csv will be appended on every iteration. Statistics are collected every 20 seconds and kept for 60 mins so depending on how often, how many entities and how many metrics are collected then this file can grow exponentially. The size on this file on disk should be monitored closely. To stop the file being appended then -NoClobber -Append must be removed from the above command.
  
  
  
  
• This can all be put together to automate vSphere metric collection.
  
  Create a .ps1 file on the desktop or other location on the system with PowerCLI installed.
  
  Copy/paste the following to the .ps1 file:
  
  [Net.ServicePointManager]::SecurityProtocol = [Net.SecurityProtocolType]::Tls,[Net.SecurityProtocolType]::Tls11,[Net.SecurityProtocolType]::Tls12
  Set-PowerCLIConfiguration -InvalidCertificateAction Ignore -Confirm:$false
  Connect-VIServer -Server ESXI/VC.example.com -User [email protected] -Password "YOUR-PASSWORD-HERE"
  $VMS = Get-VM | where {$_.PowerState -eq "PoweredOn"}
  $METRICS = "disk.numberwrite.summation","disk.numberread.summation","disk.numberReadAveraged.average","disk.numberWriteAveraged.average"
  Get-Stat -Realtime -Stat $METRICS -Entity $VMS | select Timestamp,Entity,MetricId,Value,Unit | Export-Csv -Append -NoClobber -NoTypeInformation -Path C:\vSphere_Metrics\metrics.csv
  Disconnect-VIServer -confirm:$false
  
  Save the .ps1 file.
  
  Create a Windows Scheduled Task to run the .ps1 script at the desired interval:
  Run a Powershell script as a scheduled task.
  
  
  

Additional Information

• Utilizing SQLite

  NOTE: Given the amount of data that can be generated when collecting metrics, it can prove challenging when attempting to parse a csv file for pertinent information. SQLite can be used as a persistent database. The use of SQLite in this KB is provided as an "as is" guide to assist customers and is in no way supported by VMware.
  
     

  • Download SQLite for Windows:
    SQLite for Windows
    
    
    
  • Open a Windows command prompt and run the following command:
    
    C:\sqlite\sqlite3.exe C:\vSphere_Metrics\stats.db "create table STATS ('TIMESTAMP','VM_NAME','METRIC_ID','VALUE' INTEGER,'UNIT')"
    
    The above command will create a SQLite database named "stats.db" and creates a table on that database named STATS with six rows to store the vSphere metrics (TIMESTAMP, VM_NAME, METRIC_ID, VALUE and UNIT)
     
    Change "C:\sqlite\sqlite3.exe" to the location of the SQL executable extracted in the previous step.
    
    Change "C:\vSphere_Metrics\stats.db" to the location for the SQLite database to be held.
    
    
    
    • Create a Powershell .ps1 script and populate it with the following:
      
      [Net.ServicePointManager]::SecurityProtocol = [Net.SecurityProtocolType]::Tls,[Net.SecurityProtocolType]::Tls11,[Net.SecurityProtocolType]::Tls12
      Set-PowerCLIConfiguration -InvalidCertificateAction Ignore -Confirm:$false
      Connect-VIServer -Server ESXI/VC.example.com -User [email protected] -Password "YOUR-PASSWORD-HERE"
      $VMS = Get-VM | where {$_.PowerState -eq "PoweredOn"}
      $METRICS = "disk.numberwrite.summation","disk.numberread.summation","disk.numberReadAveraged.average","disk.numberWriteAveraged.average"
      Get-Stat -Realtime -Stat $METRICS -Entity $VMS | select Timestamp,Entity,MetricId,Value,Unit | Export-Csv -NoTypeInformation -Path C:\vSphere_Metrics\metrics.csv
      Disconnect-VIServer -confirm:$false
      C:\sqlite\sqlite3.exe C:\vSphere_Metrics\stats.db -csv ".import C:\vSphere_Metrics\metrics.csv STATS"
      
      
      The Powershell script above does the following:
      
      * Connects to VC/ESXi with PowerCLI:
      Connect-VIServer -Server ESXI/VC.example.com -User [email protected] -Password "YOUR-PASSWORD-HERE"
      
      * Gets a list of all powered on VMs:
      $VMS = Get-VM | where {$_.PowerState -eq "PoweredOn"}
      
      * Defines the list of metrics that will be collected:
      $METRICS = "disk.numberwrite.summation","disk.numberread.summation" (e.t.c)
      
      * Collects the defined metrics for the specified VMs and outputs the data to a csv file:
      Get-Stat -Realtime -Stat $METRICS -Entity $VMS | select Timestamp,Entity,MetricId,IntervalSecs,Value,Unit | Export-Csv -NoTypeInformation -Path C:\vSphere_Metrics\metrics.csv
      
      * Imports the csv file into the SQLite database instance:
      C:\sqlite\sqlite3.exe C:\vSphere_Metrics\stats.db -csv ".import C:\vSphere_Metrics\metrics.csv STATS"
      
      
      
    • Create a Windows Scheduled Task to run the .ps1 script at the desired interval:
      Run a Powershell script as a scheduled task
      
      

NOTE: The csv file will be overwritten on each iteration of the script, but the SQLite database will be persistent and depending on how often the script runs and how many entities it queries and how many metrics are collected this file can grow exponentially. The size on this file on disk should be monitored closely.

• Querying the SQLite database examples
  
  Launch a windows command prompt.

  To list all collected metrics:
  C:\sqlite\sqlite3.exe -table C:\vSphere_Metrics\stats.db "select * from STATS"

  To list all collected metrics for a given day:
  C:\sqlite\sqlite3.exe -table C:\vSphere_Metrics\stats.db "select * from STATS where TIMESTAMP like '3/9/2025%'"
  
  To list all collected for a given day and hour:
  C:\sqlite\sqlite3.exe -table C:\vSphere_Metrics\stats.db "select * from STATS where TIMESTAMP like '3/9/2025 9:%PM'"
  
  To list all metrics for a specific VM and a given day/hour:
  C:\sqlite\sqlite3.exe -table C:\vSphere_Metrics\stats.db "select * from STATS where VM_NAME='TEST-VM' and TIMESTAMP like '3/9/2025 9:%PM'"
  
  To list a specific metric for a VM with a given day/hour:
  C:\sqlite\sqlite3.exe -table C:\vSphere_Metrics\stats.db "select * from STATS where VM_NAME='TEST-VM' and METRIC_ID='disk.numberread.summation' and TIMESTAMP like '3/9/2025 9:%PM'"
  
  To list a specific metric for a VM with a given day/hour and a value greater than 1000:
  C:\sqlite\sqlite3.exe -table C:\vSphere_Metrics\stats.db "select * from STATS where VM_NAME='TEST-VM' and METRIC_ID='disk.numberread.summation' and VALUE>='1000' and TIMESTAMP like '3/9/2025 9:%PM'"
  
  To delete all data in the database:
  C:\sqlite\sqlite3.exe -table C:\vSphere_Metrics\stats.db "delete from STATS"