steampipe

ec2-costs-steampipe

WITH filtered_instances AS (
  SELECT instance_id, instance_type
  FROM aws_ec2_instance
  WHERE tags->>'Name' LIKE 'web-server%'
),
usage_costs AS (
  SELECT resource_id, SUM(blended_cost) AS total_cost
  FROM aws_cost_usage
  WHERE usage_start_date >= DATE_TRUNC('month', CURRENT_DATE) - INTERVAL '1 month'
    AND usage_end_date < DATE_TRUNC('month', CURRENT_DATE)
    AND dimension_1 IN (
      SELECT instance_type FROM filtered_instances
    )
  GROUP BY resource_id
)
SELECT fi.instance_id, fi.instance_type, uc.total_cost
FROM filtered_instances fi
LEFT JOIN usage_costs uc ON fi.instance_type = uc.resource_id;

ec2-steampipe

SELECT
  i.InstanceType AS flavor,
  m.metric_name,
  MAX(m.max_cpu) AS max_cpu,
  MIN(m.min_cpu) AS min_cpu,
  AVG(m.avg_cpu) AS avg_cpu
FROM
  (
    SELECT
      MetricName AS metric_name,
      DimensionValue AS instance_id,
      MAX(Value) AS max_cpu,
      MIN(Value) AS min_cpu,
      AVG(Value) AS avg_cpu
    FROM
      cloudwatch_metric
    WHERE
      Namespace = 'AWS/EC2'
      AND MetricName = 'CPUUtilization'
      AND StartTime > DATE_SUB(NOW(), INTERVAL 1 MONTH)
    GROUP BY
      MetricName, DimensionValue
  ) AS m
JOIN
  (
    SELECT
      InstanceId AS instance_id,
      InstanceType AS flavor
    FROM
      aws_ec2_instance
  ) AS i
ON
  m.instance_id = i.instance_id
GROUP BY
  i.InstanceType, m.metric_name;