Powered off VMs - Time off

Document created by pazychowicz on Oct 31, 2017
Version 1Show Document
  • View in full screen mode

This report identifies VMs that are powered off today, how many days they’ve been powered off for and how much storage it's being using. In addition, notates what cluster they belong to. 

 

 

Sample output:

 

 

SELECT distinct  e.display_name AS 'Name'

,clu.ClusterName

,cur.st AS 'Storage (MB)'

,dur.dur AS 'Days'

FROM (SELECT uuid

,SUM(IF(property_type = 'StorageAmount', avg_value, 0)) AS st

FROM vm_stats_by_day

WHERE property_type IN ('VCPU', 'VMEM', 'StorageAmount')

AND property_subtype = 'used'

AND snapshot_time = SUBDATE(CURDATE(), 1)

GROUP BY 1

HAVING MAX(IF(property_type = 'VCPU', avg_value, 0)) = 0

AND MAX(IF(property_type = 'VMem', avg_value, 0)) = 0

) cur

JOIN entities e ON e.uuid = cur.uuid

 

JOIN (SELECT uuid

,COUNT(snapshot_time) AS dur

,MAX(sa)

FROM (SELECT uuid

,snapshot_time

,MAX(IF(property_type = 'StorageAmount', avg_value, 0)) AS sa

,MAX(IF(property_type = 'VMem', avg_value, 0)) AS vm

,MAX(IF(property_type = 'VCPU', avg_value, 0)) AS vc

FROM vm_stats_by_day

WHERE property_type IN ('VCPU', 'VMEM', 'StorageAmount')

GROUP BY 1, 2

) t

WHERE sa > 0

AND vm = 0

AND vc = 0

GROUP BY 1

) dur ON dur.uuid = cur.uuid

 

JOIN (

SELECT

substring(grp.display_name,5) as ClusterName

,e2.uuid

FROM entities e2

LEFT JOIN entity_assns_members_entities eame ON eame.entity_dest_id = e2.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

where grp.name like 'GROUP-VMsByCluster\_%'

) clu on dur.uuid = clu.uuid

 

 

ORDER BY 3 DESC, 2 DESC, 1 ASC

1 person found this helpful

Attachments

    Outcomes