Report of all VMs / Nodes with Hardware detail

WinterSoldier

I am trying to get the hardware information in my nodes report. How do I get that? Any suggestions or ideas please?

Here's the below screenshot which displays hardware information of any node on the node details page. Whether it is virtually hosted or a physical one.

I was working on SWQL which gets the list of all the VMs monitored under virtualization as well as which are managed nodes. I would like to add hardware information too in this query. Any leads would be appreciated. Thanks.

SELECT n.Name as [VM],n.IPAddress,n.PowerState ,n.BootTime,n.ProcessorCount,n.Status,h.HostName, d.Name as [Datacenter]
, c.Name as [Cluster], v.DisplayName AS [Vcenter]
, Case 
When n.NodeID IS NULL Then 'No'
When n.NodeID IS Not Null Then 'Yes'
End as [Managed Node], P.Name
FROM Orion.VIM.VirtualMachines n
Join Orion.VIM.Hosts h on n.HostID = h.HostID
Left Outer Join Orion.VIM.Clusters c on c.ClusterID = h.ClusterID
Left Outer Join Orion.VIM.DataCenters d on d.DataCenterID = h.DataCenterID
Left Outer Join Orion.Vim.Platform P on P.PlatformID = n.PlatformID
Left Outer Join Orion.VIM.VCenters v on v.VCenterID = d.VCenterID
Left Outer Join Orion.Nodes NN ON n.NodeID = NN.NodeID
Left Outer Join Orion.NodesCustomProperties NC ON NC.NodeID = n.NodeID

bourlis

Hi WinterSoldier,

I'm sorry it's not 100% clear what you mean by 'hardware detail' and it can be perceived in different ways. Hopefully this answers your question or at lease gives you a direction.  BTW, I got rid of the joins as it wasn't necessary in your SWQL query. 

SELECT n.caption

,n.IP

,n.VirtualMachine.PowerState

,n.CPUCount

,n.Volumes.Caption

,n.AssetInventory.MemoryModule.CapacityMB

,n.AssetInventory.HardDrive.Description

,TOLOCAL(n.LastBoot) as [LastBoot]

,CASE

   When n.Status ='1' then 'Up'

  When n.VirtualMachine.Status ='1'

  then 'Up' ELSE 'Down'

  End as [New Status]

,n.VirtualMachine.Host.HostName

,n.VirtualMachine.Host.DataCenter.Name as [Datacenter]

,n.VirtualMachine.Host.Cluster.Name as [Cluster]

,n.VirtualMachine.Host.DataCenter.VCenter.DisplayName AS [Vcenter]

,Case

  When n.NodeID IS NULL Then 'No'

  When n.NodeID IS Not Null Then 'Yes'

  End as [Managed Node]

,n.VirtualMachine.Platform.Name

from orion.Nodes

n where n.Category = '2'

Also, you have a join for custom properties, you can drop that join by using 

n.CustomProperties.DisplayName