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
-
Visit GitHub's GraphQL API Explorer - https://developer.github.com/v4/explorer/
-
Sign in with GitHub
-
Paste the contents of
org_repo_vuln_alerts.graphql
into the Query pane -
In the Query Variables pane, create an object that looks like this:
{"orgname": "PUT YOUR ORG NAME HERE" }
-
Press the Play button to run the query
-
Copy the entire contents to a file named
part1.json
-
Note the value in the response that says
hasNextPage:
and if it'strue
, proceed to the next step, otherwise skip ahead to the "extract" phase -
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......." }
-
Press the Play button to run the query
-
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
-
Now that we have all the raw JSON data locally, navigate to the location we've saved the
part*.json
files -
Download/copy the
extract.jq
file to the same directory as the part files. -
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!