turbot/aws_compliance

Query: codebuild_project_build_greater_then_90_days

Usage

powerpipe query aws_compliance.query.codebuild_project_build_greater_then_90_days

SQL

with latest_codebuild_build as (
select
project_name,
region,
account_id,
min(date_part('day', now() - end_time)) as build_time
from
aws_codebuild_build
group by
project_name,
region,
account_id
),
codebuild_projects as (
select
arn,
name,
region,
account_id,
title,
tags,
_ctx
from
aws_codebuild_project
group by
name,
tags,
arn,
title,
region,
account_id,
_ctx
)
select
p.arn as resource,
case
when b.build_time is null then 'alarm'
when b.build_time < 90 then 'ok'
else 'alarm'
end as status,
case
when b.build_time is null then p.title || ' has no builds.'
else p.title || ' was build ' || build_time || ' day(s) before.'
end as reason
, p.region, p.account_id
from
codebuild_projects as p
left join latest_codebuild_build as b on p.name = b.project_name and p.region = b.region and p.account_id = b.account_id;

Controls

The query is being used by the following controls: