Add Physical/VM info to report

Thanks to this thread

Re: Need to create a report that includes the number of CPU sockets, CPU Cores per Socket, CPU Vendor, Total CPU Cores. This should target all physical servers (Windows, Linux, Solaris, ESXi, Nutanix, Xen).

I have a great report that gives socket, core and processor info. I would like to try to add a column with if the server is physical or virtual, but I can't get it to work.

Here is my current SWQL:

SELECT [Node], [OS], [OS Version], [Total CPU Sockets], [Total CPU Cores], [CPU Vendor], [CPU Cores Per Socket]    FROM    (select  N.Caption as [Node],    Max(N.AssetInventory.ServerInformation.OperatingSystem) as [OS],   Max(N.AssetInventory.ServerInformation.OSVersion) as [OS Version],   Count(N.AssetInventory.Processor.Name) as [Total CPU Sockets],    Sum(N.AssetInventory.Processor.NumberOfCores) as [Total CPU Cores],    Max(N.AssetInventory.Processor.Manufacturer) as [CPU Vendor],    Sum(N.AssetInventory.Processor.NumberOfCores)/Count(N.AssetInventory.Processor.Name) as [CPU Cores Per Socket]    from Orion.Nodes N    Group By N.Caption    HAVING Count(N.AssetInventory.Processor.Name) > 0    UNION    (    SELECT  H.HostName as [Node],    CASE     WHEN H.Node.AssetInventory.ServerInformation.OperatingSystem is not NULL THEN H.Node.AssetInventory.ServerInformation.OperatingSystem     ELSE 'WMware ESXi' END as [OS],   CASE     WHEN H.Node.AssetInventory.ServerInformation.OSVersion is not null THEN H.Node.AssetInventory.ServerInformation.OSVersion     ELSE 'unknown' END as [OS Version],   H.CpuPkgCount as [Total CPU Sockets],    H.CpuCoreCount  as [Total CPU Cores],    H.ProcessorType  as [CPU Vendor],    H.CpuCoreCount/CpuPkgCount as [CPU Cores Per Socket]    FROM Orion.VIM.Hosts H    )    ) AS Physical    ORDER BY Physical.Node

What can I add in to give me if the node is physical or virtual?

Thanks all!

Find more posts tagged with

custom swql report

sql server query

Accepted answers

All comments

christopher.t.jones123

Something that might work is add something like the below to your query, if you look at asset inventory on your node details page this corresponds directly to the listed manufacturer, if it is null in the table it returns as 'virtual host', otherwise, it returns the value from the table. Hope this helps

SELECT isnull (Manufacturer, 'Virtual Host') as Manufacturer  
FROM Orion.AssetInventory.ServerInformation}

erikrichter

Thanks, Christopher.

I am about the worst SQL admin known to man and have been trying to successfully insert your recommendation with zero luck... Where would I add the two lines in the query I originally posted?

christopher.t.jones123

Hmm I am not the greatest either, its gonna take me some time to figure this out. I don't think my proposed method will work in this way.

***EDIT***

Try the below code. something like this may work for you. Let me know how it goes.

SELECT [Node], [OS], [OS Version], [Manufacturer], [Total CPU Sockets], [Total CPU Cores], [CPU Vendor], [CPU Cores Per Socket]      
FROM      
(select    
N.Caption as [Node],      
Max(N.AssetInventory.ServerInformation.OperatingSystem) as [OS],     
Max(N.AssetInventory.ServerInformation.OSVersion) as [OS Version],
MAX(N.AssetInventory.ServerInformation.Manufacturer) as [Manufacturer],     
Count(N.AssetInventory.Processor.Name) as [Total CPU Sockets],      
Sum(N.AssetInventory.Processor.NumberOfCores) as [Total CPU Cores],      
Max(N.AssetInventory.Processor.Manufacturer) as [CPU Vendor],      
Sum(N.AssetInventory.Processor.NumberOfCores)/Count(N.AssetInventory.Processor.Name) as [CPU Cores Per Socket]      
from Orion.Nodes N      
Group By N.Caption      
HAVING Count(N.AssetInventory.Processor.Name) > 0      
UNION      
(      
SELECT    
H.HostName as [Node],      
CASE   
    WHEN H.Node.AssetInventory.ServerInformation.OperatingSystem is not NULL THEN H.Node.AssetInventory.ServerInformation.OperatingSystem   
    ELSE 'WMware ESXi'   
END as [OS],     
CASE   
    WHEN H.Node.AssetInventory.ServerInformation.OSVersion is not null THEN H.Node.AssetInventory.ServerInformation.OSVersion   
    ELSE 'unknown'   
END as [OS Version],
CASE H.Node.AssetInventory.ServerInformation.Manufacturer
    WHEN 'VMware, Inc.' THEN 'Virtual'
    ELSE 'Physical'
END as [Manufacturer],
H.CpuPkgCount as [Total CPU Sockets],      
H.CpuCoreCount  as [Total CPU Cores],      
H.ProcessorType  as [CPU Vendor],      
H.CpuCoreCount/CpuPkgCount as [CPU Cores Per Socket]      
FROM Orion.VIM.Hosts H      
)      
) AS Physical      
ORDER BY Physical.Node

***EDIT 2***

So after researching this a little bit more the original Query was designed to exclude VM's. Now I think your best bet would be to create a report with 2 sections. One for Physical Servers and one for VM's

For the Physical you can use the original query designed by m-milligan in Re: Need to create a report that includes the number of CPU sockets, CPU Cores per Socket, CPU Vendor, Total CPU Cores. This should target all physical servers (Windows, Linux, Solaris, ESXi, Nutanix, Xen).

SELECT [Node], [OS], [OS Version], [Total CPU Sockets], [Total CPU Cores], [CPU Vendor], [CPU Cores Per Socket]     FROM(selectN.Caption as [Node],     Max(N.AssetInventory.ServerInformation.OperatingSystem) as [OS],    Max(N.AssetInventory.ServerInformation.OSVersion) as [OS Version],    Count(N.AssetInventory.Processor.Name) as [Total CPU Sockets],     Sum(N.AssetInventory.Processor.NumberOfCores) as [Total CPU Cores],     Max(N.AssetInventory.Processor.Manufacturer) as [CPU Vendor],     Sum(N.AssetInventory.Processor.NumberOfCores)/Count(N.AssetInventory.Processor.Name) as [CPU Cores Per Socket]     from Orion.Nodes N     WHERE N.VirtualMachine.VirtualMachineID is NULLGroup By N.Caption     HAVING Count(N.AssetInventory.Processor.Name) > 0     UNION(     SELECTH.HostName as [Node],     CASEWHEN H.Node.AssetInventory.ServerInformation.OperatingSystem is not NULL THEN H.Node.AssetInventory.ServerInformation.OperatingSystem  ELSE'WMware ESXi'END as [OS],    CASEWHEN H.Node.AssetInventory.ServerInformation.OSVersion is not null THEN H.Node.AssetInventory.ServerInformation.OSVersion  ELSE'unknown'END as [OS Version],    H.CpuPkgCount as [Total CPU Sockets],     H.CpuCoreCount  as [Total CPU Cores],     H.ProcessorType  as [CPU Vendor],     H.CpuCoreCount/CpuPkgCount as [CPU Cores Per Socket]     FROM Orion.VIM.Hosts H     )     ) AS Physical     ORDER BY Physical.Node

For the second datasource for the report you can use this modified query that excludes Physical Boxes

SELECT [Node], [OS], [OS Version], [Total CPU Sockets], [Total CPU Cores], [CPU Vendor], [CPU Cores Per Socket]       FROM  (select  N.Caption as [Node],       Max(N.AssetInventory.ServerInformation.OperatingSystem) as [OS],      Max(N.AssetInventory.ServerInformation.OSVersion) as [OS Version],      Count(N.AssetInventory.Processor.Name) as [Total CPU Sockets],       Sum(N.AssetInventory.Processor.NumberOfCores) as [Total CPU Cores],       Max(N.AssetInventory.Processor.Manufacturer) as [CPU Vendor],       Sum(N.AssetInventory.Processor.NumberOfCores)/Count(N.AssetInventory.Processor.Name) as [CPU Cores Per Socket]       from Orion.Nodes N       WHERE N.VirtualMachine.VirtualMachineID is not NULL  Group By N.Caption       HAVING Count(N.AssetInventory.Processor.Name) > 0       UNION  (       SELECT  H.HostName as [Node],       CASE  WHEN H.Node.AssetInventory.ServerInformation.OperatingSystem is not NULL THEN H.Node.AssetInventory.ServerInformation.OperatingSystem    ELSE'WMware ESXi'  END as [OS],      CASE  WHEN H.Node.AssetInventory.ServerInformation.OSVersion is not null THEN H.Node.AssetInventory.ServerInformation.OSVersion    ELSE'unknown'  END as [OS Version],      H.CpuPkgCount as [Total CPU Sockets],       H.CpuCoreCount  as [Total CPU Cores],       H.ProcessorType  as [CPU Vendor],       H.CpuCoreCount/CpuPkgCount as [CPU Cores Per Socket]       FROM Orion.VIM.Hosts HWHERE h.HostID is NULL       )       ) AS Physical       ORDER BY Physical.Node