AnsweredAssumed Answered

How can I create custom Right Sizing report with group information?

Question asked by gregg.carroll@broadridge.com on Aug 16, 2017
Latest reply on Oct 31, 2017 by will.searle

I'd like to run VM Rightsizing reports that contain their custom group information so I can eventually use filters in an Excel file.  I found an article that had a SQL query called rightsize20150811.sql but when I use this as a custom report it fails with a general error.

 

SELECT 
IF(internal_name like 'GROUP-VMsByOrgVDC_%', 'Org VDC', 
IF(internal_name like 'GROUP-USER-%', 'User Group', 
IF(internal_name like 'GROUP-VC-ANN-%', 'VC Annotation', 
IF(internal_name like 'GROUP-VMsByCluster_%', 'Cluster', 'Other')))) as 'Group Type',
IFNULL(group_name, '') as 'Group Name',
IFNULL(display_name, '') as 'VM Name', 
# round(IFNULL(vcpu_util.capacity, 0)) as 'CPU Capacity', 
round(IFNULL(vcpu_num.num_cpu, 0)) as 'vCPU #', 
round(IFNULL(vcpu_util.avg_value, 0) * 100, 2) as 'Avg vCPU %', 
round(IFNULL(vcpu_util.max_value, 0) * 100, 2) as 'Max vCPU %', 
# round(IFNULL(vcpu_util.capacity, 0) * IFNULL(vcpu_util.max_value, 0), 2) AS 'Max CPU Mhz', 
IFNULL(vCPUChangeString, 'No Change') as 'vCPU Recommendation', 
IFNULL(vCPUfromString, '') as 'vCPU From', 
IFNULL(vCPUtoString, '') as 'vCPU To', 
# round(IFNULL(vmem_util.capacity, 0)) as 'Mem Capacity', 
round(IFNULL(vmem_util.capacity, 0) / 1024 / 1024) as 'vMem (Gb)', 
round(IFNULL(vmem_util.avg_value, 0) * 100, 2) as 'Avg vMem %', 
round(IFNULL(vmem_util.max_value, 0) * 100, 2) as 'Max vMem %', 
# round(IFNULL(vmem_util.capacity, 0) * IFNULL(vmem_util.max_value, 0), 2) AS 'Max Mem Used', 
IFNULL(vMEMchangeString, 'No Change') as 'vMem Recommendation', 
IFNULL(vMEMfromString, '') as 'vMem From', IFNULL(vMEMtoString, '') as 'vMem To'
FROM
(SELECT 
distinct uuid
FROM
vm_stats_by_day
WHERE
property_subtype = 'utilization'
AND property_type in ('VMem', 'VCPU')
AND snapshot_time BETWEEN DATE_SUB(DATE(NOW()), INTERVAL 60 DAY) AND DATE_SUB(DATE(NOW()), INTERVAL 1 DAY)) as vms
left join
(SELECT 
uuid,
MAX(capacity) AS capacity,
AVG(avg_value) AS avg_value,
MAX(max_value) AS max_value
FROM
vm_stats_by_day
WHERE
property_subtype = 'utilization'
AND property_type = 'VCPU'
AND snapshot_time BETWEEN DATE_SUB(DATE(NOW()), INTERVAL 60 DAY) AND DATE_SUB(DATE(NOW()), INTERVAL 1 DAY)
GROUP BY uuid) as vcpu_util
on vms.uuid = vcpu_util.uuid
left join
(SELECT 
uuid,
MAX(max_value) AS num_cpu
FROM
vm_stats_by_day
WHERE
property_subtype = 'numVCPUs'
AND property_type = 'numVCPUs'
AND snapshot_time BETWEEN DATE_SUB(DATE(NOW()), INTERVAL 60 DAY) AND DATE_SUB(DATE(NOW()), INTERVAL 1 DAY)
GROUP BY uuid) as vcpu_num
on vms.uuid = vcpu_num.uuid
left join 
(SELECT 
uuid,
MAX(capacity) AS capacity,
AVG(avg_value) AS avg_value,
MAX(max_value) AS max_value
FROM
vm_stats_by_day
WHERE
property_subtype = 'utilization'
AND property_type = 'VMem'
AND snapshot_time BETWEEN DATE_SUB(DATE(NOW()), INTERVAL 60 DAY) AND DATE_SUB(DATE(NOW()), INTERVAL 1 DAY)
GROUP BY uuid) as vmem_util
on vms.uuid = vmem_util.uuid
left join
(SELECT uuid, 
instance_name, 
Replace(Substring_index(Substring_index(explanation, '"Explanation":',-1), ' ', 3), '"', '') AS vCPUchangeString,
Replace(Substring_index(Substring_index(explanation, '"From":',-1), '"', 2), '"', '') AS vCPUfromString,
Replace(Substring_index(Substring_index(explanation, '"To":',-1), '"', 2), '"', '') AS vCPUtoString
FROM ( 
SELECT 'VirtualMachine' AS class_name, 
display_name AS instance_name, 
uuid, 
value AS explanation 
FROM ( 
SELECT id, 
display_name, 
uuid 
FROM entities 
WHERE creation_class = 'VirtualMachine' ) AS entities 
natural JOIN 
( 
SELECT entity_entity_id AS id, 
value 
FROM entity_attrs 
WHERE NAME = 'RightsizingInfo' ) AS entity_attrs 
WHERE value LIKE '% vCpu %') AS entity_info where explanation not like '%limit%') as vcpu_resizes 
on vms.uuid = vcpu_resizes.uuid
left join
(SELECT uuid, 
instance_name, 
Replace(Substring_index(Substring_index(explanation, '"Explanation":',-1), ' ', 3), '"', '') AS vMEMchangeString,
Replace(Substring_index(Substring_index(explanation, '"From":',-1), '"', 2), '"', '') AS vMEMfromString,
Replace(Substring_index(Substring_index(explanation, '"To":',-1), '"', 2), '"', '') AS vMEMtoString
FROM ( 
SELECT 'VirtualMachine' AS class_name, 
display_name AS instance_name, 
uuid, 
value AS explanation 
FROM ( 
SELECT id, 
display_name, 
uuid 
FROM entities 
WHERE creation_class = 'VirtualMachine' ) AS entities natural 
JOIN 
( 
SELECT entity_entity_id AS id, 
value 
FROM entity_attrs 
WHERE NAME = 'RightsizingInfo' ) AS entity_attrs 
WHERE value LIKE '% vMem %' ) AS entity_info where explanation not like '%limit%') as vmem_resizes
on vms.uuid = vmem_resizes.uuid
left join
(SELECT group_name,
internal_name,
member_uuid AS uuid 
FROM 
( 
SELECT uuid AS group_uuid, 
display_name AS group_name 
FROM entities 
WHERE creation_class IN ('Group')
) AS clusterfoldergroups 
NATURAL JOIN 
( 
SELECT group_uuid, 
member_uuid, 
display_name,
internal_name
FROM entity_group_members 
WHERE group_type = 'VirtualMachine'
) AS grp_members 
) AS group_info
on vms.uuid = group_info.uuid
left join entities on vms.uuid = entities.uuid 
WHERE display_name IS NOT NULL
and internal_name is not null
and (internal_name like 'GROUP-VMsByCluster_%' 
or internal_name like 'GROUP-VMsByOrgVDC_%'
or internal_name like 'GROUP-USER-%'
or internal_name like 'GROUP-VC-ANN-%')
order by 'Group Type' asc , 'Group Name', 'VM Name' asc;
#order by 'VM Name';

Attachments

Outcomes