Skip to content

Instantly share code, notes, and snippets.

@miketheman

miketheman/_how_to.md

Last active Jun 4, 2020
Embed
What would you like to do?
Mike's Super Janky Manual GitHub Vuln Alert Query

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:

# 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
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
}
}
}
}
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment