Add to SWQL query - IP and Phys/VM

erikrichter

User m-milligan has a great core/socket query that I adjusted below to fit my needs. I would like to add 2 columns to this query, but can't seem to get the syntax correct.

What would I need fro an IP Address column and a column that states whether the node is physical or virtual?

Thanks!

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 

m-milligan

This query will show you IP Address and whether those node is physical or virtual (the NodeType column).

SELECT [Node], [IPAddress], [NodeType], [OS], [OS Version], [Total CPU Sockets], [Total CPU Cores], [CPU Vendor], [CPU Cores Per Socket]       
FROM       
(select     
N.Caption as [Node],     
N.IPAddress as [IPAddress],
CASE    
    WHEN VM.NodeID is not NULL THEN 'Virtual'
    ELSE 'Physical'
END as [NodeType],      
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
LEFT OUTER JOIN Orion.VIM.VirtualMachines VM on N.NodeID=VM.NodeID
Group By N.Caption, N.IPAddress, VM.NodeID
HAVING Count(N.AssetInventory.Processor.Name) > 0    
UNION       
(       
SELECT     
H.HostName as [Node],
H.IPAddress as [IPAddress],
'Physical' as [NodeType],
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

The query assumes that anything in Orion.VIM.Hosts is a physical server. The query adds a join between Orion.Nodes and Orion.VIM.VirtualMachines. If a row in Orion.Nodes has a corresponding row in Orion.VIM.VirtualMachines, we know it's a virtual machine. If a row in Orion.Nodes does not have a corresponding row in Orion.VIM.VirtualMachines, we assume it's a physical server.