I'm trying to write a script which returns a list of all the Pending actions from the VMTDB MySQL database.
For example, If I look at the UI overview I can see I have a total of 546 Pending actions made up of 300 scaling actions and 246 delete actions.
I've come up with the following script which returns 162 rows so i'm wondering (without going through a line by line comparison) why I have "missing" rows.
As we have multiple Azure subscription targets it would also be nice to be able to group the results by subscription. But for now i'll be happy to just return the correct data.
This is what i've got so far:
c.display_name, d.name, a.severity, a.category, a.description, MAX(a.last_notify_time), b.details, b.savings
notifications a, actions b, entities c, action_types d
a.uuid = b.target_object_uuid and
a.id = b.notification_id and
b.update_time is NULL and
b.clear_time is NULL and
a.uuid = c.uuid and
b.action_type = d.id
GROUP BY a.last_notify_time
ORDER BY b.savings DESC;
Any ideas? Anyone tried something similar ?