AnsweredAssumed Answered

how do you add group information to rightsizing reports?

Question asked by gregg.carroll@broadridge.com on Aug 16, 2017

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