13 Replies Latest reply on Jan 18, 2016 7:18 PM by pjcnch

    Orion Report to display Physical servers only

    Deltona

      Hi there,

       

      To keep this simple, how would i generate a report that displays current CPU utilization for Physical servers only.

      I am not interested in VMs. I only want Physical servers. Is it possible?

       

      Thanks in advance,

      Deltona

        • Re: Orion Report to display Physical servers only
          Jay Harris

          I gave the report a shot for you. It works in my environment.

          SELECT Nodes.NodeID, Nodes.StatusLED, Nodes.Caption, Nodes.CPULoad, Nodes.Vendor

          FROM Nodes LEFT OUTER JOIN VIM_VirtualMachines

          ON Nodes.NodeID = VIM_VirtualMachines.NodeID

          WHERE VIM_VirtualMachines.NodeID IS NULL AND (Nodes.Vendor = 'Windows' OR Nodes.Vendor = 'VMWare Inc.' OR Nodes.Vendor = 'Net-SNMP')

           

          I hid the NodeID using field formatting and grouped the report by Vendor.

           

          Hope it helps some. If is almost there and you just need it tweaked let me know and I will see what I can do.

            • Re: Orion Report to display Physical servers only
              Deltona

              Hi Jay,

               

              This seems to be working good apart from including our kvm's and stuff that use Net-SNMP. That i can fix.

              There is an issue i've spotted and that is VMs that are down are added to the report. I take it that's because a VM has to be pingable to become up and before that happens, Orion VIM won't get the status via the vmware sdk.

              I could be mistaken.

                • Re: Orion Report to display Physical servers only
                  Jay Harris

                  I added the “ OR Nodes.Vendor = ‘Net-SNMP’ “ because some Linux servers show up that way, since I got nothing erroneous when I ran the report I left it there.

                   

                  I would have thought that the report would work whether the VM was up or down, once it is in the VIM_VirtualMachines table. (That is what I use to see if the node is a VM or not.) I could see that a ping only node or a new node that has not been ID’ed by Orion VIM yet might not be in that table and thus not in the report right.

                   

                  I will play with some test nodes and see if I can find out what I am missing to get them all to show up.

                  • Re: Orion Report to display Physical servers only
                    Jay Harris

                    Okay, I ran across an older posing of mine on something similar and I bookmarked it. Then yesterday I got some free time and decided to take another shot at this for you.


                    If this does not work right I may be at a loss for what to do next.

                     

                     

                    SELECT Nodes.NodeID, Nodes.StatusLED, Nodes.Caption, Nodes.CPULoad, Nodes.Vendor
                    FROM Nodes
                         LEFT OUTER JOIN VIM_VirtualMachines
                              ON Nodes.NodeID = VIM_VirtualMachines.NodeID
                         LEFT OUTER JOIN (
                                          SELECT NodeMACAddresses.NodeID, 'Virtual' AS NodeType
                                          FROM NodeMACAddresses
                                                LEFT JOIN VIM_HostNodes
                                                     ON (VIM_HostNodes.NodeID = NodeMACAddresses.NodeID)
                                          WHERE (
                                                (NodeMACAddresses.MAC LIKE '0003FF%') OR
                                                (NodeMACAddresses.MAC LIKE '000C29%') OR
                                                (NodeMACAddresses.MAC LIKE '005056%') OR
                                                (NodeMACAddresses.MAC LIKE '000569%') OR
                                                (NodeMACAddresses.MAC LIKE '001C14%')
                                                 )
                                           AND (VIM_HostNodes.HostID IS NULL)
                                           ) AS MACResults
                               ON MACResults.NodeID = Nodes.NodeID
                    WHERE VIM_VirtualMachines.NodeID IS NULL
                    AND MACResults.NodeID IS NULL
                    AND (Nodes.Vendor = 'Windows' OR Nodes.Vendor = 'VMWare Inc.' OR Nodes.Vendor = 'Net-SNMP')
                    ORDER BY Nodes.CPULoad DESC

                     

                     

                    Again, I hid the NodeID using field formatting and grouped the report by Vendor.