SWQL to get Clusters, Hosts, VMS

Hi I was hoping i could get some help.

I am trying to get all our VM's with what host and cluster they are on for use importing into a CMDB.

So far i think i found the info in the tables but don't know how to link that into a single query that provides the 3 things.

SELECT ClusterID,HostID,.GuestDnsName, GuestState
FROM Orion.VIM.DataCenters,Orion.VIM.Clusters,Orion.VIM.Hosts,Orion.VIM.VirtualMachines
Parents
  • This is the SWQL I use to view our VM stuff.

    SELECT
    Name AS [VM]
    ,DetailsUrl AS [_LinkFor_VM]
    ,'/Orion/StatusIcon.ashx?entity=Orion.VIM.VirtualMachines.Vmware&status='+ TOSTRING(VirtualMachines.Status) +'&size=Small/' AS [_IconFor_VM]
    ,CASE
        WHEN NodeID > 0 THEN 'YES'
    	ELSE 'NO'
    END AS [Monitored]
    ,'/Orion/Admin/Accounts/images/icons/' +
    	CASE
    		WHEN NodeID IS NULL THEN 'disable'
    		ELSE 'ok_enabled'
    	END +
    + '.png' AS [_IconFor_Monitored]
    
    ,VirtualMachines.Host.Cluster.Name AS Cluster
    ,VirtualMachines.Host.Cluster.DetailsUrl AS [_LinkFor_Cluster]
    ,'/Orion/StatusIcon.ashx?entity=Orion.VIM.Clusters&id=0&status='+ TOSTRING(VirtualMachines.Host.Cluster.Status) +'&size=Small/' AS [_IconFor_Cluster]
    ,VirtualMachines.Host.HostName
    ,VirtualMachines.Host.DetailsUrl AS [_LinkFor_HostName]
    ,'/Orion/StatusIcon.ashx?entity=Orion.VIM.Hosts.Vmware&id=0&status='+ TOSTRING(VirtualMachines.Host.Status) +'&size=Small/' AS [_IconFor_HostName]
    ,GuestState
    ,IPAddress
    --,GuestVmWareToolsVersion
    --,GuestVmWareToolsStatus
    ,GuestName
    --,GuestFamily
    --,NicCount
    ,LastActivityDate
    ,VirtualDiskDateModified
    --,NetworkUsageRate
    --,NetworkTransmitRate
    --,NetworkReceiveRate
    
    FROM Orion.VIM.VirtualMachines
    --WHERE HostID IN (SELECT HostID FROM Orion.VIM.Hosts WHERE ClusterID=4)

    Which gives me a nice and easy way to quickly see the things I'm usually asked about.

Reply
  • This is the SWQL I use to view our VM stuff.

    SELECT
    Name AS [VM]
    ,DetailsUrl AS [_LinkFor_VM]
    ,'/Orion/StatusIcon.ashx?entity=Orion.VIM.VirtualMachines.Vmware&status='+ TOSTRING(VirtualMachines.Status) +'&size=Small/' AS [_IconFor_VM]
    ,CASE
        WHEN NodeID > 0 THEN 'YES'
    	ELSE 'NO'
    END AS [Monitored]
    ,'/Orion/Admin/Accounts/images/icons/' +
    	CASE
    		WHEN NodeID IS NULL THEN 'disable'
    		ELSE 'ok_enabled'
    	END +
    + '.png' AS [_IconFor_Monitored]
    
    ,VirtualMachines.Host.Cluster.Name AS Cluster
    ,VirtualMachines.Host.Cluster.DetailsUrl AS [_LinkFor_Cluster]
    ,'/Orion/StatusIcon.ashx?entity=Orion.VIM.Clusters&id=0&status='+ TOSTRING(VirtualMachines.Host.Cluster.Status) +'&size=Small/' AS [_IconFor_Cluster]
    ,VirtualMachines.Host.HostName
    ,VirtualMachines.Host.DetailsUrl AS [_LinkFor_HostName]
    ,'/Orion/StatusIcon.ashx?entity=Orion.VIM.Hosts.Vmware&id=0&status='+ TOSTRING(VirtualMachines.Host.Status) +'&size=Small/' AS [_IconFor_HostName]
    ,GuestState
    ,IPAddress
    --,GuestVmWareToolsVersion
    --,GuestVmWareToolsStatus
    ,GuestName
    --,GuestFamily
    --,NicCount
    ,LastActivityDate
    ,VirtualDiskDateModified
    --,NetworkUsageRate
    --,NetworkTransmitRate
    --,NetworkReceiveRate
    
    FROM Orion.VIM.VirtualMachines
    --WHERE HostID IN (SELECT HostID FROM Orion.VIM.Hosts WHERE ClusterID=4)

    Which gives me a nice and easy way to quickly see the things I'm usually asked about.

Children
No Data