CVE Security Vulnerability Analysis Using SQL

Steps

1. Executive Summary

IBM i 7.5 and 7.6 introduce the SYSTOOLS.CVE_INFO table function, which provides direct SQL access to the CVE (Common Vulnerabilities and Exposures) database for IBM i products. This eliminates the need to manually check external IBM support pages to determine which vulnerabilities affect a given release.

Security teams can now query CVE data in place — correlating vulnerability severity, publication dates, affected products, and IBM support URLs directly from within Db2 for i. This enables:

• Automated vulnerability inventories embedded in existing SQL-based monitoring jobs
• Prioritization workflows based on severity score and days since publication
• Release-level comparisons to assess how vulnerability exposure changes across IBM i releases
• Audit-ready reporting by joining CVE data with remediation tracking tables

2. Background — CVE Tracking on IBM i

2.1 What Is a CVE?

A CVE (Common Vulnerabilities and Exposures) is a standardized identifier assigned to a publicly known security vulnerability. Each CVE entry carries:

• A unique identifier (e.g., CVE-2024-12345)
• A severity score — Critical, High, Medium, or Low
• A publication date and a last-modified date
• A title and summary describing the vulnerability
• IBM-specific details including the affected product and a support page URL

IBM i ships with a curated feed of CVEs relevant to its own product stack. The CVE_INFO table function surfaces this feed directly in SQL, filtered by release.

2.2 How IBM i Exposes CVE Data via SQL

The SYSTOOLS.CVE_INFO table function accepts a single required parameter: IBMI_RELEASE. This parameter filters the result set to CVEs relevant to the specified IBM i release.

-- Basic invocation
SELECT * FROM TABLE(SYSTOOLS.CVE_INFO(IBMI_RELEASE => '7.6')) AS X;

The function reaches out to IBM support infrastructure to retrieve full vulnerability details, so latency may vary depending on network connectivity. For offline environments, core fields such as CVE_ID, SCORE, PUBLISH_DATE, TITLE, and SUMMARY are still available.

2.3 Understanding Severity Scores

Severity scores are assigned by IBM and may differ from CVSS base scores in some cases. Always review the https://www.ibm.com/support/pages/bulletin/ for the full assessment.

2.4 Key Fields Reference

3. SQL Queries — Inventory and Baseline

3.1 All CVEs for the Current Release

The starting point for any CVE assessment is a full inventory of known vulnerabilities for the running release.

-- All CVEs for IBM i 7.6, ordered by severity and publish date
SELECT
    CVE_ID,
    SCORE,
    PUBLISH_DATE,
    MODIFICATION_DATE,
    PRODUCT_NAME,
    TITLE,
    IBM_SUPPORT_URL
FROM TABLE(SYSTOOLS.CVE_INFO(IBMI_RELEASE => '7.6')) AS X
ORDER BY
    CASE SCORE
        WHEN 'Critical' THEN 1
        WHEN 'High'     THEN 2
        WHEN 'Medium'   THEN 3
        WHEN 'Low'      THEN 4
        ELSE 5
    END,
    PUBLISH_DATE DESC;

Sample Report

3.2 CVE Count by Severity Score

Before diving into individual vulnerabilities, understand the overall severity landscape.

-- CVE count and percentage by severity score
WITH CVE_BASE AS (
    SELECT SCORE
    FROM TABLE(SYSTOOLS.CVE_INFO(IBMI_RELEASE => '7.6')) AS X
),
TOTALS AS (
    SELECT COUNT(*) AS TOTAL_CVES FROM CVE_BASE
)
SELECT
    C.SCORE,
    COUNT(*) AS CVE_COUNT,
    TOTAL_CVES,
    CAST(
        DECIMAL(COUNT(*) * 100, 5, 1) / TOTAL_CVES
    AS DECIMAL(5,1)) AS PCT_OF_TOTAL
FROM CVE_BASE C
CROSS JOIN TOTALS
GROUP BY C.SCORE, TOTAL_CVES
ORDER BY
    CASE C.SCORE
        WHEN 'Critical' THEN 1
        WHEN 'High'     THEN 2
        WHEN 'Medium'   THEN 3
        WHEN 'Low'      THEN 4
        ELSE 5
    END;

3.3 Most Recently Published CVEs

Track newly disclosed vulnerabilities that may not yet have PTFs available.

-- 20 most recently published CVEs
SELECT
    CVE_ID, SCORE, PUBLISH_DATE, PRODUCT_NAME,
    TITLE, SUMMARY, IBM_SUPPORT_URL
FROM TABLE(SYSTOOLS.CVE_INFO(IBMI_RELEASE => '7.6')) AS X
ORDER BY PUBLISH_DATE DESC
FETCH FIRST 20 ROWS ONLY;

3.4 Most Recently Modified CVEs

IBM may revise CVE records after initial publication — for example, to update severity scores, add PTF references, or clarify affected products.

-- CVEs modified in the last 60 days
SELECT
    CVE_ID, SCORE, PUBLISH_DATE, MODIFICATION_DATE,
    PRODUCT_NAME, TITLE, IBM_SUPPORT_URL
FROM TABLE(SYSTOOLS.CVE_INFO(IBMI_RELEASE => '7.6')) AS X
WHERE MODIFICATION_DATE >= CURRENT DATE - 60 DAYS
ORDER BY MODIFICATION_DATE DESC;

4. SQL Queries — High-Priority Vulnerability Focus

4.1 Critical and High Severity CVEs

These are the CVEs that demand immediate attention. Isolate them for rapid review and remediation scheduling.

-- Critical and High severity CVEs — action required
SELECT
    CVE_ID,
    SCORE,
    PUBLISH_DATE,
    DAYS(CURRENT DATE) - DAYS(PUBLISH_DATE) AS DAYS_OPEN,
    PRODUCT_NAME,
    TITLE,
    SUMMARY,
    IBM_SUPPORT_URL
FROM TABLE(SYSTOOLS.CVE_INFO(IBMI_RELEASE => '7.6')) AS X
WHERE SCORE IN ('Critical', 'High')
ORDER BY
    CASE SCORE
        WHEN 'Critical' THEN 1
        WHEN 'High'     THEN 2
    END,
    PUBLISH_DATE ASC;

Sample Report

4.2 Unresolved CVEs Older Than 90 Days

Long-open vulnerabilities represent accumulated remediation debt. Flag anything over 90 days for escalation.

-- CVEs published more than 90 days ago — remediation overdue
SELECT
    CVE_ID, SCORE, PUBLISH_DATE,
    DAYS(CURRENT DATE) - DAYS(PUBLISH_DATE) AS DAYS_OPEN,
    PRODUCT_NAME, TITLE, IBM_SUPPORT_URL
FROM TABLE(SYSTOOLS.CVE_INFO(IBMI_RELEASE => '7.6')) AS X
WHERE PUBLISH_DATE < CURRENT DATE - 90 DAYS
ORDER BY
    CASE SCORE
        WHEN 'Critical' THEN 1
        WHEN 'High'     THEN 2
        WHEN 'Medium'   THEN 3
        WHEN 'Low'      THEN 4
        ELSE 5
    END,
    DAYS_OPEN DESC;

4.3 CVEs by Product Name

When a specific IBM i component has recently had issues, narrow the scope to that product.

-- All CVEs for a specific product — parameterise as needed
SELECT
    CVE_ID, SCORE, PUBLISH_DATE, PRODUCT_ID,
    PRODUCT_NAME, TITLE, SUMMARY, IBM_SUPPORT_URL
FROM TABLE(SYSTOOLS.CVE_INFO(IBMI_RELEASE => '7.6')) AS X
WHERE UPPER(PRODUCT_NAME) LIKE '%IBM I%'
ORDER BY
    CASE SCORE
        WHEN 'Critical' THEN 1
        WHEN 'High'     THEN 2
        WHEN 'Medium'   THEN 3
        WHEN 'Low'      THEN 4
        ELSE 5
    END,
    PUBLISH_DATE DESC;

4.4 CVEs Across All Supported Releases

Compare the CVE landscape across both 7.5 and 7.6 to understand how vulnerability exposure differs between releases.

-- CVE comparison across IBM i 7.5 and 7.6
SELECT
    CVE_ID, SCORE, PUBLISH_DATE, PRODUCT_NAME,
    IBMI_RELEASE, TITLE, IBM_SUPPORT_URL
FROM TABLE(SYSTOOLS.CVE_INFO(IBMI_RELEASE => '7.5')) AS X

UNION ALL

SELECT
    CVE_ID, SCORE, PUBLISH_DATE, PRODUCT_NAME,
    IBMI_RELEASE, TITLE, IBM_SUPPORT_URL
FROM TABLE(SYSTOOLS.CVE_INFO(IBMI_RELEASE => '7.6')) AS Y

ORDER BY CVE_ID, IBMI_RELEASE;

5. SQL Queries — Trend and Pattern Analysis

5.1 CVE Publication Trend by Month

Understanding the rate of new CVE publications helps security teams anticipate patch cycles and staffing needs.

-- CVE publication count by year and month
SELECT
    YEAR(PUBLISH_DATE)  AS PUB_YEAR,
    MONTH(PUBLISH_DATE) AS PUB_MONTH,
    COUNT(*)            AS CVE_COUNT,
    SUM(CASE WHEN SCORE = 'Critical' THEN 1 ELSE 0 END) AS CRITICAL_COUNT,
    SUM(CASE WHEN SCORE = 'High'     THEN 1 ELSE 0 END) AS HIGH_COUNT,
    SUM(CASE WHEN SCORE = 'Medium'   THEN 1 ELSE 0 END) AS MEDIUM_COUNT,
    SUM(CASE WHEN SCORE = 'Low'      THEN 1 ELSE 0 END) AS LOW_COUNT
FROM TABLE(SYSTOOLS.CVE_INFO(IBMI_RELEASE => '7.6')) AS X
GROUP BY YEAR(PUBLISH_DATE), MONTH(PUBLISH_DATE)
ORDER BY PUB_YEAR DESC, PUB_MONTH DESC;

5.2 Score Distribution Summary

A simple pivot showing how many CVEs fall into each severity band.

-- Score distribution — pivot summary
SELECT
    COUNT(*)                                             AS TOTAL_CVES,
    SUM(CASE WHEN SCORE = 'Critical' THEN 1 ELSE 0 END) AS CRITICAL,
    SUM(CASE WHEN SCORE = 'High'     THEN 1 ELSE 0 END) AS HIGH,
    SUM(CASE WHEN SCORE = 'Medium'   THEN 1 ELSE 0 END) AS MEDIUM,
    SUM(CASE WHEN SCORE = 'Low'      THEN 1 ELSE 0 END) AS LOW
FROM TABLE(SYSTOOLS.CVE_INFO(IBMI_RELEASE => '7.6')) AS X;

5.3 Most Active Products by CVE Count

Identify which IBM i products contribute the most CVEs, indicating where remediation effort should be concentrated.

-- CVE count and severity breakdown by product
SELECT
    PRODUCT_ID,
    PRODUCT_NAME,
    COUNT(*)                                             AS TOTAL_CVES,
    SUM(CASE WHEN SCORE = 'Critical' THEN 1 ELSE 0 END) AS CRITICAL,
    SUM(CASE WHEN SCORE = 'High'     THEN 1 ELSE 0 END) AS HIGH,
    SUM(CASE WHEN SCORE = 'Medium'   THEN 1 ELSE 0 END) AS MEDIUM,
    SUM(CASE WHEN SCORE = 'Low'      THEN 1 ELSE 0 END) AS LOW,
    MIN(PUBLISH_DATE)                                    AS EARLIEST_CVE,
    MAX(PUBLISH_DATE)                                    AS LATEST_CVE
FROM TABLE(SYSTOOLS.CVE_INFO(IBMI_RELEASE => '7.6')) AS X
GROUP BY PRODUCT_ID, PRODUCT_NAME
ORDER BY TOTAL_CVES DESC;

5.4 CVEs Introduced in the Last 30 Days

A focused view of brand-new CVEs for integration into a weekly or monthly security review.

-- New CVEs published in the last 30 days
SELECT
    CVE_ID, SCORE, PUBLISH_DATE, PRODUCT_NAME,
    TITLE, SUMMARY, IBM_SUPPORT_URL, X_FORCE_URL
FROM TABLE(SYSTOOLS.CVE_INFO(IBMI_RELEASE => '7.6')) AS X
WHERE PUBLISH_DATE >= CURRENT DATE - 30 DAYS
ORDER BY
    CASE SCORE
        WHEN 'Critical' THEN 1
        WHEN 'High'     THEN 2
        WHEN 'Medium'   THEN 3
        WHEN 'Low'      THEN 4
        ELSE 5
    END,
    PUBLISH_DATE DESC;

6. SQL Queries — Operational Risk Dashboard

6.1 Full CVE Risk Summary by Score and Product

A comprehensive single query that provides a prioritized, multi-dimensional view of the CVE landscape for a release.

-- CVE risk summary — score × product × age
SELECT
    SCORE,
    PRODUCT_NAME,
    CVE_ID,
    PUBLISH_DATE,
    DAYS(CURRENT DATE) - DAYS(PUBLISH_DATE) AS DAYS_OPEN,
    MODIFICATION_DATE,
    TITLE,
    IBM_SUPPORT_URL
FROM TABLE(SYSTOOLS.CVE_INFO(IBMI_RELEASE => '7.6')) AS X
ORDER BY
    CASE SCORE
        WHEN 'Critical' THEN 1
        WHEN 'High'     THEN 2
        WHEN 'Medium'   THEN 3
        WHEN 'Low'      THEN 4
        ELSE 5
    END,
    PRODUCT_NAME,
    DAYS_OPEN DESC;

Sample Report

6.2 CVE Aging Report — Days Since Publication

Segment the CVE backlog into aging buckets to measure remediation velocity over time.

-- CVE aging buckets — measure remediation debt
SELECT
    SCORE,
    SUM(CASE WHEN DAYS(CURRENT DATE) - DAYS(PUBLISH_DATE) <=  30 THEN 1 ELSE 0 END) AS "0-30 Days",
    SUM(CASE WHEN DAYS(CURRENT DATE) - DAYS(PUBLISH_DATE) BETWEEN  31 AND  60 THEN 1 ELSE 0 END) AS "31-60 Days",
    SUM(CASE WHEN DAYS(CURRENT DATE) - DAYS(PUBLISH_DATE) BETWEEN  61 AND  90 THEN 1 ELSE 0 END) AS "61-90 Days",
    SUM(CASE WHEN DAYS(CURRENT DATE) - DAYS(PUBLISH_DATE) BETWEEN  91 AND 180 THEN 1 ELSE 0 END) AS "91-180 Days",
    SUM(CASE WHEN DAYS(CURRENT DATE) - DAYS(PUBLISH_DATE)  > 180 THEN 1 ELSE 0 END) AS ">180 Days",
    COUNT(*) AS TOTAL
FROM TABLE(SYSTOOLS.CVE_INFO(IBMI_RELEASE => '7.6')) AS X
GROUP BY SCORE
ORDER BY
    CASE SCORE
        WHEN 'Critical' THEN 1
        WHEN 'High'     THEN 2
        WHEN 'Medium'   THEN 3
        WHEN 'Low'      THEN 4
        ELSE 5
    END;

7. Remediation and Operational Guidance

-- Optional: persist a baseline snapshot
CREATE TABLE QTEMP.CVE_BASELINE AS (
    SELECT CURRENT DATE AS SNAPSHOT_DATE, X.*
    FROM TABLE(SYSTOOLS.CVE_INFO(IBMI_RELEASE => '7.6')) AS X
) WITH DATA;

-- Check if a specific PTF has been applied
SELECT PTF_IDENTIFIER, PTF_LOADED_STATUS, PTF_PRODUCT_ID
FROM QSYS2.PTF_INFO
WHERE PTF_IDENTIFIER = 'MFnnnnn';

-- Alert when new Critical or High CVEs appear
SELECT CVE_ID, SCORE, PUBLISH_DATE, TITLE, IBM_SUPPORT_URL
FROM TABLE(SYSTOOLS.CVE_INFO(IBMI_RELEASE => '7.6')) AS X
WHERE SCORE IN ('Critical', 'High')
  AND PUBLISH_DATE >= CURRENT DATE - 7 DAYS
ORDER BY SCORE, PUBLISH_DATE DESC;

8. Quick Reference

CVE_INFO Table Function Syntax

SELECT * FROM TABLE(SYSTOOLS.CVE_INFO(IBMI_RELEASE => '7.6')) AS X;

Parameter: IBMI_RELEASE — required. Valid values: '7.5', '7.6'

Severity Score ORDER BY Template

ORDER BY
    CASE SCORE
        WHEN 'Critical' THEN 1
        WHEN 'High'     THEN 2
        WHEN 'Medium'   THEN 3
        WHEN 'Low'      THEN 4
        ELSE 5
    END

Days Open Calculation

DAYS(CURRENT DATE) - DAYS(PUBLISH_DATE) AS DAYS_OPEN

CVEs Published or Modified in Last N Days

WHERE PUBLISH_DATE      >= CURRENT DATE - 30 DAYS   -- new CVEs
WHERE MODIFICATION_DATE >= CURRENT DATE - 30 DAYS   -- updated CVEs

Common Predicates

Related IBM i SQL Services

References

• IBM Documentation: SYSTOOLS.CVE_INFO Table Function — IBM i 7.6
• IBM Documentation: SYSTOOLS.CVE_INFO Table Function — IBM i 7.5
• IBM X-Force: IBM X-Force Exchange