12 Replies Latest reply on Apr 28, 2018 12:43 AM by er.vansh17091

    OS Info Report.

    er.vansh17091

      OS Info.

        • 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).
          m-milligan

          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
          1 of 1 people found this helpful
              • Re: OS Info Report.
                er.vansh17091

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

                  • Re: OS Info Report.
                    m-milligan

                    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.

                    1 of 1 people found this helpful
                      • Re: OS Info Report.
                        er.vansh17091

                        Hi M-Milligan,

                         

                        Greetings!

                         

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

                          • Re: OS Info Report.
                            er.vansh17091

                            Hi M-Milligan,

                             

                            Greetings!

                             

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

                              • Re: OS Info Report.
                                m-milligan

                                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 
                                1 of 1 people found this helpful
                                  • Re: OS Info Report.
                                    er.vansh17091

                                    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?

                                     

                                      • Re: OS Info Report.
                                        m-milligan

                                        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   
                                        1 of 1 people found this helpful