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.

Solution:

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]
go
declare @tblToolsVer table
(
strVMName nvarchar(255),
pageout int,
intToolsVer int,
strStatus
nvarchar(255),
strHostName nvarchar(255),
intHostVer int
)

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

select ev.name 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'
when
3 then 'OK'
when NULL then 'Indeterminate'
end as Tools_Status,
eh.name as Host_Name, ho.Product_Build as Host_Build
from vpx_vm vm
join vpx_entity ev on ev.id = vm.id
join vpx_entity eh on eh.id = vm.host_id
join vpx_host ho on ho.id = vm.host_id
join vpxV_hist_stat_daily s on s.entity = ('vm-'+ CONVERT (Varchar, vm.ID))
Where
s.stat_name = 'swapped' and ev.id in(
select distinct(h.id) 
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,
getdate())
)
group by ev.name, vm.tools_version, vm.tools_status, eh.name, ho.product_build

select * from @tblToolsVer

No comments:

Post a Comment