Keeping track of the the virtual machines can be a little pain. Here is a query and a report that hopefully will make it a bit easier to keep track of those virtual machines and their corresponding physical hosts.

The Query

select distinct SMS_R_System.Name, SMS_G_System_OPERATING_SYSTEM.Caption, SMS_G_System_VIRTUAL_MACHINE.PhysicalHostNameFullyQualified from  SMS_R_System inner join SMS_G_System_OPERATING_SYSTEM on SMS_G_System_OPERATING_SYSTEM.ResourceID = SMS_R_System.ResourceId inner join SMS_G_System_VIRTUAL_MACHINE on SMS_G_System_VIRTUAL_MACHINE.ResourceID = SMS_R_System.ResourceId where SMS_G_System_VIRTUAL_MACHINE.PhysicalHostNameFullyQualified is not null  order by SMS_G_System_VIRTUAL_MACHINE.PhysicalHostNameFullyQualified

image

The Report

SELECT     TOP (100) PERCENT dbo.v_R_System.Netbios_Name0 AS Name, dbo.v_GS_OPERATING_SYSTEM.Caption0 AS [Operating System],
                      dbo.v_GS_VIRTUAL_MACHINE.PhysicalHostNameFullyQualifi0 AS [Physical host]
FROM         dbo.v_R_System INNER JOIN
                      dbo.v_GS_OPERATING_SYSTEM ON dbo.v_R_System.ResourceID = dbo.v_GS_OPERATING_SYSTEM.ResourceID INNER JOIN
                      dbo.v_GS_VIRTUAL_MACHINE ON dbo.v_R_System.ResourceID = dbo.v_GS_VIRTUAL_MACHINE.ResourceID
WHERE     (dbo.v_GS_VIRTUAL_MACHINE.PhysicalHostName0 IS NOT NULL)
ORDER BY [Physical host]

image