Is there any query available to fetch Average CPU and Mem Utilization along with 95th Percentile for all Clusters for a week or month?
Basically the output should be like
Cluster name Avg CPU 95th Perc CPU Avg Mem 95th Perc Mem
If you are looking for cluster stats instead of just vm stats try the following:
ClusterName as 'Cluster'
, round(avg(CPU),2) as 'Weekly Average CPU Utilization%'
, round(avg(Mem),2) as 'Weekly Average Mem Utilization%'
, max(CPU) as '95th Percentile CPU Utilization%'
, max(Mem) as '95th Percentile Mem Utilization%'
, substring(e.display_name,5) ClusterName
, max(if(property_type = 'CPU', value*100,0)) as CPU
, max(if(property_type = 'Mem', value*100,0)) as Mem
from cluster_stats_by_day c
join entities e on c.internal_name = e.name
where name like 'GROUP-PMsByCluster_%'
and c.recorded_on >= date_sub(curdate(), interval 7 day)
and c.property_subtype = 'utilization'
group by c.recorded_on, e.display_name
) as A
group by 1
order by 1 asc
Not exactly what you are looking for, but the below sql query could give you a feel for the structure to get to the result you are after.
Hope this helps..
vm.snapshot_time as Date,
grp.display_name as VM_Name,
max(if(vm.property_type='VCPU', vm.capacity, 0)) as VCPU_Capacity_in_MHz,
max(if(vm.property_type='VCPU', vm.avg_value, 0))*100 as Avg_VCPU_Percent,
max(if(vm.property_type='VCPU', vm.max_value, 0))*100 as Max_VCPU_Percent,
max(if(vm.property_type='VMem', vm.capacity, 0))/1024 as VMem_Capacity_in_MB,
max(if(vm.property_type='VMem', vm.avg_value, 0))*100 as Avg_VMem_Percent,
max(if(vm.property_type='VMem', vm.max_value, 0))*100 as Max_VMem_Percent
from vm_stats_by_month as vm
join vm_group_members grp ON vm.uuid=grp.member_uuid
where vm.property_type in ('VCPU','VMem')
and vm.snapshot_time >= date_sub(date(now()), interval 1 month)
and grp.group_name= "Customer's Name"
order by vm.snapshot_time , grp.display_name;
Retrieving data ...