Detect & correct ElastiCache clusters exceeding max age

ElastiCache clusters have an ongoing operational cost, so clusters that surpass a certain age should be retired to prevent unnecessary expenses.

This query trigger identifies ElastiCache clusters exceeding the maximum age and then either sends a notification or attempts to perform a predefined corrective action.

Getting Started

By default, this trigger is disabled, but it can be configured by setting the following variables:

elasticache_clusters_exceeding_max_age_trigger_enabled should be set to true as the default is false.
elasticache_clusters_exceeding_max_age_trigger_schedule should be set to your preferred schedule.
elasticache_clusters_exceeding_max_age_default_action should be set to the desired action (e.g., "notify" for notifications or "delete_cluster" to delete the cluster).

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 Elasticache clusters exceeding max age

Query

with filter_clusters as (
  select
    distinct c.replication_group_id as name,
    c.cache_cluster_create_time,
    c._ctx,
    c.region,
    c.account_id,
    'redis' as engine,
    c.partition
  from
    aws_elasticache_replication_group as rg
    left join aws_elasticache_cluster as c on rg.replication_group_id = c.replication_group_id
  union
  select
    cache_cluster_id as name,
    cache_cluster_create_time,
    _ctx,
    region,
    account_id,
    engine,
    partition
  from
    aws_elasticache_cluster
  where
    engine = 'memcached'
)
select
  concat(name, ' [', region, '/', account_id, ']') as title,
  name,
  region,
  account_id,
  _ctx ->> 'connection_name' as cred
from
  filter_clusters
where
  date_part('day', now() - cache_cluster_create_time) > 90;

Schedule

15m