Reports: Infrastructure Summary Past 30 Days by Host.sql

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

The attached/below report provides you with 30 day min, avg and max utilization values for all resources on all hosts.

 

 

 

Select

Host
, round(min(MinMem),2) as 'Min Mem Util%'
, round(avg(AvgMem),2) as 'Avg Mem Util%'
, round(max(MaxMem),2) as 'Max Mem Util%'
, round(min(MinCPU),2) as 'Min CPU Util%'
, round(avg(AvgCPU),2) as 'Avg CPU Util%'
, round(max(MaxCPU),2) as 'Max CPU Util%'
, round(min(MinRdyQ1),2) as 'Min 1CPU Rdy Util%'
, round(avg(AvgRdyQ1),2) as 'Avg 1CPU Rdy Util%'
, round(max(MaxRdyQ1),2) as 'Max 1CPU Rdy Util%'
, round(min(MinRdyQ2),2) as 'Min 2CPU Rdy Util%'
, round(avg(AvgRdyQ2),2) as 'Avg 2CPU Rdy Util%'
, round(max(MaxRdyQ2),2) as 'Max 2CPU Rdy Util%'
, round(min(MinRdyQ4),2) as 'Min 4CPU Rdy Util%'
, round(avg(AvgRdyQ4),2) as 'Avg 4CPU Rdy Util%'
, round(max(MaxRdyQ4),2) as 'Max 4CPU Rdy Util%'
, round(min(MinRdyQ8),2) as 'Min 8CPU Rdy Util%'
, round(avg(AvgRdyQ8),2) as 'Avg 8CPU Rdy Util%'
, round(max(MaxRdyQ8),2) as 'Max 8CPU Rdy Util%'
, round(min(MinBallooning),2) as 'Min Ballooning Util%'
, round(avg(AvgBallooning),2) as 'Avg Ballooning Util%'
, round(max(MaxBallooning),2) as 'Max Ballooning Util%'
, round(min(MinSwapping),2) as 'Min Swapping Util%'
, round(avg(AvgSwapping),2) as 'Avg Swapping Util%'
, round(max(MaxSwapping) ,2) as 'Max Swapping Util%'
, round(min(MinIO),2) as 'Min IO Util%'
, round(avg(AvgIO),2) as 'Avg IO Util%'
, round(max(MaxIO),2) as 'Max IO Util%'
, round(min(MinNet),2) as 'Min Net Util%'
, round(avg(AvgNet),2) as 'Avg Net Util%'
, round(max(MaxNet),2) as 'Max Net Util%'

from

(select
p.snapshot_time
, e.display_name as Host
, MAX(if(p.property_type = 'Mem', p.min_value*100, 0)) as MinMem
, MAX(if(p.property_type = 'Mem', p.avg_value*100, 0)) as AvgMem
, MAX(if(p.property_type = 'Mem', p.max_value*100, 0)) as MaxMem
, MAX(if(p.property_type = 'CPU', p.min_value*100, 0)) as MinCPU
, MAX(if(p.property_type = 'CPU', p.avg_value*100, 0)) as AvgCPU
, MAX(if(p.property_type = 'CPU', p.max_value*100, 0)) as MaxCPU
, MAX(if(p.property_type = 'Q1VCPU', p.min_value*100, 0)) as MinRdyQ1
, MAX(if(p.property_type = 'Q1VCPU', p.avg_value*100, 0)) as AvgRdyQ1
, MAX(if(p.property_type = 'Q1VCPU', p.max_value*100, 0)) as MaxRdyQ1
, MAX(if(p.property_type = 'Q2VCPU', p.min_value*100, 0)) as MinRdyQ2
, MAX(if(p.property_type = 'Q2VCPU', p.avg_value*100, 0)) as AvgRdyQ2
, MAX(if(p.property_type = 'Q2VCPU', p.max_value*100, 0)) as MaxRdyQ2
, MAX(if(p.property_type = 'Q4VCPU', p.min_value*100, 0)) as MinRdyQ4
, MAX(if(p.property_type = 'Q4VCPU', p.avg_value*100, 0)) as AvgRdyQ4
, MAX(if(p.property_type = 'Q4VCPU', p.max_value*100, 0)) as MaxRdyQ4
, MAX(if(p.property_type = 'Q8VCPU', p.min_value*100, 0)) as MinRdyQ8
, MAX(if(p.property_type = 'Q8VCPU', p.avg_value*100, 0)) as AvgRdyQ8
, MAX(if(p.property_type = 'Q8VCPU', p.max_value*100, 0)) as MaxRdyQ8
, MAX(if(p.property_type = 'Ballooning', p.min_value*100, 0)) as MinBallooning
, MAX(if(p.property_type = 'Ballooning', p.avg_value*100, 0)) as AvgBallooning
, MAX(if(p.property_type = 'Ballooning', p.max_value*100, 0)) as MaxBallooning
, MAX(if(p.property_type = 'Swapping', p.min_value*100, 0)) as MinSwapping
, MAX(if(p.property_type = 'Swapping', p.avg_value*100, 0)) as AvgSwapping
, MAX(if(p.property_type = 'Swapping', p.max_value*100, 0)) as MaxSwapping
, MAX(if(p.property_type = 'IOThroughput', p.min_value*100, 0)) as MinIO
, MAX(if(p.property_type = 'IOThroughput', p.avg_value*100, 0)) as AvgIO
, MAX(if(p.property_type = 'IOThroughput', p.max_value*100, 0)) as MaxIO
, MAX(if(p.property_type = 'NetThroughput', p.min_value*100, 0)) as MinNet
, MAX(if(p.property_type = 'NetThroughput', p.avg_value*100, 0)) as AvgNet
, MAX(if(p.property_type = 'NetThroughput', p.max_value*100, 0)) as MaxNet

from pm_stats_by_day p
LEFT JOIN entities e on p.uuid = e.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 = p.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 p.property_subtype = 'utilization'
and snapshot_time >= date_sub(curdate(), interval 30 day)
and e.display_name is not null
group by p.snapshot_time, e.uuid) as a


Group By 1

Order by 1 asc

Outcomes