< Blog

SQL Report – IF/CASE forum post

This post is to answer a question within the TechNet forums. For more details please see the forum post.
https://social.technet.microsoft.com/Forums/en-US/406bf692-76aa-4804-b412-7f8c045b2b82/sql-report-ifcase?forum=configmanagergeneral

In a nutshell this query will find all VMs and provides basic detail about them.

SELECT distinct
SYS.Netbios_Name0,
Gvm.PhysicalHostName0,
SYS.User_Name0,
SYS.Resource_Domain_OR_Workgr0,
OPSYS.Caption0 as C054,
OPSYS.Version0,
CSYS.Manufacturer0,
CSYS.Model0,
Processor.MaxClockSpeed0,
MEM.TotalPhysicalMemory0,
WSTATUS.LastHWScan
FROM
v_R_System SYS
LEFT JOIN v_GS_X86_PC_MEMORY MEM on SYS.ResourceID = MEM.ResourceID
LEFT JOIN v_GS_COMPUTER_SYSTEM CSYS on SYS.ResourceID = CSYS.ResourceID
LEFT JOIN v_GS_PROCESSOR Processor on Processor.ResourceID = SYS.ResourceID
LEFT JOIN v_GS_OPERATING_SYSTEM OPSYS on SYS.ResourceID = OPSYS.ResourceID
LEFT JOIN v_GS_WORKSTATION_STATUS wSTATUS on SYS.ResourceID = WSTATUS.ResourceID
LEFT JOIN (SELECT * FROM v_GS_VIRTUAL_MACHINE_64 UNION SELECT * FROM v_GS_VIRTUAL_MACHINE) gvm ON SYS.ResourceID = gvm.ResourceID
WHERE
OPSYS.Caption0 is not null
and CSYS.Model0 = ‘Virtual Machine’
ORDER BY
SYS.Netbios_Name0,
SYS.Resource_Domain_OR_Workgr0