Does anyone have a query to pull out all the powered OFF Vm's list by last accessed/powered off date ?
I don't know if you will be able to get the exact date that a VM was shut off or not.
However - this report will give you the last month (max) that a VM had activity for ones that didn't have it over 3 months.
Hopefully this helps.
SELECT DATE_FORMAT(MAX(VMSBM.snapshot_time), '%Y/%m') AS 'Last Seen On YYYY/MM'
,clsmem.display_name AS 'Virtual Machine'
FROM vm_stats_by_month VMSBM
LEFT JOIN cluster_members clsmem ON clsmem.member_uuid = VMSBM.uuid AND clsmem.recorded_on = VMSBM.snapshot_time
WHERE clsmem.group_name = 'VMs'
AND VMSBM.property_type = 'priceIndex'
AND VMSBM.snapshot_time < end_of_month(DATE_SUB(CURDATE(), INTERVAL 3 MONTH))
AND VMSBM.uuid NOT IN (SELECT DISTINCT uuid
WHERE property_type = 'priceIndex'
AND snapshot_time > end_of_month(DATE_SUB(CURDATE(), INTERVAL 3 MONTH))
GROUP BY clsmem.display_name
ORDER BY 1
Retrieving data ...