Thursday, January 7, 2010

Query to Get Paged Out VMs from Virtual Center Database

Brief Description: Query the Virtual Center database for guests with swapped out memory.


Just a quick handy script. In our implementation, we've seen swap out correlate to discrepancies between the Guest and host tools version, and found it handy to pull back tools and build info when querying for swap out. I use the (possibly overly convoluted, corrections are always welcome) query to find VMs which are swapping in the last hour, their tools version, and the build of their parent host.

Written with a table variable to make it clearer if you want to modify which columns are pulled back. Our VMware database records stat sample time in UTC (hence the +6), I'm unclear whether that's always the case.
Use [YourVMwareDB]
declare @tblToolsVer table
strVMName nvarchar(255),
pageout int,
intToolsVer int,
strHostName nvarchar(255),
intHostVer int

insert into @tblToolsVer(strVMName, pageout, intToolsVer, strStatus, strHostName, intHostVer)

select as VMName, CONVERT(decimal(12,2),ROUND(AVG(s.stat_value)/1024,2)) as swapped, vm.tools_version,
case vm.tools_status
when 0 then 'Not Installed'
when 1 then 'Not Running'
when 2 then 'Out of Date'
3 then 'OK'
when NULL then 'Indeterminate'
end as Tools_Status, as Host_Name, ho.Product_Build as Host_Build
from vpx_vm vm
join vpx_entity ev on =
join vpx_entity eh on = vm.host_id
join vpx_host ho on = vm.host_id
join vpxV_hist_stat_daily s on s.entity = ('vm-'+ CONVERT (Varchar, vm.ID))
s.stat_name = 'swapped' and in(
select distinct( 
from vpx_entity h 
join vpxV_hist_stat_daily s on s.entity = ('vm-'+ CONVERT (Varchar, h.ID))
where h.type_id = 0 and s.stat_name =
'swapped' and s.stat_value > 800 and s.sample_time >= dateadd(hh, +6,
group by, vm.tools_version, vm.tools_status,, ho.product_build

select * from @tblToolsVer

No comments:

Post a Comment