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:
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:
Comments
- Andreas