Detect & correct Compute virtual machine exceeding max age

Overview

Compute virtual machine can be quite costly to retain, it is also likely that after a certain point in time they're no longer required and should be cleaned up to prevent further costs.

This query trigger detects virtual machines exceeding max age and then either sends a notification or attempts to perform a predefined corrective action.

Getting Started

By default, this trigger is disabled, however it can be configured by setting the below variables

compute_virtual_machines_exceeding_max_age_trigger_enabled should be set to true as the default is false.
compute_virtual_machines_exceeding_max_age_trigger_schedule should be set to your desired running schedule
compute_virtual_machines_exceeding_max_age_default_action should be set to your desired action (i.e. "notify" for notifications or "delete_virtual_machine" to delete the snapshot).

Then starting the server:

flowpipe server

or if you've set the variables in a .fpvars file:

flowpipe server --var-file=/path/to/your.fpvars

Pipeline

Correct Compute virtual machines exceeding max age

Query

select
  concat(
    vm.id,
    ' (',
    vm.title,
    ') [',
    vm.size,
    '/',
    vm.region,
    '/',
    vm.resource_group,
    ']'
  ) as title,
  vm.id,
  vm.name,
  vm.subscription_id,
  vm.resource_group,
  vm.title,
  vm.sp_connection_name as conn
from
  azure_compute_virtual_machine as vm,
  jsonb_array_elements(statuses) as s,
  azure_subscription as sub
where
  sub.subscription_id = vm.subscription_id
  and vm.power_state in ('running', 'starting')
  and s ->> 'time' is not null
  and date_part('day', now() - (s ->> 'time') :: timestamptz) > 90;

Schedule