亚马逊云科技服务之安全巡检及优化

select
  case
    when prev_month.service_name is null then 'arn:' || base_month.partition || ':::' || base_month.account_id || ':cost/' || base_month.service
    else 'arn:' || prev_month.partition || ':::' || prev_month.account_id || ':cost/' || prev_month.service
  end as resource,
  case
    when base_month.cost is null then 'skip'
    when prev_month.cost is null then 'ok'
    -- adjust this value to change threshold for the alarm
    when (prev_month.cost - base_month.cost) between (base_month.cost * 0.1)  and 50 then 'info'
    when (prev_month.cost - base_month.cost) > (base_month.cost * 0.1) and (prev_month.cost - base_month.cost) > $1 then 'alarm'
    else 'ok'
  end as status

mod "local" {
  title = "Insight-mod-ECR"
}
dashboard "dashboard_total_ec2" {
  title = "ECR Dashboard"
  text {
    value = "ECR will use this dashboard to show you account resource usage and help you optimize your observation resource usage!"
  }
  container {
     title = "AWS Basic Resources Overview"
    # Analysis
    card {
      query = query.ebs_volume_count
      width = 2
    }
    card {
      sql = query.ec2_instance_count.sql
      width = 2
    }
    card {
      query = query.ebs_volume_unattached_count
      width = 2
    }
    card {
      query = query.vpc_count
      width = 2
    }
    card {
      query = query.rds_db_cluster_count
      width = 2
    }
    card {
      query = query.s3_bucket_count
      width = 2
    }
}
  container {
     title = "Analysis"
    chart {
      title = "Instances by State"
      query = query.ec2_instance_by_state
      type  = "donut"
      width = 4
    }
    chart {
      title = "Instances by Region"
      query = query.ec2_instance_by_region
      type  = "column"
      width = 4
    }
    chart {
      title = "Instances by Account"
      query = query.ec2_instance_by_account
      type  = "column"
      width = 4
    }

}
  container {
    title = "Performance & Utilization"
    chart {
      title = "Top 10 CPU - Last 7 days"
      query = query.ec2_top10_cpu_past_week
      type  = "line"
      width = 6
    }
    chart {
      title = "Top 10 CPU - Last 7 days"
      query = query.rds_db_instance_top10_cpu_past_week
      type  = "line"
      width = 6
    }
  }
}

#AWS Basic Resources Overview
query "ebs_volume_count" {
  sql = <<-EOQ
    select
      count(*) as "Volumes"
    from
      aws_ebs_volume;
  EOQ
}
query "ec2_instance_count" {
  sql = <<-EOQ
    select count(*) as "Instances" from aws_ec2_instance
  EOQ
}
query "ebs_volume_unattached_count" {
  sql = <<-EOQ
    select
      count(*) as value,
      'Vol Not In-Use' as label,
      case count(*) when 0 then 'ok' else 'alert' end as "type"
    from
      aws_ebs_volume
    where
      jsonb_array_length(attachments) = 0;
  EOQ
}
query "vpc_count" {
  sql = <<-EOQ
    select count(*) as "VPCs" from aws_vpc;
  EOQ
}
query "rds_db_cluster_count" {
  sql = <<-EOQ
    select count(*) as "DB Clusters" from aws_rds_db_cluster;
  EOQ
}
query "s3_bucket_count" {
  sql = <<-EOQ
    select count(*) as "Buckets" from aws_s3_bucket;
  EOQ
}
#Analysis
query "ec2_instance_by_region" {
  sql = <<-EOQ
    select
      region,
      count(i.*) as total
    from
      aws_ec2_instance as i
    group by
      region
  EOQ
}
query "ec2_instance_by_account" {
  sql = <<-EOQ
    select
      a.title as "Account",
      count(i.*) as "total"
    from
      aws_ec2_instance as i,
      aws_account as a
    where
      a.account_id = i.account_id
    group by
      a.title
    order by
      count(i.*) desc;
  EOQ
}
query "ec2_instance_by_state" {
  sql = <<-EOQ
    select
      instance_state,
      count(instance_state)
    from
      aws_ec2_instance
    group by
      instance_state
  EOQ
}

#Performance & Utilization
query "ec2_top10_cpu_past_week" {
  sql = <<-EOQ
    with top_n as (
    select
      instance_id,
      avg(average)
    from
      aws_ec2_instance_metric_cpu_utilization_daily
    where
      timestamp  >= CURRENT_DATE - INTERVAL '7 day'
    group by
      instance_id
    order by
      avg desc
    limit 10
  )
  select
      timestamp,
      instance_id,
      average
    from
      aws_ec2_instance_metric_cpu_utilization_hourly
    where
      timestamp  >= CURRENT_DATE - INTERVAL '7 day'
      and instance_id in (select instance_id from top_n)
    order by
      timestamp;
  EOQ
}
query "rds_db_instance_top10_cpu_past_week" {
  sql = <<-EOQ
    with top_n as (
      select
        db_instance_identifier,
        avg(average)
      from
        aws_rds_db_instance_metric_cpu_utilization_daily
      where
        timestamp  >= CURRENT_DATE - INTERVAL '7 day'
      group by
        db_instance_identifier
      order by
        avg desc
      limit 10
  )
  select
      timestamp,
      db_instance_identifier,
      average
    from
       aws_rds_db_instance_metric_cpu_utilization_hourly
    where
      timestamp  >= CURRENT_DATE - INTERVAL '7 day'
      and db_instance_identifier in (select db_instance_identifier from top_n)
    order by
      timestamp;
  EOQ
}