How to export vCenter tasks & events via command line

Products

• VMware vCenter Server

Issue/Introduction

All the vCenter tasks or events are not displaying in the vCenter UI, despite the task retention being set to 30 days on vCenter.

Environment

VMware vCenter 7.x
VMware vCenter 8.x

Cause

Due to the high volume of tasks, the vCenter UI either continuously loads or fails to display all tasks.

Resolution

Login to vCenter Server via SSH and execute the below command :

/opt/vmware/vpostgres/current/bin/psql -U postgres -d VCDB -c "COPY (select * from vpxv_events where create_time >= date_trunc('DAY',NOW()) - INTERVAL '7 DAYS') to '/tmp/vpxv_events.csv' DELIMITER ','CSV HEADER;COPY (select * from vpxv_event_arg where event_id in (select event_id from vpxv_events where create_time >= date_trunc('DAY',NOW()) - INTERVAL '7 DAYS')) to '/tmp/vpxv_event_arg.csv' DELIMITER ','CSV HEADER;COPY (select * from vpx_task where start_time >= date_trunc('DAY',NOW()) - INTERVAL '7 DAYS') to '/tmp/vpx_task.csv' DELIMITER ','CSV HEADER;";mkdir /var/log/vmware/tasks-events;mv /tmp/vpx_task.csv /var/log/vmware/tasks-events/tasks.csv;mv /tmp/vpxv_event_arg.csv /var/log/vmware/tasks-events/events.csv;echo 'Delete /var/log/vmware/task-events since this is a folder for support purposes only' > /var/log/vmware/tasks-events/readme.txt;date >> /var/log/vmware/tasks-events/readme.txt

 

Note : The above command creates a folder called tasks-events under /var/log/vmware, from there it will export two CSV files for tasks and events. You can adjust the number of interval days in the above command from 7 to any value based on your requirements.

 

Additional Information

This command sequence connects to the VMware vCenter PostgreSQL database (VCDB) as the postgres user, extracts task, event, and event argument data for the last 7 days (plus the current day) into temporary CSV files in /var/log/vmware. It then creates a directory /var/log/vmware/tasks-events, moves the vpx_task.csv and vpxv_event_arg.csv files into it (renaming them to tasks.csv and events.csv respectively), and finally creates/updates a readme.txt file within that directory, explaining its purpose and adding a timestamp. The vpxv_events.csv file created by the first COPY command is not moved and remains in /tmp as that directory does not persist reboots
.

 

To understand what information these tasks and events files hold and how they can be useful - these are the fields in each file which make reading the task/event information even easier.
 

head -n1 events.csv
event_id,chain_id,event_type,extended_class,create_time,username,category,vm_id,vm_name,host_id,host_name,computeresource_id,computeresource_type,computeresource_name,datacenter_id,datacenter_name,datastore_id,datastore_name,network_id,network_name,network_type,dvs_id,dvs_name


head -n1 tasks.csv
task_id,name,descriptionid,entity_id,entity_type,entity_name,locked_data,complete_state,cancelled,cancellable,error_data,result_data,progress,reason_data,queue_time,start_time,complete_time,event_chain_id,username,vm_id,host_id,computeresource_id,datacenter_id,resourcepool_id,folder_id,alarm_id,scheduledtask_id,change_tag_id,parent_task_id,root_task_id,description,activation_id 

Credit to Allan Robertson who helped with the DB aspect of this command.