Chart GitHub CVE Review Times

Query GitHub advisories, export CVE review lag, and chart the median.

Introduction

30 Second Summary

A newly disclosed vulnerability can appear in the news long before your usual tools finish reviewing it. Without a timeline, you cannot tell whether that delay lasts minutes or days.

In this project, you will use the GitHub GraphQL API to build a script that measures the delay between vulnerability publication and GitHub review. Its outputs are a CSV, a review-lag histogram, and a withdrawn-advisory audit.

What You'll Build

You open your generated histogram to see the median wait between a severe vulnerability going public and GitHub reviewing it.

By the end of this project, you'll have:

  • A complete severe-advisory CSV with one row per advisory. You can inspect each CVE ID beside its NVD publication time, GitHub review time, and lag in hours.
  • A review-lag histogram that lets you spot the median delay for critical and high severity vulnerabilities.
  • A withdrawn-advisory audit that lets you compare past alerts against a package you use.
  • Secret Mission: An optional challenge to push your skills further.

What do I need before I start?

A free GitHub account plus Python or Node with an HTTP client is enough to begin. You do not need a payment card or a paid GitHub seat.

Before We Start

Pulling vulnerability dates from the GitHub GraphQL API requires a GitHub identity plus a local environment that can run the analysis.

In this step, you will prepare your GitHub account. You will also set up the local tools needed to write and run your Python script.

In this step, get ready to:
  • Confirm that your GitHub account is available.
  • Set up Visual Studio Code as your code editor.
  • Verify your local Python toolchain.
Prepare your GitHub account

The GitHub Advisory Database uses your personal account to identify API requests. A verified email address keeps the account ready for the access token you create later.

Choose the path that matches your current account status.

✔️ I have a verified account

  • Visit the GitHub homepage in your browser.
  • Sign in to your personal account.
  • Confirm that you can see your signed-in dashboard.

Your existing account is ready to use for this project.

ⓧ I need an account

  • Visit the official GitHub sign-up page in your browser.
  • Follow the prompts to create your personal account.
  • Open the verification message in your email inbox.
  • Complete the email verification process.
  • Return to the GitHub homepage.
  • Confirm that you can see your signed-in dashboard.

Your new account is now ready for GitHub API access.

Having trouble with your account?

  • Check the spam folder if the verification message is missing.
  • Confirm that you entered the same email address used during sign-up.

Ask for help with finishing your GitHub account setup.

Your GitHub identity is ready. That gives the advisory queries a secure account to work from.

Set up Visual Studio Code

A code editor keeps your script readable while you add the query logic in small pieces. You will use Visual Studio Code for that workspace.

  • Press Cmd+Space on macOS or the Windows key on Windows to open system search.
  • Type Visual Studio Code into the search field.
  • Press Enter to try opening the editor.

✔️ Visual Studio Code opens

VS Code is installed. Keep the editor open so it is ready for your analysis files.

ⓧ Visual Studio Code is not installed yet

Install VS Code using the instructions for your operating system.

macOS

  • Visit the official Visual Studio Code download page.
  • Download the Universal .dmg installer for macOS.
  • Open the downloaded .dmg file.
  • Drag Visual Studio Code.app into the Applications folder.
  • Press Cmd+Space to open Spotlight.
  • Type Visual Studio Code into Spotlight.
  • Press Enter to open the editor.

You should see the VS Code welcome screen.

Windows

  • Visit the official Visual Studio Code download page.
  • Download the User Installer for your Windows system.
  • Run the downloaded .exe installer.
  • Follow the installer prompts to complete the installation.
  • Press the Windows key to open system search.
  • Type Visual Studio Code into the search field.
  • Press Enter to open the editor.

You should see the VS Code welcome screen.

Visual Studio Code still will not open?

  • Confirm that the installer finished before searching for the application.
  • Restart your computer if the installed application is missing from system search.

Ask for help with installing Visual Studio Code.

Your editor is ready. You now have a dedicated place to build the advisory analysis script.

Verify Python and pip

Python runs the analysis script on your computer. The pip package installer adds the libraries used for API requests and charting.

  • Press Cmd+Space on macOS or the Windows key on Windows to open system search.
  • Type Terminal on macOS or PowerShell on Windows.
  • Press Enter to open the terminal.

Before you run the checks, do you expect both commands to return version information?

macOS

  • Check Python 3 plus its package installer by running:
python3 --version
python3 -m pip --version

What do these checks prove?

  • The python3 --version command confirms that your terminal can find a Python 3 interpreter.
  • The python3 -m pip --version command confirms that the same interpreter can access pip.

Windows

  • Check Python 3 plus its package installer by running:
py -3 --version
py -3 -m pip --version

What do these checks prove?

  • The py -3 --version command confirms that the Windows launcher can find a Python 3 interpreter.
  • The py -3 -m pip --version command confirms that the same interpreter can access pip.

✔️ I see Python and pip versions

Your output names Python 3 on the first line. It shows a pip version on the second line.

Your local Python toolchain is ready to create the CSV analysis later in the project.

ⓧ A command is not available

Install Python 3 using the instructions for your operating system. The installer includes pip.

macOS

  • Visit the official Python downloads page for macOS.
  • Download the latest supported macOS installer.
  • Open the downloaded .pkg file.
  • Follow the installer prompts to complete the installation.
  • Close Terminal after the installation finishes.
  • Reopen Terminal through Spotlight.
  • Verify Python 3 plus pip by running:
python3 --version
python3 -m pip --version
What should you see?

The first line should identify Python 3. The second line should identify pip plus its installation path.

Windows

  • Visit the official Python downloads page for Windows.
  • Download the recommended installer for the latest stable Python 3 release.
  • Run the downloaded .exe installer.
  • Follow the installer prompts to complete the installation.
  • Close PowerShell after the installation finishes.
  • Reopen PowerShell through system search.
  • Verify Python 3 plus pip by running:
py -3 --version
py -3 -m pip --version
What should you see?

The first line should identify Python 3. The second line should identify pip plus its installation path.

Commands still unavailable?

  • Close every open terminal window before trying the version checks again.
  • Confirm that the Python installer completed without being cancelled.

Ask for help with getting Python and pip recognized.

Your account plus local tools are ready for the first advisory query.

Your setup is complete. Next, you will create a read-only GitHub token and run your first advisory query.

Query Advisories in GraphQL Explorer

Measuring review delays starts with a trustworthy source of vulnerability records. The GitHub Advisory Database holds the public advisories that your later analysis measures.

GraphQL lets you request a precise set of fields from that database. GitHub returns the requested data as a structured JSON response.

You will create a tightly limited credential. You will then prove the connection with a small advisory query.

In this step, get ready to:
  • Create a GitHub token that can only access public information.
  • Configure a browser GraphQL Explorer to use GitHub's API.
  • Run a security advisory query that returns JSON data.
Create a read-only GitHub token

A personal access token authenticates your API requests as your GitHub account. A classic token with no assigned scopes can only access public information.

This credential deserves the same care as a password. You will keep its reach narrow by leaving every scope unchecked.

  • Open GitHub in your browser.
  • Sign in with your GitHub account.
  • Click your profile picture in the upper-right corner.
  • Click Settings.
  • Click Developer settings in the left sidebar.
  • Click Tokens (classic) under Personal access tokens.
  • Select Generate new token.
  • Click Generate new token (classic).

You are now on the form that controls the token's lifetime and access.

  • Enter cve-review-analysis in the Note field.
  • Choose an expiration date that gives you enough time to finish the project.
  • Leave every option under Select scopes unchecked.
  • Click Generate token.
  • Copy the generated token into a password manager or encrypted credential manager.
  • Confirm that the token is absent from your code editor and project files.

Good. Your token can authenticate public API requests while your project files stay credential-free.

Why Leave Every Scope Empty?

This project only reads public advisory data. An unscoped classic token provides access to public information without granting access to private repositories.

Can't Create the Token?

  • Confirm that your GitHub account has a verified email address.
  • Return to Developer settings if the token form closes before you generate the credential.
  • Check that every scope remains empty before generating the token again.

Need help with the token form? You can also ask in GitHub's Apps, API and Webhooks community.

Open a GraphQL Explorer for GitHub

GitHub's current documentation recommends using a GraphQL client IDE for interactive queries. Altair provides a browser editor that you can point at GitHub's official endpoint.

  • Open the Altair web app in your browser.
  • Enter https://api.github.com/graphql in the endpoint field.
  • Keep POST selected as the request method.
  • Click Auth.
  • Choose Bearer token as the authorization method.
  • Paste your stored token into the bearer token field.
  • Close the Auth panel to hide the credential.

Your Explorer now sends authenticated requests to GitHub's GraphQL endpoint.

Run your first advisory query

A GraphQL query names the objects and fields that belong in the response. This first request stays small so you can inspect each advisory easily.

  • Replace the starter text in the Query editor with the query below:
# Request a small first page of public security advisories
query FirstAdvisories {
  securityAdvisories(first: 5) {
    # Select the fields needed to recognize each result
    nodes {
      ghsaId
      severity
      summary
    }
  }
}

What Does This Query Do?

  • The securityAdvisories(first: 5) field requests a manageable first page of five advisories.
  • The nodes field selects the individual advisory records.
  • The ghsaId field identifies each advisory with its GitHub Security Advisory ID.
  • The severity field reports the advisory's severity level.
  • The summary field provides a short description of the vulnerability.

Before you send the query, do you expect your endpoint and token setup to return advisory data or an authentication error?

  • Click Send Request to run the query.

You will see a JSON response containing a data object. Inside securityAdvisories, the nodes array contains five advisory entries.

That's your first live result. The Explorer can now read advisory data from GitHub with your limited token.

Don't See Advisory Nodes?

  • Confirm that the endpoint is exactly https://api.github.com/graphql.
  • Reopen Auth to confirm that Bearer token is selected.
  • Paste the token again from your secure storage if the response mentions authentication.
  • Check that the query has matching braces if the response mentions invalid syntax.

Help me diagnose my GraphQL response.

Your authenticated advisory connection is ready. Next, you will add the CVE identifier and review timestamps needed to measure GitHub's response lag.

Add CVE and Review Date Fields

Your first query proved that the GitHub GraphQL API can return security advisories. The next goal is to make each result useful for timing a review.

A CVE ID connects a result to its public vulnerability record. The NVD publication time starts the interval you will measure.

The GitHub review time closes that interval. The source code location links the advisory to the affected code.

In this step, get ready to:
  • Extend the existing advisory selection with four new fields.
  • Run the expanded query in GitHub GraphQL Explorer.
  • Confirm that the JSON response contains CVE IDs plus review dates.
Add the advisory fields

A GraphQL selection set controls which fields appear in the response. Adding fields expands each advisory object without removing the data you already requested.

  • Return to the query editor in GitHub GraphQL Explorer from earlier.
  • Find the braces that list fields for each advisory.
  • Keep every field already inside those braces.
  • Add the four advisory fields by pasting these lines inside the same braces:
# Request the advisory details used in the lag report.
cveId
nvdPublishedAt
githubReviewedAt
sourceCodeLocation

What Do These Fields Add?

  • The cveId field identifies the advisory's CVE record.
  • The nvdPublishedAt field records when the National Vulnerability Database published its record.
  • The githubReviewedAt field records when GitHub reviewed the advisory.
  • The sourceCodeLocation field points to affected source code relevant to the advisory.

Before you run the expanded query, do you expect the response objects to gain new keys or replace the fields you already had?

  • Run the expanded query with the triangular control above the query editor.

You'll see the existing fields remain in each JSON advisory object. The four new keys appear beside them.

Advisories with available records show a CVE ID plus NVD publication and GitHub review timestamps.

Missing the New Fields?

  • Move all four field lines inside the braces that select data for each advisory.
  • Check each field name against the snippet above.
  • Run the updated query again after correcting the selection.

Help me fix missing fields in my advisory response.

Read the review timeline

The two date fields provide the endpoints for calculating review lag. Inspecting one complete advisory confirms that the query now returns those endpoints together.

Before your final check, which fields should identify the advisory and bracket its review timeline?

  • Scan the JSON response for an advisory where cveId contains a CVE value.
  • Check nvdPublishedAt in the same advisory object.
  • Check githubReviewedAt in that advisory object.
  • Check sourceCodeLocation in that advisory object.

You should see the CVE identifier beside both timestamps. You should also see the source code location field in the same advisory object.

Why Can an NVD Date Be Empty?

Some advisories never receive an NVD record. Their nvdPublishedAt value can therefore be null even though the query is working.

Great work. Your advisory results now carry the evidence needed to measure review lag.

Next, you'll narrow the feed to critical and high severity advisories.

Filter and Page Severe Advisories

Your query now returns the CVE identifiers plus the timestamps needed to measure review lag.

The unfiltered response includes advisories outside your severe-risk focus. Cursor pagination also keeps the GitHub GraphQL API moving beyond its first page of results.

In this step, get ready to:
  • Filter the advisory feed to critical or high severity.
  • Request pagination details with each page.
  • Use the returned cursor to fetch the next page.
Filter for severe advisories

The severities argument filters the connection before GitHub returns any nodes. This keeps the response focused on advisories marked CRITICAL or HIGH.

  • Switch back to the GraphQL Explorer from earlier.
  • Replace the existing query by pasting this version:
# Keep one cursor variable so the query can request each page.
query($cursor: String) {
  securityAdvisories(
    first: 100
    after: $cursor
    severities: [CRITICAL, HIGH]
  ) {
    pageInfo {
      hasNextPage
      endCursor
    }
    nodes {
      severity
      cveId
      nvdPublishedAt
      githubReviewedAt
      sourceCodeLocation
    }
  }
}

What Does This Query Do?

  • The $cursor variable gives after a reusable starting position for each page.
  • The first: 100 argument requests the maximum number of connection items allowed in one page.
  • The severities argument keeps advisories with either requested severity.
  • The pageInfo selection reports whether another page exists.
  • The severity field makes the server-side filter visible in each returned node.

Before you run the query, do you expect any moderate advisories to remain in the response?

  • Execute the query using the control above the query editor.

You'll see advisory nodes whose severity value is CRITICAL or HIGH.

The response also includes pageInfo beside the returned nodes. Your query now produces a focused severe-advisory feed.

Query Not Running?

  • Place severities inside the parentheses for securityAdvisories.
  • Keep $cursor declared as String at the top of the query.
  • Match every opening brace with a closing brace.

Ask for help with the severe-advisory query.

Read the pagination cursor

The pageInfo object describes the page you just received. Its ending cursor identifies where the next request should resume.

  • Find pageInfo beneath securityAdvisories in the response.
  • Check hasNextPage to confirm another page exists.
  • Capture the value beside endCursor here: your endCursor value.

How Does a Cursor Work?

A cursor marks a position in the result set. Passing endCursor into the next request makes the connection continue after the final node on the current page.

The value is opaque. Copying it exactly preserves GitHub's chosen position.

Request the next page

The query already connects $cursor to the after argument. Supplying the cursor activates the next-page request without changing the advisory fields.

  • Expand the query variables area beneath the editor.
  • Populate $cursor by pasting this JSON:
{
  "cursor": "[[END_CURSOR=\"your endCursor value\"]]"
}

What Does This JSON Do?

The cursor key supplies the captured value to $cursor. The after argument uses that position when GitHub resolves the query.

Before you run the query again, do you expect the response to repeat the first page?

  • Execute the query again using the control above the query editor.

You'll see a different set of advisory nodes. The new pageInfo contains a fresh endCursor for the following page.

That's the pagination loop working. Your query can now move through the full severe-advisory result set.

Still Seeing the First Page?

  • Confirm the JSON key is spelled cursor.
  • Paste the complete cursor value without removing any characters.
  • Confirm the query still passes $cursor to after.

Ask for help loading the next page.

✔️ Awesome, I've got everything!

Your query includes the severe filters plus the fields needed for cursor pagination.

ⓧ I'd like to double check the full code

# Keep one cursor variable so the query can request each page.
query($cursor: String) {
  securityAdvisories(
    first: 100
    after: $cursor
    severities: [CRITICAL, HIGH]
  ) {
    pageInfo {
      hasNextPage
      endCursor
    }
    nodes {
      severity
      cveId
      nvdPublishedAt
      githubReviewedAt
      sourceCodeLocation
    }
  }
}

Your query can now collect severe advisories across every available page. Next, you'll move this pagination loop into a script that writes the results to a CSV.

Export Advisory Lags to CSV

Your filtered query already proves that the GitHub GraphQL API can return critical and high advisories page by page.

Now you need a repeatable export that follows every cursor. The script will turn each advisory into one CSV row with a review lag measured in hours.

In this step, get ready to:
  • Create a local Python exporter for your severe advisory query.
  • Fetch every page through cursor pagination.
  • Export the advisory fields with their review lags to a CSV file.
Create the local script

The browser query is useful for testing one response. A local Python script can repeat that request until GitHub reports that no pages remain.

  • Use Finder on macOS or File Explorer on Windows to create a folder named github-cve-review-lag on your Desktop.
  • Open the github-cve-review-lag folder in your code editor.

You should see the empty github-cve-review-lag folder in your editor's file sidebar.

  • Create github_cve_review_lag.py inside the github-cve-review-lag folder.
  • Add the imports and severe-advisory query by pasting this code into github_cve_review_lag.py:
import csv
import json
import os
from datetime import datetime
from urllib.request import Request, urlopen

# Send every GraphQL request to GitHub's single API endpoint.
ENDPOINT = "https://api.github.com/graphql"

# Request severe advisories plus the cursor needed for the next page.
QUERY = """
query($cursor: String) {
  securityAdvisories(first: 100, after: $cursor, severities: [CRITICAL, HIGH]) {
    totalCount
    pageInfo {
      hasNextPage
      endCursor
    }
    nodes {
      cveId
      nvdPublishedAt
      githubReviewedAt
      sourceCodeLocation
      severity
    }
  }
}
"""

What does this code do?

  • The standard library imports handle HTTP requests, timestamps, environment variables, JSON responses, and CSV output without an extra package.
  • The QUERY constant carries your critical and high severity filter into the script.
  • The pageInfo fields tell the script whether another page exists.
  • Save github_cve_review_lag.py.
  • Confirm that the script is valid Python syntax by running this command in your editor's terminal:
python -m py_compile github_cve_review_lag.py

What does this check prove?

The py_compile module checks the file's Python syntax without sending a request to GitHub. A clean return to the terminal prompt confirms that this first chunk compiles.

Seeing a syntax error?

  • Check that the opening and closing triple quotes around QUERY are both present.
  • Confirm that each closing brace from the GraphQL query is inside the triple-quoted string.

Use this prompt if the compiler still reports a problem: Help me compare my Python query string with this syntax error.

Each advisory needs a consistent row shape before it can enter the CSV. The next function also keeps missing NVD dates safe by leaving the lag cell blank.

  • Add this function below the QUERY string in github_cve_review_lag.py:
def advisory_to_row(advisory):
    # Calculate a lag only when both ends of the timeline exist.
    published = advisory["nvdPublishedAt"]
    reviewed = advisory["githubReviewedAt"]
    lag_hours = ""
    if published and reviewed:
        start = datetime.fromisoformat(published.replace("Z", "+00:00"))
        end = datetime.fromisoformat(reviewed.replace("Z", "+00:00"))
        lag_hours = round((end - start).total_seconds() / 3600, 2)

    # Match each GraphQL field to a readable CSV column.
    return {
        "CVE ID": advisory["cveId"] or "",
        "NVD publish time": published or "",
        "GitHub review time": reviewed or "",
        "Source code location": advisory["sourceCodeLocation"] or "",
        "Severity": advisory["severity"],
        "Review lag hours": lag_hours,
    }

How is the lag calculated?

  • The function parses both timestamps with their time zone offsets intact.
  • The timestamp subtraction produces the elapsed review time.
  • Dividing the total seconds by 3600 converts that duration into hours.
  • A missing timestamp produces an empty Review lag hours cell.
  • Save github_cve_review_lag.py.
  • Check the expanded script's syntax by running:
python -m py_compile github_cve_review_lag.py

What does this check prove?

This compilation checks the timestamp calculation and the returned dictionary. A clean terminal prompt confirms that the row conversion function has valid syntax.

Is the row function failing to compile?

  • Check that the body of advisory_to_row() stays indented beneath the function definition.
  • Confirm that every dictionary key has a comma after its value.

Ask for targeted help here: Help me fix the advisory row function.

Fetch every advisory page

A single API response contains at most the requested page size. Cursor pagination carries the previous page's endCursor into the next request until hasNextPage becomes false.

  • Add the pagination function below advisory_to_row() in github_cve_review_lag.py:
def fetch_advisories(token):
    # Follow every cursor so the export includes the complete result set.
    rows = []
    cursor = None
    total_count = 0
    while True:
        body = json.dumps({"query": QUERY, "variables": {"cursor": cursor}}).encode("utf-8")
        request = Request(
            ENDPOINT,
            data=body,
            headers={"Authorization": f"Bearer {token}"},
            method="POST",
        )
        with urlopen(request) as response:
            result = json.load(response)

        if result.get("errors"):
            raise RuntimeError(json.dumps(result["errors"], indent=2))

        advisories = result["data"]["securityAdvisories"]
        total_count = advisories["totalCount"]
        rows.extend(advisory_to_row(advisory) for advisory in advisories["nodes"])
        page_info = advisories["pageInfo"]
        if not page_info["hasNextPage"]:
            break
        cursor = page_info["endCursor"]

    return rows, total_count

How does pagination work here?

  • The first request sends a null cursor to begin at the start of the result set.
  • Each response contributes its advisory nodes to rows.
  • The loop copies endCursor into the next request when another page exists.
  • The function returns the collected rows with GitHub's totalCount value for verification.
  • Save github_cve_review_lag.py.
  • Verify the pagination function's syntax by running:
python -m py_compile github_cve_review_lag.py

What does this check prove?

This compilation checks the request construction and pagination loop. A clean return confirms that Python can parse the complete function.

Does the pagination function show an error?

  • Check that the statements inside while True: use the same indentation.
  • Confirm that the closing parenthesis for Request() appears before the response block.

Get help with the loop here: Help me debug the pagination function.

The final block connects authentication, pagination, and file output. It also prints both counts so you can confirm that the CSV includes every advisory returned by the filter.

  • Add this final block below fetch_advisories() in github_cve_review_lag.py:
if __name__ == "__main__":
    # Read the token from the current environment instead of a project file.
    token = os.environ.get("GITHUB_TOKEN")
    if not token:
        raise RuntimeError("Set GITHUB_TOKEN before running the script.")

    # Export every advisory with a stable column order.
    rows, total_count = fetch_advisories(token)
    fieldnames = [
        "CVE ID",
        "NVD publish time",
        "GitHub review time",
        "Source code location",
        "Severity",
        "Review lag hours",
    ]
    with open("review_lag.csv", "w", newline="", encoding="utf-8") as csv_file:
        writer = csv.DictWriter(csv_file, fieldnames=fieldnames)
        writer.writeheader()
        writer.writerows(rows)

    print(f"Wrote {len(rows)} of {total_count} advisories to review_lag.csv")

What does the final block do?

  • The script reads GITHUB_TOKEN from the current terminal environment.
  • The CSV writer preserves the six column names in a stable order.
  • The completion message compares the exported row count with totalCount.
  • Save github_cve_review_lag.py.
  • Compile the completed script by running:
python -m py_compile github_cve_review_lag.py

What does this final syntax check prove?

This command compiles the complete exporter without contacting the API. A clean return confirms that the full file is ready for its authenticated run.

Is the final block failing to compile?

  • Confirm that the lines inside the with open() block use one extra indentation level.
  • Check that every value in fieldnames has a comma after it.

Ask for a focused comparison here: Help me check the final CSV block.

✔️ Awesome, I've got everything!

Your completed exporter now contains the query, row conversion, pagination loop, environment-variable authentication, and CSV writer. Save github_cve_review_lag.py before continuing.

ⓧ I'd like to double check the full code

import csv
import json
import os
from datetime import datetime
from urllib.request import Request, urlopen

# Send every GraphQL request to GitHub's single API endpoint.
ENDPOINT = "https://api.github.com/graphql"

# Request severe advisories plus the cursor needed for the next page.
QUERY = """
query($cursor: String) {
  securityAdvisories(first: 100, after: $cursor, severities: [CRITICAL, HIGH]) {
    totalCount
    pageInfo {
      hasNextPage
      endCursor
    }
    nodes {
      cveId
      nvdPublishedAt
      githubReviewedAt
      sourceCodeLocation
      severity
    }
  }
}
"""


def advisory_to_row(advisory):
    # Calculate a lag only when both ends of the timeline exist.
    published = advisory["nvdPublishedAt"]
    reviewed = advisory["githubReviewedAt"]
    lag_hours = ""
    if published and reviewed:
        start = datetime.fromisoformat(published.replace("Z", "+00:00"))
        end = datetime.fromisoformat(reviewed.replace("Z", "+00:00"))
        lag_hours = round((end - start).total_seconds() / 3600, 2)

    # Match each GraphQL field to a readable CSV column.
    return {
        "CVE ID": advisory["cveId"] or "",
        "NVD publish time": published or "",
        "GitHub review time": reviewed or "",
        "Source code location": advisory["sourceCodeLocation"] or "",
        "Severity": advisory["severity"],
        "Review lag hours": lag_hours,
    }


def fetch_advisories(token):
    # Follow every cursor so the export includes the complete result set.
    rows = []
    cursor = None
    total_count = 0
    while True:
        body = json.dumps({"query": QUERY, "variables": {"cursor": cursor}}).encode("utf-8")
        request = Request(
            ENDPOINT,
            data=body,
            headers={"Authorization": f"Bearer {token}"},
            method="POST",
        )
        with urlopen(request) as response:
            result = json.load(response)

        if result.get("errors"):
            raise RuntimeError(json.dumps(result["errors"], indent=2))

        advisories = result["data"]["securityAdvisories"]
        total_count = advisories["totalCount"]
        rows.extend(advisory_to_row(advisory) for advisory in advisories["nodes"])
        page_info = advisories["pageInfo"]
        if not page_info["hasNextPage"]:
            break
        cursor = page_info["endCursor"]

    return rows, total_count


if __name__ == "__main__":
    # Read the token from the current environment instead of a project file.
    token = os.environ.get("GITHUB_TOKEN")
    if not token:
        raise RuntimeError("Set GITHUB_TOKEN before running the script.")

    # Export every advisory with a stable column order.
    rows, total_count = fetch_advisories(token)
    fieldnames = [
        "CVE ID",
        "NVD publish time",
        "GitHub review time",
        "Source code location",
        "Severity",
        "Review lag hours",
    ]
    with open("review_lag.csv", "w", newline="", encoding="utf-8") as csv_file:
        writer = csv.DictWriter(csv_file, fieldnames=fieldnames)
        writer.writeheader()
        writer.writerows(rows)

    print(f"Wrote {len(rows)} of {total_count} advisories to review_lag.csv")
Run the export and inspect the CSV

Your token grants access to the API, so this next part deserves care. The commands keep it in the current terminal session without writing it into github_cve_review_lag.py or review_lag.csv.

  • Open a terminal inside the github-cve-review-lag folder.
  • Choose the tab for your operating system.

macOS

  • Load your token into the current terminal session by running these commands:
  • Paste your token when the terminal waits for input.
  • Press Enter after pasting the token.
read -s GITHUB_TOKEN
export GITHUB_TOKEN

How is the token handled?

The read -s command suppresses the token while you type or paste it. export makes the value available to the Python process launched from this terminal.

Did the terminal return immediately?

Run the first line again if the terminal did not wait for your token. Keep the token out of your script and project files.

Get help without sharing the token: Help me set a hidden session variable on macOS.

Windows

  • Load your token into the current PowerShell session by running this command:
  • Paste your token when PowerShell displays the prompt.
  • Press Enter after pasting the token.
$env:GITHUB_TOKEN = Read-Host "Paste your GitHub token" -MaskInput

How is the token handled?

PowerShell masks the token while you enter it. The resulting GITHUB_TOKEN value stays scoped to the current session and is inherited by the Python process.

Is MaskInput unavailable?

Use a current PowerShell release if the terminal reports that the masking parameter is unavailable. Do not place the token directly in the Python file.

Get help without exposing the credential: Help me set a masked PowerShell environment variable.

Before you run the exporter, do you expect the exported row count to match GitHub's total count?

macOS

  • Run the completed exporter with Python:
python3 github_cve_review_lag.py

What should you see?

The terminal prints the number of rows written and GitHub's total advisory count. Both numbers should match before the message ends with review_lag.csv.

Did the export fail?

  • Set the token again if the error mentions the missing GITHUB_TOKEN environment variable.
  • Check the token's validity if the response reports an authentication problem.
  • Confirm that your internet connection can reach the GitHub API if the request cannot open the endpoint.

Ask for help without including your credential: Help me diagnose this export failure.

Windows

  • Run the completed exporter with Python:
python github_cve_review_lag.py

What should you see?

The terminal prints the exported row count beside GitHub's total advisory count. Matching values confirm that cursor pagination reached the final page.

Did the export fail?

  • Set the token again if the error mentions the missing GITHUB_TOKEN environment variable.
  • Check the token's validity if the response reports an authentication problem.
  • Confirm that your internet connection can reach the GitHub API if the request cannot open the endpoint.

Ask for help without including your credential: Help me diagnose this export failure.

That is the full dataset captured. Your matching counts prove that the script followed every cursor instead of stopping after the first page.

  • Open review_lag.csv from the github-cve-review-lag folder in your spreadsheet app.
  • Confirm that the first row contains the six expected column headers.
  • Check several rows for CRITICAL or HIGH in the severity column.
  • Find a row with both timestamps and confirm that its review lag cell contains a number.

You should see one row per critical or high advisory. Rows without both timestamps have a blank review lag value because there is no complete interval to calculate.

Your advisory timeline is now a reusable dataset. Next, you will plot the review lags and mark their median.

Plot the Review Lag Distribution

Your paginated exporter now turns GitHub advisory data into a CSV that you can inspect one row at a time.

Individual rows show each review delay. A histogram reveals where most critical or high severity CVEs sit across the full distribution.

In this step, get ready to:
  • Install Matplotlib for plotting the exported review lags.
  • Calculate the median review lag from the populated CSV values.
  • Draw a histogram with the median shown as a reference line.
Prepare Python for charting

Matplotlib turns numeric values into charts. You will use it to group similar review lags into histogram bars.

  • Install the plotting library in your existing Python environment by running:
python -m pip install -U matplotlib

What does this command do?

The command installs Matplotlib through Python's package installer. The upgrade option also brings an existing installation up to date.

  • Confirm the terminal returns to its prompt after reporting that Matplotlib is installed or already available.

Matplotlib installation failed?

  • Check that the terminal has an internet connection before retrying the installation.
  • Use the same Python environment that ran your exporter in the previous step.

Help me install Matplotlib in the Python environment for my GitHub advisory exporter.

Calculate the median review lag

The median is the middle review lag after the values are sorted. It gives you a typical delay without letting a few extreme waits dominate the result.

  • Return to github_cve_review_lag.py in the code editor from earlier.
  • Add the following code at the end of the file after the existing CSV-writing code:
def calculate_median_review_lag(csv_path):
    from statistics import median

    # Read only values that can contribute to review-lag analysis
    review_lags = []
    with open(csv_path, newline="", encoding="utf-8") as csv_file:
        reader = csv.DictReader(csv_file)
        for row in reader:
            lag_value = row["review_lag_hours"]
            if lag_value:
                review_lags.append(float(lag_value))

    # Stop with a clear message when no rows contain both timestamps
    if not review_lags:
        raise ValueError("No review lag values available to plot.")

    median_lag = median(review_lags)
    print(f"Median review lag: {median_lag:.1f} hours")
    return review_lags, median_lag

review_lags, median_lag = calculate_median_review_lag("review_lag.csv")

What does this code do?

  • The csv.DictReader call maps each row to the column names in review_lag.csv.
  • Blank review_lag_hours cells are skipped because those advisories do not have both timestamps.
  • The median() call finds the middle value among the usable review lags.
  • The function returns the numeric values for the histogram plus the median marker position.
  • Save github_cve_review_lag.py.

Before you run the script, predict whether the median will land near the densest cluster or be pulled toward the longest lags.

  • Switch back to the terminal session that has your secure token environment variable.
  • Recreate the CSV and calculate its median by running:
python github_cve_review_lag.py

What should you see?

The script prints a median review lag measured in hours after it rewrites the CSV. Your exact value reflects the advisory data returned during this run.

No median value in the terminal?

  • Check that review_lag.csv is in the same folder as github_cve_review_lag.py.
  • Confirm the CSV header contains review_lag_hours with the same spelling used in the script.
  • Confirm at least one row has a numeric value in that column.

Help me debug the median calculation in my review-lag exporter.

Draw the histogram

Each histogram bar counts advisories within one review-lag range. A vertical median line makes the typical delay visible even when the distribution has a long tail.

  • Return to github_cve_review_lag.py in your code editor.
  • Add the following code below review_lags, median_lag = calculate_median_review_lag("review_lag.csv"):
def plot_review_lag(review_lags, median_lag):
    import matplotlib.pyplot as plt

    # Show how many advisories fall into each review-lag range
    plt.figure(figsize=(10, 6))
    plt.hist(review_lags, bins=30, edgecolor="black")
    plt.axvline(
        median_lag,
        color="red",
        linestyle="--",
        linewidth=2,
        label=f"Median: {median_lag:.1f} hours",
    )
    plt.xlabel("Review lag (hours)")
    plt.ylabel("Number of advisories")
    plt.title("GitHub Advisory Review Lag for Critical and High CVEs")
    plt.legend()
    plt.tight_layout()

    # Save a reusable chart next to the CSV export
    plt.savefig("review_lag_histogram.png", dpi=150)
    plt.close()
    print("Saved chart to review_lag_histogram.png")

plot_review_lag(review_lags, median_lag)

What does this code do?

  • The hist() call divides the review lags into 30 ranges.
  • The axvline() call draws a dashed red line at the calculated median.
  • The line label prints the median in hours directly on the chart.
  • The savefig() call writes the finished chart to review_lag_histogram.png.
  • Save github_cve_review_lag.py.

✔️ Awesome, I've got everything!

Your plotting workflow is complete. The saved script now calculates the median before generating the histogram.

ⓧ I'd like to double check the full code

The plotting section at the bottom of github_cve_review_lag.py should match this reference.

def calculate_median_review_lag(csv_path):
    from statistics import median

    # Read only values that can contribute to review-lag analysis
    review_lags = []
    with open(csv_path, newline="", encoding="utf-8") as csv_file:
        reader = csv.DictReader(csv_file)
        for row in reader:
            lag_value = row["review_lag_hours"]
            if lag_value:
                review_lags.append(float(lag_value))

    # Stop with a clear message when no rows contain both timestamps
    if not review_lags:
        raise ValueError("No review lag values available to plot.")

    median_lag = median(review_lags)
    print(f"Median review lag: {median_lag:.1f} hours")
    return review_lags, median_lag

review_lags, median_lag = calculate_median_review_lag("review_lag.csv")

def plot_review_lag(review_lags, median_lag):
    import matplotlib.pyplot as plt

    # Show how many advisories fall into each review-lag range
    plt.figure(figsize=(10, 6))
    plt.hist(review_lags, bins=30, edgecolor="black")
    plt.axvline(
        median_lag,
        color="red",
        linestyle="--",
        linewidth=2,
        label=f"Median: {median_lag:.1f} hours",
    )
    plt.xlabel("Review lag (hours)")
    plt.ylabel("Number of advisories")
    plt.title("GitHub Advisory Review Lag for Critical and High CVEs")
    plt.legend()
    plt.tight_layout()

    # Save a reusable chart next to the CSV export
    plt.savefig("review_lag_histogram.png", dpi=150)
    plt.close()
    print("Saved chart to review_lag_histogram.png")

plot_review_lag(review_lags, median_lag)

Before you rerun the script, predict where the red median line will appear relative to the tallest histogram bars.

  • Switch back to the terminal session that has your secure token environment variable.
  • Regenerate the CSV and its histogram by running:
python github_cve_review_lag.py

What should you see?

The terminal prints the median review lag followed by a confirmation that review_lag_histogram.png was saved.

  • Select review_lag_histogram.png in your code editor's file sidebar to open the finished chart.

You should see histogram bars across review-lag hours. A dashed red line should carry a label showing the median number of hours.

Chart missing or empty?

  • Confirm the terminal printed the message that review_lag_histogram.png was saved.
  • Check that the plotting function call remains below its function definition.
  • Confirm review_lags contains numeric values from the CSV before Matplotlib receives it.

Help me debug my missing or empty review-lag histogram.

Your CSV now has a visual summary that exposes the typical review delay. Next, you will audit withdrawn advisories against a package you use.

Audit Withdrawn Advisories

Your histogram shows how long severe CVEs wait for GitHub review. The final question is whether an advisory tied to software you use was later withdrawn.

The GitHub GraphQL API can isolate those records with one filter. You will run that audit in Explorer. You will then compare the affected package names with one of your dependencies.

In this step, get ready to:
  • Run a second advisory query that returns withdrawn records.
  • Compare the returned package names with a dependency you use.
  • Capture the result of your withdrawn-advisory audit.
Run the withdrawn-advisory query

A withdrawn advisory has a value in withdrawnAt. The isWithdrawn filter asks the server to return only advisories with that status.

  • Return to the GitHub GraphQL Explorer from your earlier severe-advisory query.
  • Replace the severe-advisory query in Explorer with the withdrawn-advisory query below:
# Return only withdrawn advisories with affected package details
query WithdrawnAdvisories {
  securityAdvisories(first: 100, isWithdrawn: true) {
    totalCount
    nodes {
      # Show advisory identifiers and withdrawal evidence
      ghsaId
      cveId
      summary
      withdrawnAt
      permalink
      vulnerabilities(first: 100) {
        nodes {
          # Include affected package details
          package {
            ecosystem
            name
          }
          vulnerableVersionRange
        }
      }
    }
  }
}

What does this query do?

  • The isWithdrawn: true filter limits the result to withdrawn advisories.
  • The withdrawnAt field shows when each returned advisory was withdrawn.
  • The nested package object identifies the affected ecosystem plus package name.
  • The vulnerableVersionRange field shows which package versions the advisory covered.
  • The totalCount field shows how many withdrawn advisories match the filter.

Before you run the query, take a moment to predict which field should contain the clearest evidence that an advisory was withdrawn.

  • Run the query with the triangular run control in Explorer.

You will see a JSON response with a total count plus advisory nodes. Each returned node includes a withdrawal timestamp plus affected package details.

Good work. The API has now separated withdrawn records from active advisory results.

Query rejected?

Confirm that isWithdrawn uses a capital W. Check that true has no quotation marks.

Check that every opening brace has a matching closing brace.

Ask for help with the withdrawn query.

Compare the results with a package you use

Each vulnerability node identifies the package associated with its withdrawn advisory. A matching package name reveals a past advisory that covered one of your dependencies.

  • Choose one dependency you currently use from its package manifest or lockfile.
  • Record its exact package name here: your package name.
  • Return to the JSON response in Explorer.

Before you search, predict whether the package name will appear in the withdrawn-advisory results.

  • Press Cmd+F on macOS or Ctrl+F on Windows to open your browser's page search.
  • Enter your package name in the search field.

✔️ I found a package match

A match shows that one returned withdrawn advisory names your dependency.

  • Locate the matching package object in the JSON response.
  • Record its ghsaId value.
  • Record its withdrawnAt value.
  • Compare its vulnerableVersionRange value with the package version you use.

Your comparison now links a withdrawn advisory to a dependency you recognise.

ⓧ I found no package match

No match means the current result page does not name your selected dependency. That absence is still a valid result for this comparison.

  • Confirm that the package name matches the spelling used in your dependency file.
  • Record that no matching withdrawn advisory appeared on the returned page.

Your audit now documents the result without treating a missing match as an error.

That closes the loop on your advisory analysis. Your chart measures review lag. Your second query now gives you a repeatable way to audit withdrawn dependency alerts.

Secret mission

Audit the Histogram's Data Coverage

Find out how many severe advisories had enough timestamp data to contribute to your histogram. You will calculate the chart's coverage and identify whether missing NVD dates explain the excluded rows.

Clean Up Your Resources

Clean Up Your Resources

The GitHub Advisory Database creates no ongoing charges for this project. Choose whether to keep the local artifacts, pause your work, or remove everything.

Resources you used:

  • A read-only GitHub personal access token stored securely outside your project files.
  • The github_cve_review_lag.py export script.
  • The review_lag.csv advisory dataset.
  • The review_lag_histogram.png chart.

Keep everything running

No action needed. Choose this if you plan to refresh your advisory analysis each month.

  • Keep the read-only token in its secure storage location.
  • Keep github_cve_review_lag.py for future exports.
  • Keep review_lag.csv as your current advisory dataset.
  • Keep review_lag_histogram.png as your current review-lag chart.

Pause - I'll come back to this later

There is no background service to stop. Pausing means closing your tools while preserving every project artifact.

  • Close the browser tab containing GitHub GraphQL Explorer.
  • Close any open view of github_cve_review_lag.py.
  • Close any open view of review_lag.csv.
  • Close any open view of review_lag_histogram.png.
  • Leave the read-only token in its secure storage location.

Your script, dataset, chart, and token remain ready for your next monthly review.

Delete - I don't want to use this again

Deleting these items is final. Your GitHub account remains intact.

  • Return to the GitHub page where you created the read-only personal access token.
  • Locate the token you created for this project.
  • Revoke the project token.

You should see that the token is no longer active. It can no longer authorize requests to the GitHub Advisory Database.

  • Remove the revoked token value from the secure storage location you used earlier.

The file deletion steps depend on your operating system.

macOS

  • Press Cmd+Space to open system search.
  • Type Finder and press Enter.
  • Search for github_cve_review_lag.py in the Finder search field.
  • Use the search result to open its containing folder.
  • Move github_cve_review_lag.py to the Trash.
  • Move review_lag.csv to the Trash.
  • Move review_lag_histogram.png to the Trash.
  • Empty the Trash.
  • Search for github_cve_review_lag.py again to confirm its removal.

You should see no matching script. The CSV and histogram should also be absent from their former folder.

Windows

  • Press the Windows key to open system search.
  • Type File Explorer and press Enter.
  • Search for github_cve_review_lag.py in the File Explorer search field.
  • Use the search result to open its containing folder.
  • Move github_cve_review_lag.py to the Recycle Bin.
  • Move review_lag.csv to the Recycle Bin.
  • Move review_lag_histogram.png to the Recycle Bin.
  • Empty the Recycle Bin.
  • Search for github_cve_review_lag.py again to confirm its removal.

You should see no matching script. The CSV and histogram should also be absent from their former folder.

That closes out the local analysis artifacts. The revoked token can no longer access the API.

Nice Work!

Nice Work!

You did it! Your reproducible GitHub Advisory Database audit now measures review lag for severe CVEs.

You've learned how to:

  • Run a paginated GraphQL query that retrieves CVE IDs plus NVD publish times and GitHub review times.
  • Use cursor pagination in Python to export every critical or high advisory into review_lag.csv with calculated review lag hours.
  • Turn the CSV data into a histogram with the median review lag marked. Compare withdrawn advisories with a package you use to audit past dependency alerts.
  • Secret Mission: Take on the optional challenge to extend your advisory analysis.

Ready to quiz yourself?