Query: repository_security_advisory_table

select
  r.name_with_owner as "Repository",
  a.security_advisory_summary as "Advisory",
  a.security_advisory_severity as "Severity",
  a.security_advisory_cve_id as "CVE",
  a.dependency_package_name as "Package",
  a.dependency_scope as "Scope",
  a.created_at as "Alert Created",
  now()::date - a.created_at::date as "Age in Days",
  a.html_url as "advisory_url",
  r.url,
  case
    when a.security_advisory_severity = 'critical' then 1
    when a.security_advisory_severity = 'high' then 2
    when a.security_advisory_severity = 'medium' then 3
    when a.security_advisory_severity = 'low' then 4
    else 5
  end as weight
from
  github_my_repository r
  join github_repository_dependabot_alert a on r.name_with_owner = a.repository_full_name
where
  a.state = 'open'
  and r.name_with_owner like 'turbot/steampipe-plugin%'
  and r.name_with_owner not in ('turbot/steampipe-plugin-papertrail', 'turbot/steampipe-plugin-wordpress', 'turbot/steampipe-plugin-html', 'turbot/steampipe-plugin-chaos', 'turbot/steampipe-plugin-chaosratelimit', 'turbot/steampipe-plugin-chaosdynamic', 'turbot/steampipe-plugin-pokemon', 'turbot/steampipe-plugin-gitlab')
order by
  weight;