«

»

Jul 01

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.
vsphere-client-tasks
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
data-from-othersources

Then just follow the wizard.

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

Select the vCenter Database and the VPX_TASK table.
vpx_task

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.
dataconnection-vpx_task

That is all there is to it. The data from the VPX_TASK table is now available in Excel.
tasks-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.
filtering

Someone will probably find this useful, maybe 🙂

Be careful, have fun.

About the author

vHersey

Hersey Cartwright is an IT professional with extensive experience designing, implementing, managing, and supporting technologies that improve business processes. Hersey is Solutions Architect for SimpliVity covering Virginia, Washington DC, and Maryland. He holds the VMware Certified Design Expert (VCDX-DV #128) certification. Hersey actively participates in the VMware community and was awarded the VMware vExpert title in 2016, 2015, 2014, 2013, and 2012. He enjoys working with, teaching, and writing about virtualization and other data center technologies. Follow Hersey on Twitter @herseyc

1 comment

  1. fernando

    Hi.
    Is it possible to export data from VCDB about (CPU usage and Memory Usage) using Microsoft SQL Server 2008 queries and save in microsoft excel 2007 file?

    Tks.

    Fernando.

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>

four × four =