SQL Query for VMTurbo Report


select
distinct(vminst.display_name),
vmgrps.group_name as OS,
vmvstor.VStorage_Capacity_in_MB,
vmvstor.VStorage_Used_in_MB,
vmvstor.VStorage_Used_Percent,
vmvCPU.Num_of_VCPUs,
vmvCPUUsed.VCPU_Capacity_MHZ,
vmvCPUUsed.VCPU_Avg_MHZ,
vmvCPUUsed.VCPU_Peak_MHZ,
vmvMem.VMem_Capacity_MB,
vmvMem.VMem_Avg_MB,
vmvMem.VMem_Peak_MB,
vmvstor.Date
from
(select
uuid,
FORMAT(capacity,2) as VStorage_Capacity_in_MB,
FORMAT((avg_value*capacity),2) as VStorage_Used_in_MB,
FORMAT((avg_value*100),2) as VStorage_Used_Percent,
snapshot_time as 'Date'
from vm_stats_by_day
where property_type="VStorage" and property_subtype='utilization' and to_days(snapshot_time) >= to_days(date_sub(now(), interval 1 day))
group by uuid)
as vmvstor
join
(select
uuid,
FORMAT(max_value, 0) as 'Num_of_VCPUs',
snapshot_time
from vm_stats_by_day
where property_type="NumVCPUs" and to_days(snapshot_time) >= to_days(date_sub(now(), interval 1 day))
order by uuid, snapshot_time)
as vmvCPU
on vmvCPU.uuid = vmvstor.uuid
join
(select
uuid,
FORMAT(capacity,2) as 'VCPU_Capacity_MHZ',
FORMAT(avg_value,2) as 'VCPU_Avg_MHZ',
FORMAT(max_value,2) as 'VCPU_Peak_MHZ',
snapshot_time
from vm_stats_by_day
where property_type="VCPU" and property_subtype="used" and to_days(snapshot_time) >= to_days(date_sub(now(), interval 1 day))
order by uuid, snapshot_time)
as vmvCPUUsed
on vmvCPUUsed.uuid = vmvstor.uuid
join
(select
uuid,
FORMAT((capacity/1024),2) as 'VMem_Capacity_MB',
FORMAT((avg_value/1024),2) as 'VMem_Avg_MB',
FORMAT((max_value/1024),2) as 'VMem_Peak_MB',
snapshot_time
from vm_stats_by_day
where property_type="VMem" and property_subtype="used" and to_days(snapshot_time) >= to_days(date_sub(now(), interval 1 day))
order by uuid, snapshot_time)
as vmvMem
on vmvMem.uuid = vmvstor.uuid
join
(select
uuid,
display_name as 'display_name'
from vm_instances)
as vminst
on vmvstor.uuid = vminst.uuid
join
(select member_uuid, group_name
from vm_group_members
where internal_name like 'GROUP-USER-%' and group_name like 'Windows %' or group_name like 'linux')
as vmgrps
on vmvCPU.uuid = vmgrps.member_uuid

Leave a Reply

Your email address will not be published. Required fields are marked *