Mike's Super Janky Manual GitHub Vuln Alert Query

_how_to.md

How To

Prerequsites:

• GitHub account with admin-level rights to repos, possibly even org-level admin.
• Command Line familiarity
• jq tool

Note: Commands have been written assuming a macOS machine, they may work on other platforms, but I haven't tested those.

Data collection

1. Visit GitHub's GraphQL API Explorer - https://developer.github.com/v4/explorer/

2. Sign in with GitHub

3. Paste the contents of org_repo_vuln_alerts.graphql into the Query pane

4. In the Query Variables pane, create an object that looks like this:

   {"orgname": "PUT YOUR ORG NAME HERE" }
   

5. Press the Play button to run the query

6. Copy the entire contents to a file named part1.json

7. Note the value in the response that says hasNextPage: and if it's true, proceed to the next step, otherwise skip ahead to the "extract" phase

8. Copy the endCursor value from the repsonse, and add that to the Query Variables object, so that

   {"orgname": "PUT YOUR ORG NAME HERE", "cursor": "Y......." }
   

9. Press the Play button to run the query

10. Copy the entire contents to a file named part2.json

Repeat the last few steps until we've copied all of the data to JSON files, and hasNextPage: false appears. Note: We can estimate the parts with the total value in the repsonse - there's roughly 100 records in each part.

Extract

1. Now that we have all the raw JSON data locally, navigate to the location we've saved the part*.json files

2. Download/copy the extract.jq file to the same directory as the part files.

3. Run a command to extract/flatten the data from each part file, and append to a new CSV file

   jq -r -f extract.jq part1.json > list.csv
   jq -r -f extract.jq part2.json >> list.csv
   ...
   

Note: The first command uses one arrow for redirection to create a new file. The second and any subsequent commands append to the same file. If we run the command with a single arrow, it'll overwrite the list.csv file.

Now we have a CSV file we can open with Google Sheets, parse some other way, etc.

Success!

Refs:

• https://developer.github.com/v4/guides/using-the-explorer/
• https://help.github.com/en/github/managing-security-vulnerabilities/about-security-alerts-for-vulnerable-dependencies

extract.jq

# Flatten a response from GitHub's v4 API to CSV-friendly/tabular records.
#
# Use like:
#   jq -r -f extract.jq output/part1.json > list.csv
#   jq -r -f extract.jq output/part2.json >> list.csv
#
# CSV header:
# name,description,isArchived,isFork,isPrivate,severity,package_name,ecosystem,vulnerableRequirements,vulnerableVersionRange,vulnerableManifestPath,updatedAt,dismissedAt,dismissReason,dismisser

.data.organization.repositories.nodes[]
  | . as $in
  | $in.vulnerabilityAlerts.nodes[] as $n
  | [
      $in.name,
      $in.description,
      $in.isArchived,
      $in.isFork,
      $in.isPrivate,

      $n.securityVulnerability.severity,
      $n.securityVulnerability.package.name,
      $n.securityVulnerability.package.ecosystem,
      $n.vulnerableRequirements,
      $n.securityVulnerability.vulnerableVersionRange,
      $n.vulnerableManifestPath,
      $n.securityVulnerability.updatedAt,

      $n.dismissedAt,
      $n.dismissReason,
      $n.dismisser.login
    ]
  | @csv

org_repo_vuln_alerts.graphql

query OrgRepoVulnerabilityAlerts($orgname: String!, $cursor: String) {
  organization(login: $orgname) {
    repositories(first: 100, after: $cursor, orderBy: {field: NAME, direction: ASC}) {
      totalCount
      pageInfo {
        hasNextPage
        endCursor
      }
      nodes {
        name
        isArchived
        vulnerabilityAlerts(first: 100) {
          totalCount
          nodes {
            vulnerableManifestPath
            vulnerableRequirements
            securityVulnerability {
              severity
              package {
                ecosystem
                name
              }
              updatedAt
              vulnerableVersionRange
              advisory {
                severity
              }
            }
            dismissedAt
            dismissReason
            dismisser {
              login
            }
          }
        }
        description
        isFork
        isPrivate
      }
    }
  }
}