cancel
Showing results for 
Search instead for 
Did you mean: 
Create Post
Level 11

OS Info Report.

0 Kudos
19 Replies
Level 13

This query will pull the information you need for your report only information about nodes that are ​not​ ESX hosts:

select N.Caption as [Node],
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 NULL
Group By N.Caption
HAVING Count(N.AssetInventory.Processor.Name) > 0
ORDER BY N.Caption

Hi M-Milligan,

Really fantastic. Thank you so much for your valuable answers. Its working.

0 Kudos

Hi M-Milligan,

ESXi (VMware) devices details are not showing/missing. can you please have a look and do the needful.

Report Description:

Please 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).

0 Kudos

Ah, I forgot that CPU, etc for ESX hosts is in Orion.VIM.Hosts, not Orion.Nodes. Try this:

SELECT [Node], [Total CPU Sockets], [Total CPU Cores], [CPU Vendor], [CPU Cores Per Socket]
FROM
(select N.Caption as [Node],
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 NULL
Group By N.Caption
HAVING Count(N.AssetInventory.Processor.Name) > 0
UNION
(
SELECT H.HostName as [Node],
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

This will select any ESX hosts and any nodes that do not have a counterpart in Orion.VIM.VirtualMachines.

Hi M-Milligan,

Greetings!

Thank you so much for prompt response. Yes, It is working well.

Hi M-Milligan,

Greetings!

Is it possible to get the OS on the report for easy sorting?

0 Kudos

Sure. Refer to Node.AssetInventory.ServerInformation.OperatingSystem and Node.AssetInventory.ServerInformation.OSVersion, like this:

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 NULL
Group By N.Caption
HAVING Count(N.AssetInventory.Processor.Name) > 0
UNION
(
SELECT H.HostName as [Node],
H.Node.AssetInventory.ServerInformation.OperatingSystem as [OS],
H.Node.AssetInventory.ServerInformation.OSVersion 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

Hi M-Milligan,

Excellent!  Thank you so much for the splendid support. Looks like few nodes OS information is not showing is there any issue behind that?

pastedImage_1.png

0 Kudos

Interesting. How are you monitoring those servers? Are you using SNMP, ICMP, the Agent, or are you monitoring them through vCenter? On my report, the OS is empty under these conditions:

  1. The server is an ESX host
  2. The server is monitored only through vCenter

ESX hosts run VMware's proprietary pseudo-operating system, ESXi. If you want to force the OS to be 'VMware ESXi' for these hosts, use this query:

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 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 H  
)  
) AS Physical  
ORDER BY Physical.Node  

This query has been VERY helpful. I adjusted it to the following to give me all my servers:

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

QUESTION:

How can I add in a couple columns with IP address, and whether or not the node is physical or virtual?

Hi M-Milligan,

thank you for your response.

Yes, we are monitoring servers via snmpv2c only we are about to move on agent polling soon across Unix categories servers.

bwaccpblade006- monitoring via snmp2vc -Machine Type - Suse (hp).

And also checked few nodes CPU cores per socket  & Total CPU cores are missing for dew nodes.

pastedImage_0.png

0 Kudos

What do you see if you look at the detail page for those nodes? Is there an Asset Inventory page for those nodes? Does the number of CPU cores appear there?

How would I edit this wonderful query to only show me physical servers?

0 Kudos

The query, as-is, should only show physical servers. It looks for nodes that don't have a virtual machine ID linking them to the table of VMs, and it shows ESX hosts, which are physical servers.

0 Kudos

Have a question.  If I have a custom proptery called Hospital_System how would I add that into the report so that I can see what servers belong where?

0 Kudos

Is Hospital_System a custom property of Nodes? If so, you'll need to refer to Nodes.CustomProperties.Hospital_System in the top query, and create a bogus field called Hospital_System in the bottom query, like this, so you can refer to it in the outside query:

SELECT [Node], [Hospital_System], [OS], [OS Version], [Total CPU Sockets], [Total CPU Cores], [CPU Vendor], [CPU Cores Per Socket]   
FROM    
(select  
N.Caption as [Node],    
N.CustomProperties.Hospital_System as [Hospital_System],
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 NULL    
Group By N.Caption    
HAVING Count(N.AssetInventory.Processor.Name) > 0    
UNION    
(    
SELECT  
H.HostName as [Node],    
'' as [Hospital_System],
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

I had thought it worked that way, My team was trying to tell me that it grabbed vm's. 

Sorry about the confusion.

0 Kudos

No problem. Can they show you a VM that's being picked up by the query? If it's really happening, I can help figure out why .

0 Kudos

Hi M-Milligan,

thank you! let me check.

0 Kudos