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
  • Start from the Orion.VIM.VirtualMachines table and work from there. Try the query below for the names of the hosts/clusters/vm's. Build as you see fit.

    SELECT vm.Node.Caption, vm.Host.node.Caption, vm.Host.Cluster.Name
    FROM Orion.VIM.VirtualMachines vm

  • Thanks. That at least gets me the linked tables.

  • Although if someone still wants to help further with joining, i would like to get the datacentre..

    SELECT vm.GuestDnsName as DNSName, vm.Node.Caption as VM, vm.Host.node.Caption as Host, vm.Host.Cluster.Name as Cluster, vm.GuestState as Status, vm.GuestName as GuestName, vm.IPAddress as IP, hosts.DataCenterID
    FROM Orion.VIM.VirtualMachines vm, Orion.VIM.Hosts as Hosts, Orion.VIM.DataCenters as DataCenters
    INNER JOIN DataCenters.DataCenterID ON Hosts.DataCenterID AND Hosts.HostName ON DataCenters.Hosts

  • I may be misunderstanding your request, but to get the DataCenter name just navigate from the VirtualMachines entity over to the DataCenter entity in the same manner. 

    SELECT vm.Node.Caption, vm.Host.node.Caption, vm.Host.Cluster.Name, vm.Host.DataCenter.Name
    FROM Orion.VIM.VirtualMachines vm

    This article should help explain the navigation properties and how they link entities together. It makes life a whole lot easier and eliminates joins in many cases.

    support.solarwinds.com/.../Use-SolarWinds-Query-Language-SWQL

  • I'm just dumb and didn't see that field in hosts.. Your right it has what I wanted..

    SELECT vm.GuestDnsName as DNSName, vm.Node.Caption as VM, vm.Host.node.Caption as Host, vm.Host.Cluster.Name as Cluster, vm.GuestState as Status, vm.GuestName as GuestName, vm.IPAddress as IP, vm.Host.DataCenter.Name as DataCenter FROM Orion.VIM.VirtualMachines vm
  • And that link is super helpful! not sure why i didn't come across it in all my searches.

  • 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.