turbot/aws_insights

Query: cloudtrail_trail_monthly_forecast_table

Usage

powerpipe query aws_insights.query.cloudtrail_trail_monthly_forecast_table

SQL

with monthly_costs as (
select
period_start,
period_end,
case
when date_trunc('month', period_start) = date_trunc('month', CURRENT_DATE :: timestamp) then 'Month to Date'
when date_trunc('month', period_start) = date_trunc(
'month',
CURRENT_DATE :: timestamp - interval '1 month'
) then 'Previous Month'
else to_char (period_start, 'Month')
end as period_label,
period_end :: date - period_start :: date as days,
sum(unblended_cost_amount) :: numeric :: money as unblended_cost_amount,
(
sum(unblended_cost_amount) / (period_end :: date - period_start :: date)
) :: numeric :: money as average_daily_cost,
date_part(
'days',
date_trunc ('month', period_start) + '1 MONTH' :: interval - '1 DAY' :: interval
) as days_in_month,
sum(unblended_cost_amount) / (period_end :: date - period_start :: date) * date_part(
'days',
date_trunc ('month', period_start) + '1 MONTH' :: interval - '1 DAY' :: interval
) :: numeric :: money as forecast_amount
from
aws_cost_by_service_usage_type_monthly as c
where
service = 'AWS CloudTrail'
and date_trunc('month', period_start) >= date_trunc(
'month',
CURRENT_DATE :: timestamp - interval '1 month'
)
group by
period_start,
period_end
)
select
period_label as "Period",
unblended_cost_amount as "Cost",
average_daily_cost as "Daily Avg Cost"
from
monthly_costs
union all
select
'This Month (Forecast)' as "Period",
(
select
forecast_amount
from
monthly_costs
where
period_label = 'Month to Date'
) as "Cost",
(
select
average_daily_cost
from
monthly_costs
where
period_label = 'Month to Date'
) as "Daily Avg Cost";

Dashboards

The query is used in the dashboards: