turbot/github_compliance

Query: default_branch_pipeline_must_have_jobs_with_sbom_generation

Usage

powerpipe query github_compliance.query.default_branch_pipeline_must_have_jobs_with_sbom_generation

SQL

with repositories as (
select
name_with_owner,
url
from
github_my_repository
order by
name_with_owner
),
pipelines as (
select
name,
repository_full_name,
pipeline
from
github_workflow
where
repository_full_name in (select name_with_owner from repositories)
),
build_jobs_sbom_details as (
select
p.repository_full_name,
count(*) filter (
where
(
(step ->> 'type' = 'task'
and (step -> 'task' ->> 'name')::text in (
'argonsecurity/actions/generate-manifest',
'anchore/sbom-action',
'CycloneDX/gh-\w+-generate-sbom'
)
)
or (step ->> 'type' = 'shell'
and ((step -> 'shell' ->> 'script')::text like glob('billy generate') or
(step -> 'shell' ->> 'script')::text like glob('trivy sbom') or
(step -> 'shell' ->> 'script')::text like glob('trivy .* --format cyclonedx') or
(step -> 'shell' ->> 'script')::text like glob('syft .*') or
(step -> 'shell' ->> 'script')::text like glob('spdx-sbom-generator') or
(step -> 'shell' ->> 'script')::text like glob('cyclonedx-\w+') or
(step -> 'shell' ->> 'script')::text like glob('jake sbom')
)
)
)
) as jobs_with_sbom,
count(*) filter (
where
(job -> 'metadata' -> 'build')::bool
and (
not (step ->> 'type' = 'task'
and (step -> 'task' ->> 'name')::text in (
'argonsecurity/actions/generate-manifest',
'anchore/sbom-action',
'CycloneDX/gh-\w+-generate-sbom'
)
)
or not (step ->> 'type' = 'shell'
and ((step -> 'shell' ->> 'script')::text like glob('billy generate') or
(step -> 'shell' ->> 'script')::text like glob('trivy sbom') or
(step -> 'shell' ->> 'script')::text like glob('trivy .* --format cyclonedx') or
(step -> 'shell' ->> 'script')::text like glob('syft .*') or
(step -> 'shell' ->> 'script')::text like glob('spdx-sbom-generator') or
(step -> 'shell' ->> 'script')::text like glob('cyclonedx-\w+') or
(step -> 'shell' ->> 'script')::text like glob('jake sbom')
)
)
)
) as jobs_without_sbom
from
pipelines as p,
jsonb_array_elements(pipeline -> 'jobs') as job,
jsonb_array_elements(job -> 'steps') as step
group by
p.repository_full_name,
pipeline
),
pipeline_with_sbom_job_details as (
select
repository_full_name,
count(*) filter (where jobs_with_sbom > 0) as pipeline_with_sbom_jobs,
count(*) filter (where jobs_without_sbom > 0) as pipeline_without_sbom_jobs
from
build_jobs_sbom_details
group by
repository_full_name
)
select
-- Required Columns
r.url as resource,
case
when ps.pipeline_without_sbom_jobs > 0 then 'alarm'
when ps.repository_full_name is null then 'info'
else 'ok'
end as status,
case
when ps.pipeline_without_sbom_jobs > 0 then ps.pipeline_without_sbom_jobs::text || ' pipeline(s) contain a build job without SBOM generation.'
when ps.repository_full_name is null then 'No pipelines available in the repository.'
else 'All pipeline(s) contain a build job with SBOM generation.'
end as reason,
-- Additional Dimensions
r.name_with_owner
from
repositories as r
left join pipeline_with_sbom_job_details as ps on r.name_with_owner = ps.repository_full_name;

Controls

The query is being used by the following controls: