vCenter 5.5 Tasks to Excel

Disclaimer: This is likely unsupported. I am doing this on my lab environment and have not experienced any issues, but again it is a lab environment. With that said use do the things in this post at your own risk.

I received an inquiry about ways to get information about vCenter tasks other than just through the vSphere Client. Specifically to make it easier to filter through the tasks to determine who did what, or even how many times a specific task (such as cloning a VM) was done.

Here are the tasks displayed in the vSphere Client.

By default the number of tasks returned is 100 (I think). From the vSphere Web Client the Tasks list also only shows 100 tasks per page by default. There is a search/filter box in both clients but they are pretty basic. You cannot do complex searches, for example, cloning done by a certain user.

VPX_TASK table in the vCenter Database contains all the tasks which have not be purged based on the Database Retention policy (The Database Retention Policy is configured in Administration -> vCenter Server Settings -> Database Retention Policy).

Just a quick note, in my lab I am running vCenter 5.5 on Windows 2012 R2 using the SQL Express Database which is installed when you deploy vCenter Server without an external database. This same process could be used if the Windows version of vCenter is using an external SQL database but instead you would connect to the SQL server.

I am able pull the data from the VPX_TASK table directly into Excel. To do this you will need a Windows Authenticated or SQL user configured with db_datareader role on the vCenter Database. Since I am using the default SQL Express instance installed with vCenter, I did need to change the authentication mode to mixed to allow for authentication of SQL users.

From the Data tab in Excel, select From Other Sources, and Select From SQL Server

Then just follow the wizard.

Enter the SQL Server Address and Instance and the username and password.

Select the vCenter Database and the VPX_TASK table.

The wizard will save a connection file for future use. You can add a description and specify if you want to save the username and password information to the file.

That is all there is to it. The data from the VPX_TASK table is now available in Excel.

I am guessing someone with some decent Excel chops could do a lot with filtering this. I am not that guy, but you can use the drop downs at the top of each column to filter out specific data.

Someone will probably find this useful, maybe 🙂

Be careful, have fun.