Table: github_repository_vulnerability_alert - Query GitHub Repository Vulnerability Alerts using SQL

GitHub Repository Vulnerability Alerts is a feature within GitHub that allows users to monitor and respond to security vulnerabilities in their repositories. It provides a centralized way to set up and manage alerts for various repositories, including package names, affected ranges, and alert statuses. GitHub Repository Vulnerability Alerts helps users stay informed about the security status of their repositories and take appropriate actions when potential vulnerabilities are detected.

Table Usage Guide

The github_repository_vulnerability_alert table provides insights into repository vulnerability alerts within GitHub. As a security analyst, explore alert-specific details through this table, including alert status, affected package name, and affected range. Utilize it to uncover information about potential vulnerabilities, such as those with high severity, the affected versions of packages, and the verification of fixed versions.

Important Notes

You must specify the repository_full_name (repository including org/user prefix) column in the where or join clause to query the table.

Examples

List vulnerability alerts

This query helps to analyze the security vulnerabilities of a particular Github repository. It provides insights into the severity and description of each vulnerability, aiding in prioritizing and addressing security concerns effectively.

select
  number,
  created_at,
  state,
  security_advisory -> 'cvss' -> 'score' as cvss_score,
  security_advisory ->> 'description' as description,
  severity,
  vulnerable_manifest_filename,
  vulnerable_manifest_path
from
  github_repository_vulnerability_alert
where
  repository_full_name = 'turbot/steampipe';

select
  number,
  created_at,
  state,
  json_extract(
    json_extract(security_advisory, '$.cvss'),
    '$.score'
  ) as cvss_score,
  json_extract(security_advisory, '$.description') as description,
  severity,
  vulnerable_manifest_filename,
  vulnerable_manifest_path
from
  github_repository_vulnerability_alert
where
  repository_full_name = 'turbot/steampipe';

List open vulnerability alerts

The query aids in identifying active vulnerability alerts within a specific GitHub repository. This is useful for maintaining the security of the repository by addressing potential threats promptly.

select
  number,
  created_at,
  state,
  security_advisory -> 'cvss' -> 'score' as cvss_score,
  security_advisory ->> 'description' as description,
  severity,
  vulnerable_manifest_filename,
  vulnerable_manifest_path
from
  github_repository_vulnerability_alert
where
  repository_full_name = 'turbot/steampipe'
  and state = 'OPEN';

select
  number,
  created_at,
  state,
  json_extract(security_advisory, '$.cvss.score') as cvss_score,
  json_extract(security_advisory, '$.description') as description,
  severity,
  vulnerable_manifest_filename,
  vulnerable_manifest_path
from
  github_repository_vulnerability_alert
where
  repository_full_name = 'turbot/steampipe'
  and state = 'OPEN';

List open critical vulnerability alerts

Identify critical security vulnerabilities in a specific GitHub repository that are currently open. This can help prioritize security efforts and address the most severe issues first.

select
  number,
  created_at,
  state,
  security_advisory -> 'cvss' -> 'score' as cvss_score,
  security_advisory ->> 'description' as description,
  severity,
  vulnerable_manifest_filename,
  vulnerable_manifest_path
from
  github_repository_vulnerability_alert
where
  repository_full_name = 'turbot/steampipe'
  and state = 'OPEN'
  and severity = 'CRITICAL';

select
  number,
  created_at,
  state,
  json_extract(
    json_extract(security_advisory, '$.cvss'),
    '$.score'
  ) as cvss_score,
  json_extract(security_advisory, '$.description') as description,
  severity,
  vulnerable_manifest_filename,
  vulnerable_manifest_path
from
  github_repository_vulnerability_alert
where
  repository_full_name = 'turbot/steampipe'
  and state = 'OPEN'
  and severity = 'CRITICAL';

Schema for github_repository_vulnerability_alert

Name	Type	Operators	Description
_ctx	jsonb		Steampipe context in JSON form.
auto_dismissed_at	timestamp with time zone		Timestamp at which the vulnerability alert was automatically dismissed.
created_at	timestamp with time zone		Timestamp when the vulnerability alert was created.
cvss_score	double precision		The CVSS score of the advisory associated with the vulnerability alert.
dependency_scope	text		The dependency scope of the vulnerability alert, will be RUNTIME or DEVELOPMENT.
dismiss_comment	text		Comment on the dismissal of the vulnerability alert.
dismiss_reason	text		Reason for the dismissal of the vulnerability alert.
dismissed_at	timestamp with time zone		Timestamp at which the vulnerability alert was dismissed.
dismisser	jsonb		The user whom dismissed the vulnerability alert.
fixed_at	timestamp with time zone		Timestamp when the vulnerability alert was marked as fixed.
login_id	text	=, !=, ~~, ~~, !~~, !~~	Unique identifier for the user login.
node_id	text		The node id of the vulnerability alert.
number	bigint		Number of vulnerability alert.
repository_full_name	text	=	The full name of the repository, including the owner and repo name.
security_advisory	jsonb		The security advisory associated with the vulnerability alert.
security_vulnerability	jsonb		The vulnerability associated with the vulnerability alert.
severity	text		Severity of the vulnerability.
sp_connection_name	text	=, !=, ~~, ~~, !~~, !~~	Steampipe connection name.
sp_ctx	jsonb		Steampipe context in JSON form.
state	text	=	State of the vulnerability alert, will be 'OPEN', 'FIXED' or 'DISMISSED'.
vulnerable_manifest_filename	text		Filename of the vulnerable manifest.
vulnerable_manifest_path	text		Path of the vulnerable manifest.
vulnerable_requirements	text		Vulnerable requirements from the manifest.

Export

This table is available as a standalone Exporter CLI. Steampipe exporters are stand-alone binaries that allow you to extract data using Steampipe plugins without a database.

You can download the tarball for your platform from the Releases page, but it is simplest to install them with the steampipe_export_installer.sh script:

/bin/sh -c "$(curl -fsSL https://steampipe.io/install/export.sh)" -- github

You can pass the configuration to the command with the --config argument:

steampipe_export_github --config '<your_config>' github_repository_vulnerability_alert