vCenter Server Performance Graphs shows empty result for Historical Stats like Past Day/Week/Month/Year

Products

• VMware vCenter Server

Issue/Introduction

Symptoms:

• You have recently migrated vCenter Server running on Windows OS with Oracle Database to VCSA
• Last Day/Week/Month/Year performance charts shows empty result while checking from UI or WebClient (Monitor -> Performance -> Overview or Advanced ->Select Last Day / Week / Month / Year)
• Historical stats in VCDB shows empty rows
  • Connect to Postgres using command
    • /opt/vmware/vpostgres/current/bin/psql -d VCDB -U postgres
  • Execute below query
    select count( * ) from VPX_HIST_STAT2;
    Example:
    VCDB=# SELECT COUNT(*) FROM vpx_hist_stat2;
     count
    -------
         0 ---->>> shows 0 rows
    (1 row)

Environment

VMware vCenter Server Appliance 6.7.x

Cause

This is caused due to incorrect behavior of Stats Rollup Jobs on vCenter Server

Resolution

Follow below steps to resolve the issue:

1. Login to VCSA using SSH.
2. Change shell to bash if you are on appliance shell.

    * List APIs: "help api list"
    * List Plugins: "help pi list"
    * Launch BASH: "shell"

Command> shell
Shell access is granted to root
root@vcsa1 [ ~ ]#

3. Take backup of /usr/lib/vmware-vpx/sql/postgres.sql file before editing.

cp /usr/lib/vmware-vpx/sql/postgres.sql /usr/lib/vmware-vpx/sql/postgres.sql_backup

4. Stop VPXD Service.

service-control --stop vmware-vpxd

Note: vCenter Server will be down after stopping VPXD service, stop the service and proceed with the steps if you have downtime.

5. Edit /usr/lib/vmware-vpx/sql/postgres.sql

vi /usr/lib/vmware-vpx/sql/postgres.sql

6. Search for strings "query:kQueryStatsStatIdNullDevice1Part" , "query:kQueryStatsStatIdNullDevice2", "query:kQueryStatsStatIdNullDevice3"  and "query:kQueryStatsStatIdNullDevice4" in the postgres.sql file

      7. Connect to Postgres using command:

/opt/vmware/vpostgres/current/bin/psql -d VCDB -U postgres
 

     8. Download the attached scripts and copy to VCSA (/tmp location) using WinScp, follow
         Error when uploading files to vCenter Server Appliance using WinSCP if you face
         difficulty to  connect to VCSA.                               

     9. Change ownership of copied files using below command:
      chown vpostgres:users /tmp/sql1
    chown vpostgres:users /tmp/sql2
    chown vpostgres:users /tmp/sql3

    10. Recreate the Stats Rollup Procedures by importing the downloaded scripts.
    VCDB=# \i /tmp/sql2
    VCDB=# \i /tmp/sql1
     VCDB=# \i /tmp/sql3
        Example:
    VCDB=# \i /tmp/SQL2
    CREATE FUNCTION
    VCDB=# \i /tmp/SQL1
    CREATE FUNCTION
    VCDB=# \i /tmp/SQL3
    CREATE FUNCTION

 

     11. Start VPXD service and refresh the UI Client.
           service-control --start vpxd

Note:  It will take some time to populate the historical data post recreating the jobs. You may verify the contents using Postgres query select count( * ) from VPX_HIST_STAT2; This will show the Stats available for Past Week data.