Control: EMR clusters idle for more than 30 minutes should be reviewed

powerpipe control run aws_thrifty.control.emr_cluster_is_idle_30_minutes

powerpipe login
powerpipe control run aws_thrifty.control.emr_cluster_is_idle_30_minutes --share

with cluster_metrics as (
  select
    id,
    maximum,
    date(timestamp) as timestamp
  from
    aws_emr_cluster_metric_is_idle
  where
    timestamp >= current_timestamp - interval '40 minutes'
),
  emr_cluster_isidle as (
    select
      id,
      count(maximum) as count,
      sum(maximum)/count(maximum) as avagsum
    from
      cluster_metrics
    group by id, timestamp
  )
  select
    i.id as resource,
    case
      when u.id is null then 'error'
      when avagsum = 1 and count >= 7  then 'alarm'
      else 'ok'
    end as status,
    case
      when u.id is null then 'CloudWatch metrics not available for ' || i.title || '.'
      else i.title || ' is idle from last ' || (count*5 - 5) ||  ' minutes.'
    end as reason
    
    , i.region, i.account_id
  from
    aws_emr_cluster as i
    left join emr_cluster_isidle as u on u.id = i.id;