Reports: Idle VMs for Specified Group

File uploaded by robert.coppersmith on Apr 12, 2017
Version 1Show Document
  • View in full screen mode

This report will provide you with the following data points if either the MAX vCPU or vMem utilization in the past 60 days is less than 30%.

 

VMName

HostName

GroupName

vCPU Allocation 

vCPU Utilization (Max as seen in past 60 days)

vMem Allocation

vMem Utilization (Max as seen in past 60 days)

 

You will need to specify the name of the Turbonomic group that you would like to run this for, instructions on where to edit the Group Name are in the query itself but to provide additional clarity you'll want to find the section that looks like this:

 

/*
replace TEST with your custom group name in the line below, LEAVE THE ' ' AROUND THE GROUP NAME THAT YOU ENTER i.e. if your group name is Custom SQL Group1, the below should say 'Custom SQL Group1'
*/
'TEST')

 

You'll want to just switch out 'TEST' with your group name as mentioned in the comments.

 

The results returned will also only include VMs have been in the environment for longer than 60 days, if you would like to adjust that logical criteria just reach out to us here and we can do so.

 

You can however, easily adjust which max utilization value you would like to use at the bottom of the report in the WHERE a.vcpu < 30 or a.vmem <30 line.

 

So for example if you prefer to see results returned if the max utilization is less than 10% then just adjust that "WHERE" statement above to the following WHERE a.vcpu < 10 or a.vmem <10

 

Let us know if there are any questions, I have copied the query below as well if you would prefer to copy and paste rather than download the file.

 


select
a.VirtualMachineName as VMName
, a.HostName as HostName
, a.GroupName as GroupName
, a.numVCPUs as vCPU_Allocation
, a.vcpu as vCPU_Utilization
, a.vMemAlloc as vMem_Allocation
, a.vmem as vMem_Utilization
FROM
(
SELECT
e.display_name as VirtualMachineName
,e.uuid as UID1
,substring(hst.display_name,5) as HostName
,grp.display_name as GroupName
,MAX(IF(vm.property_type = 'VCPU' AND vm.property_subtype = 'utilization', vm.max_value*100, 0)) as vcpu
,MAX(IF(vm.property_type = 'VMem' AND vm.property_subtype = 'utilization', vm.max_value*100, 0)) as vmem
,MAX(IF(vm.property_type = 'numVCPUs', (
select v2.max_value from vm_stats_by_day v2
where v2.property_type = 'numVCPUs'
and v2.uuid = vm.uuid
and v2.snapshot_time >= date_sub(curdate(), interval 1 day)
), 0)) as numVCPUs
,MAX(IF(vm.property_type = 'vMem' and vm.property_subtype = 'used', (
select round((max(v3.capacity)/1024/1024),2) from vm_stats_by_day v3
where v3.property_type = 'vMem'
and v3.property_subtype = 'used'
and v3.uuid = vm.uuid
and v3.snapshot_time >= date_sub(curdate(), interval 1 day)
), 0)) as vMemAlloc
FROM vm_stats_by_day vm
LEFT JOIN entities e on e.uuid = vm.uuid
LEFT JOIN entity_assns_members_entities eame ON eame.entity_dest_id = e.id
LEFT JOIN entity_assns eas ON eas.id = eame.entity_assn_src_id
LEFT JOIN entities grp ON grp.id = eas.entity_entity_id
LEFT JOIN entities en on en.uuid = vm.uuid
LEFT JOIN entity_assns_members_entities eamee ON eamee.entity_dest_id = en.id
LEFT JOIN entity_assns eass ON eass.id = eamee.entity_assn_src_id
LEFT JOIN entities hst ON hst.id = eass.entity_entity_id
WHERE snapshot_time >= date_sub(curdate(), interval 60 day)
AND grp.name = concat('GROUP-USER-',
/*
replace TEST with your custom group name in the line below, LEAVE THE ' ' AROUND THE GROUP NAME THAT YOU ENTER i.e. if your group name is Custom SQL Group1, the below should say 'Custom SQL Group1'
*/
'TEST')
AND hst.name like 'GROUP-VMs\_%'
GROUP BY 1
) as a
JOIN
(
select distinct vm2.uuid as UID2 from vm_stats_by_day vm2
where vm2.snapshot_time between (NOW() - INTERVAL 90 DAY) AND (NOW() - INTERVAL 60 DAY)
) as b ON a.UID1 = b.UID2

WHERE a.vcpu < 30 or a.vmem < 30

Outcomes