Reports: Average Drive Utilization Percent Changes Month Over Month (Past 3 Months)

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

This report will provide you with month over month percent change of every VM drive/partition in the environment.

 

Currently this is only looking across the past 3 months so it will be calculating the percent change from 3 months ago to 2 months ago then from 2 months ago to last month. If you would like to expand this time range just reach out to us here and we can do so.

 

Query is pasted after the sample output if copy/paste is preferred over downloading and saving the file.

 

Sample output:

 

 

Query:

 

select
distinct
VM_Name2 as VM,
VM_Disk_Drive2 as 'Drive Type',
if(
round((if(VM_Disk_Drive2 = VM_Disk_Drive1, ADUP1-ADUP2, 0)),2)<0
, round(min(if(VM_Disk_Drive2 = VM_Disk_Drive1, ADUP1-ADUP2, 0)),2)
, round(max(if(VM_Disk_Drive2 = VM_Disk_Drive1, ADUP1-ADUP2, 0)),2)) as 'Avg Drive Utilization %Change from 2 Months Prior to Last Month',

if(
round((if(VM_Disk_Drive3 = VM_Disk_Drive2, ADUP2-ADUP3, 0)),2)<0
, round(min(if(VM_Disk_Drive3 = VM_Disk_Drive2, ADUP2-ADUP3, 0)),2)
, round(max(if(VM_Disk_Drive3 = VM_Disk_Drive2, ADUP2-ADUP3, 0)),2)) as 'Avg Drive Utilization %Change from 3 Months Prior to 2 Months Prior'


from

(
select distinct
vm1.snapshot_time as Month1,
e1.uuid as uuid1,
e1.display_name as VM_Name1,
vm1.commodity_key as VM_Disk_Drive1,
avg(vm1.avg_value)*100 as ADUP1,
Max(vm1.max_value)*100 as MDUP1
from vm_stats_by_month as vm1
LEFT JOIN entities e1 on e1.uuid = vm1.uuid
LEFT JOIN entity_assns_members_entities eame1 ON eame1.entity_dest_id = e1.id
LEFT JOIN entity_assns eas1 ON eas1.id = eame1.entity_assn_src_id
LEFT JOIN entities grp1 ON grp1.id = eas1.entity_entity_id

LEFT JOIN entities en1 on en1.uuid = vm1.uuid
LEFT JOIN entity_assns_members_entities eamee1 ON eamee1.entity_dest_id = en1.id
LEFT JOIN entity_assns eass1 ON eass1.id = eamee1.entity_assn_src_id
LEFT JOIN entities hst1 ON hst1.id = eass1.entity_entity_id

WHERE (hst1.name like 'GROUP-VMs\_%'
and vm1.property_type = 'VStorage'
and vm1.property_subtype='utilization'
and month(vm1.snapshot_time) = month(current_date - interval 1 month)
and e1.display_name is not null
and vm1.commodity_key is not null
)
group by vm1.snapshot_time, e1.display_name, vm1.commodity_key

) as M1

JOIN

(select distinct
vm2.snapshot_time as Month2,
e2.uuid as uuid2,
e2.display_name as VM_Name2,
vm2.commodity_key as VM_Disk_Drive2,
avg(vm2.avg_value)*100 as ADUP2,
Max(vm2.max_value)*100 as MDUP2
from vm_stats_by_month as vm2
LEFT JOIN entities e2 on e2.uuid = vm2.uuid
LEFT JOIN entity_assns_members_entities eame2 ON eame2.entity_dest_id = e2.id
LEFT JOIN entity_assns eas2 ON eas2.id = eame2.entity_assn_src_id
LEFT JOIN entities grp2 ON grp2.id = eas2.entity_entity_id

LEFT JOIN entities en2 on en2.uuid = vm2.uuid
LEFT JOIN entity_assns_members_entities eamee2 ON eamee2.entity_dest_id = en2.id
LEFT JOIN entity_assns eass2 ON eass2.id = eamee2.entity_assn_src_id
LEFT JOIN entities hst2 ON hst2.id = eass2.entity_entity_id

WHERE (hst2.name like 'GROUP-VMs\_%'
and vm2.property_type = 'VStorage'
and vm2.property_subtype='utilization'
and month(vm2.snapshot_time) = month(current_date - interval 2 month)
and e2.display_name is not null
and vm2.commodity_key is not null
)
group by vm2.snapshot_time, e2.display_name, vm2.commodity_key

) as M2 on M1.uuid1 = M2.uuid2

JOIN

(select distinct
vm3.snapshot_time as Month3,
e3.uuid as uuid3,
e3.display_name as VM_Name3,
vm3.commodity_key as VM_Disk_Drive3,
avg(vm3.avg_value)*100 as ADUP3,
Max(vm3.max_value)*100 as MDUP3
from vm_stats_by_month as vm3
LEFT JOIN entities e3 on e3.uuid = vm3.uuid
LEFT JOIN entity_assns_members_entities eame3 ON eame3.entity_dest_id = e3.id
LEFT JOIN entity_assns eas3 ON eas3.id = eame3.entity_assn_src_id
LEFT JOIN entities grp3 ON grp3.id = eas3.entity_entity_id

LEFT JOIN entities en3 on en3.uuid = vm3.uuid
LEFT JOIN entity_assns_members_entities eamee3 ON eamee3.entity_dest_id = en3.id
LEFT JOIN entity_assns eass3 ON eass3.id = eamee3.entity_assn_src_id
LEFT JOIN entities hst3 ON hst3.id = eass3.entity_entity_id

WHERE (hst3.name like 'GROUP-VMs\_%'
and vm3.property_type = 'VStorage'
and vm3.property_subtype='utilization'
and month(vm3.snapshot_time) = month(current_date - interval 3 month)
and e3.display_name is not null
and vm3.commodity_key is not null
)
group by vm3.snapshot_time, e3.display_name, vm3.commodity_key
) as M3 on M2.uuid2 = M3.uuid3

group by VM_Name2, VM_Disk_Drive2

Outcomes