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

Find more posts tagged with

hardware

select_ip_address

for

entity

servers

physical

server

report

Accepted answers

jayharris

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.

All comments

Deltona

Bump

familyofcrowes

I tried this, but it looks like it will have to be an SQL report. I am not that great with SQL so I will pass this on to a co-worker and see what he thinks.... I'll update if I have anything useful.

Deltona

Hi,

I appreciate you giving this a go. Thank you!

Deltona

BUMP

jayharris

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.

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.

jayharris

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.

Deltona

Thank you Jay, it is greatly appreciated!

jayharris

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.

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

Deltona

Excellent. Thanks Jay!

This has met my requirements for now, I hope it will come to good use for others, too.

pjcnch

This looks great but could anyone help me outwith some guidance on how to make a "report" out of the SQL query in the Orion Reports tab?