vMotion SQL Report

I created this report a long time ago when vMotion was viewed w/ much skepticism.  It's proved quite useful even to this day not to only prove that vMotion works, but as a troubleshooting tool to make sure vMotion wasn't the 'cause' of problems.

As unlikely as it may be the 'cause' of issues, transparency helps and 'knowing' the root cause of issues only strengthens the case for a software defined datacenter.  <stepping off soapbox>

Anyway, here are the meat and potatoes:

Query is set to EST and implements daylight savings time on appropriate dates.  You can change the numbers highlight in red to match your time zone.
SQL Query for the Dataset:

SELECT        ENTITY_NAME AS [VM Name], COMPLETE_STATE AS Status, CANCELLED AS Cancelled, CASE WHEN START_TIME >= '11/1/09 02:00:000' THEN DATEADD(hour, - 4
                         START_TIME) WHEN START_TIME BETWEEN '04/1/09 02:00:00.000' AND '11/1/09 01:59:59.999' THEN DATEADD(hour, - 3, START_TIME) 
                         WHEN START_TIME BETWEEN '11/1/08 02:00:00.000' AND '04/1/09 01:59:59.999' THEN DATEADD(hour, - 4, START_TIME) WHEN START_TIME BETWEEN 
                         '04/1/08 02:00:00.000' AND '11/1/08 01:59:59.999' THEN DATEADD(hour, - 3, START_TIME) ELSE START_TIME END AS [Start Time], DATEADD(hour, - 4
                         COMPLETE_TIME) AS [Complete Time], USERNAME AS Username
FROM            VPX_TASK
WHERE        (DESCRIPTIONID = N'vim.VirtualMachine.migrate') OR
                         (DESCRIPTIONID = N'Drm.ExecuteVMotionLRO')
ORDER BY [Start Time] DESC

The returned values for 'status' and 'username' are not very user friendly so here are some expressions I drew up to return some 'friendlier' values:
Status: =IIf(Fields!Status.Value = "success","Completed Successfully","Failed, Check Host Logs")
Username: =Iif(Fields!USERNAME.Value = "","DRS Automated",Fields!USERNAME.Value)

When DRS vMotions a VM, it does so as a null user.  Rather than return a blank field, I fill it in w/ "DRS Automated".  In the end, this is the type of report you can end up with in SQL reporting services:


Andreas Peetz said...

You really should not query the vCenter database for this information, but write a PowerCLI script that uses supported APIs to get the relevant events from the vCenter eventlog!

- Andreas

Chris Nakagaki said...

This was prior to me learning PowerCLI. However, I'm not so sure PowerCLI is all that efficient when trying to pull this data either though. It can be somewhat slow to return this information and even slower when you want historical data.

Cody Konior said...

Do you know if the HOST_ID in the VPX_TASK table for a Drm.ExecuteVMotionLRO is referring to the source host or the destination host of the move?