turbot/aws_well_architected

Query: wellarchitected_workload_milestone_unanswered_table

Usage

powerpipe query aws_well_architected.query.wellarchitected_workload_milestone_unanswered_table

SQL

with milestone_info as (
select
workload_id,
milestone_number,
milestone_name,
recorded_at
from
aws_wellarchitected_milestone
where
workload_id = $1
union
select
$1 as workload_id,
0 as milestone_number, -- Lens review API returns latest milestone as 0
'latest' as milestone_name,
current_timestamp as recorded_at
), lens_review as (
select
*
from
aws_wellarchitected_lens_review
where
milestone_number in (select milestone_number from aws_wellarchitected_milestone where workload_id = $1 union select 0 as milestone_number) -- Latest milestone is returned as 0
and workload_id = (select workload_id from aws_wellarchitected_workload where workload_id = $1)
), risk_data as (
select
milestone_number,
sum((risk_counts ->> 'UNANSWERED')::int) as unanswered_risks
from
lens_review as r
where
r.lens_arn = any(string_to_array($2, ','))
group by
r.milestone_number
order by
r.milestone_number
) select
case
when m.milestone_number = 0 then null -- Instead of showing milestone number as 0 for latest, show null to avoid confusion
else m.milestone_number
end as "Milestone Number",
case
when m.milestone_number = 0 then 'latest'
else m.milestone_name
end as "Milestone Name",
r.unanswered_risks as "Unanswered Questions",
to_char(m.recorded_at, 'YYYY-MM-DD HH24:MI') as "Date Saved"
from
risk_data r
left join milestone_info m
on r.milestone_number = m.milestone_number
order by
recorded_at desc;

Params

ArgsNameDefaultDescriptionVariable
$1workload_id
    $2lens_arn

      Dashboards

      The query is used in the dashboards: