Finding Unused VMs in VMware

With the great capabilities of VMware, comes one of the drawback – VM sprawl. On a whim, someone can call IT and spin up a VM (or a dozen VMs), but then immediately forget about them. There are a great many tools that help track this – assigning VMs to users and automating the reclamation cycle – but those tools only work after they have been installed.

So, you have a VM farm with 2,000 virtual machines and you are running out of resources – how can you identify which ones to reclaim? There are various ways to track who was the last person to log into each system, and then hunt them down for details about it, and hopefully they are willing to take responsibility for the machine and state if it can be reclaimed or not. But for 2,000 VMs, that would take forever, there must be an easier method to target only questionable systems….

Enter Virtual Center performance metrics. By default, Virtual Center gathers several metrics about every VM in its environment. Of these, 3 are particularly useful for this problem: Network KBps, Disk KBps, and CPU MHz. These metrics help us determine how many KB of network traffic has occurred, how many KB of information was read/written from the guest OS, and how many MHz the VM used. Virtual Center retains this information for daily, weekly, monthly, and even yearly periods.

Using the below TSQL script, I was able to gather basic information about each virtual machine, as well as summarized performance information for the Disk, Network, and CPU. Sorting this data from smallest-to-largest allows you to quickly identify VMs that may not be used at all, or possibly are under used and can be consolidated.

select vms.VMID, vms.NAME, vms.MEM_SIZE_MB, vms.NUM_VCPU, vms.BOOT_TIME, vms.POWER_STATE, vms.GUEST_OS, vms.DNS_NAME, vms.IP_ADDRESS, moid.MOID
,(select SUM(hsd.STAT_VALUE)
from VPXV_HIST_STAT_MONTHLY hsd (NOLOCK)
where hsd.Entity = moid.MOID
and hsd.STAT_GROUP=’disk’
and hsd.STAT_NAME=’usage’
and hsd.STAT_ROLLUP_TYPE=’average’) as ‘DiskKB’
,(select SUM(hsd.STAT_VALUE)
from VPXV_HIST_STAT_MONTHLY hsd (NOLOCK)
where hsd.Entity = moid.MOID
and hsd.STAT_GROUP=’net’
and hsd.STAT_NAME=’usage’
and hsd.STAT_ROLLUP_TYPE=’average’) as ‘NetKB’
,(select SUM(hsd.STAT_VALUE)
from VPXV_HIST_STAT_MONTHLY hsd (NOLOCK)
where hsd.Entity = moid.MOID
and hsd.STAT_GROUP=’cpu’
and hsd.STAT_NAME=’usagemhz’
and hsd.STAT_ROLLUP_TYPE=’average’) as ‘CPUMHz’
from vpxv_vms vms (NOLOCK)
INNER JOIN VPXV_ENTITY_MOID moid (NOLOCK) on vms.VMID=moid.ENTITYID
where vms.POWER_STATE=’On’