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

How to run inventory report of only physical servers?

Jump to solution

I need to run a report that lists physical servers.  I need something similar to the default report named "Inventory Fixed Asset Report"  but I need to leave out all virtual machines.  There doesn't seem to be a good way to do this.  What am I missing?  Seems to me that this is how you'd normally want a 'Fixed Asset Report' to work.  Any ideas?

1 Solution
Level 9


After contacting Support, I was able to get it working.  I had to explicitly tell the report to include Models that are empty.  Here's the query I ended up with to filter out VMs from report:ScreenShot364.jpg

View solution in original post

9 Replies
Level 9

Well, after backing out the exclusions for Virtual Machines and VMware Virtual Platform models, this node does show back up in the report. That node is definitely not a VM so adding this exclusion is definitely doing something that I don't understand.

0 Kudos
Product Manager
Product Manager

I'm not seeing this in my lab environment. Please ensure that you are using the appropriate "Model" field, as there are no less than 11 fields in the Report Writer named "Model". The one you should be using in this report is under [Asset Inventory -> System Information]. If that does not resolve the issue, please open a case with Support so we can troubleshoot the issue further.

Model Asset Inventory.png

0 Kudos
Level 9

I opened a Support case.  I am using the "Model" field from System Inventory.

It looks like what's happening is if I do a query like the pic below, the report filters out nodes with the specified Models correctly….but it also filters out nodes whose Model is empty.  Which is why the node I mentioned earlier is getting left out of the report.


ScreenShot351.jpg

0 Kudos
Level 9


After contacting Support, I was able to get it working.  I had to explicitly tell the report to include Models that are empty.  Here's the query I ended up with to filter out VMs from report:ScreenShot364.jpg

View solution in original post

Level 8

Go Settings > Manage Reports > Create a new report > Use Advance Database SQL Query > Select SQL and use the script below:


SELECT Nodes.NodeID, Nodes.StatusLED, Nodes.Caption, Nodes.Vendor, Nodes.IP_Address, Nodes.Machinetype
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.Caption ASC


0 Kudos
Product Manager
Product Manager

Great share  maxjam‌! I turned this into a web based report and posted it in the Content Exchange for everyone in the community to enjoy.

Physical Server Inventory Report

0 Kudos
Product Manager
Product Manager

The simplest way to exclude virtual machines from Asset Inventory reports is to add this as a filter condition of the report using the "Model" - "System Information" property..

model.png

Then exclude all nodes where the "Model" property equals "Virtual Machine"

Virtual Machine.png

0 Kudos
Level 9

Thanks.  It looks like excluding Virtual Machines and VMware Virtual Platform Models effectively filters out my VMs.  However, I've noticed that this report leaves out a lot of other nodes for some reason.  I have lots of physical nodes that don't make it into this report.

I would have expected that a selection that includes All Nodes where the Host Name is not empty would catch just about everything.  For example, this node does not make it into the report.

ScreenShot310.jpg

Product Manager
Product Manager

It's rare, but honestly I'm stumped. I cannot think of any reason why this node would not appear in the out-of-the-box Fixed Asset Inventory report. If after removing your filter this node does not re-appear in the report please open a case with support so we can dig into this further.

0 Kudos