Skip to content

Instantly share code, notes, and snippets.

@buzzia2001

buzzia2001/CVEAnalysis.sql

Last active September 1, 2026 14:00
Show Gist options

  • Select an option

    Learn more about clone URLs
  • Save buzzia2001/0bb0dab14d63205377dcc395ef078c7b to your computer and use it in GitHub Desktop.

Select an option

Learn more about clone URLs
Save buzzia2001/0bb0dab14d63205377dcc395ef078c7b to your computer and use it in GitHub Desktop.
Download ZIP
Performing CVE Analysis Using SQL
Raw
CVEAnalysis.sql
-- Purpose: This first query replaces the use of the SYSTOOLS HTTPGETCLOB functions with the QSYS2.HTTPGET function, which performs much
-- better because it does not use Java.
-- Version: 1.0
-- Date 26/08/2026
-- Author: Andrea Buzzi
-- Docs: https://www.ibm.com/docs/en/i/7.6.0?topic=functions-http-get-http-get-blob
-- https://www.ibm.com/docs/en/i/7.6.0?topic=functions-json-table
CREATE OR REPLACE FUNCTION SQLTOOLS.CVE_LIST (
IBMI_RELEASE CHAR(3) CCSID 1208 DEFAULT (
SELECT SYSIBMADM.ENV_SYS_INFO.OS_VERSION CONCAT '.' CONCAT SYSIBMADM.ENV_SYS_INFO.OS_RELEASE
FROM SYSIBMADM.ENV_SYS_INFO)
)
RETURNS TABLE (
CVE_ID VARCHAR(20) CCSID 1208,
SCORE VARCHAR(20) CCSID 1208,
PUBLISH_DATE DATE,
TITLE VARCHAR(2000) CCSID 1208,
IBM_SUPPORT_URL VARCHAR(200) CCSID 1208,
"SUMMARY" VARCHAR(2000) CCSID 1208,
DESCRIPTION VARCHAR(5000) CCSID 1208,
PRODUCT_ID VARCHAR(7) CCSID 1208,
PRODUCT_NAME VARCHAR(50) CCSID 1208,
IBMI_RELEASE CHAR(3) CCSID 1208,
MODIFICATION_DATE DATE,
X_FORCE_URL VARCHAR(100) CCSID 1208,
FIELD_VULNERABILITY_DETAILS CLOB(102400) CCSID 1208,
AFFECTED_PRODUCTS VARCHAR(3000) CCSID 1208
)
LANGUAGE SQL
SPECIFIC SQLTOOLS.CVELIST
NOT DETERMINISTIC
MODIFIES SQL DATA
CALLED ON NULL INPUT
NO EXTERNAL ACTION
NOT FENCED
SYSTEM_TIME SENSITIVE NO
SET OPTION ALWBLK = *ALLREAD,
ALWCPYDTA = *OPTIMIZE,
COMMIT = *NONE,
DECRESULT = (31,
31,
00),
DFTRDBCOL = QSYS2,
DLYPRP = *NO,
DYNDFTCOL = *NO,
DYNUSRPRF = *USER,
SRTSEQ = *HEX,
USRPRF = *USER
BEGIN
RETURN
SELECT CVE_ID,
CVE_BASE_SCORE,
DATE(TIMESTAMP_FORMAT(CHAR(CVE_PUBLISH_DATE), 'YYYY-MM-DD')),
CVE_TITLE,
CVE_URL,
CVE_SUMMARY,
CVE_DESCRIPTION,
CVE_LICPGM,
CVE_PRODUCT,
IBMI_RELEASE,
DATE(TIMESTAMP_FORMAT(CHAR(CVE_MODIFICATION_DATE), 'YYYY-MM-DD')),
CVE_X_FORCE_URL,
CVE_FIELD_VULNERABILITY_DETAILS,
CVE_AFFECTED_PRODUCTS
FROM JSON_TABLE(
QSYS2.HTTP_GET(
'https://www.ibm.com/support/pages/securityapp/api/search?q=IBM%20i', '{"sslTolerate":"true"}'), --use sslTolerate to disable certificate check (or better import ca inside DCM)
'lax $.results[*]'
COLUMNS(
CVE_ID VARCHAR(20) CCSID 1208 PATH '$.field_cve_id',
CVE_BASE_SCORE VARCHAR(20) CCSID 1208 PATH '$.field_cvss_base_score',
CVE_PUBLISH_DATE VARCHAR(200) CCSID 1208 PATH '$.field_pub_date',
CVE_TITLE VARCHAR(2000) CCSID 1208 PATH '$.title',
CVE_URL VARCHAR(200) CCSID 1208 PATH '$.field_published_url',
CVE_SUMMARY VARCHAR(2000) CCSID 1208 PATH '$.field_summary',
CVE_DESCRIPTION VARCHAR(5000) CCSID 1208 PATH '$.field_cvss_desc',
CVE_LICPGM VARCHAR(7) CCSID 1208 PATH '$.field_offering_pid_number',
CVE_PRODUCT VARCHAR(50) CCSID 1208 PATH '$.field_product',
CVE_AFFECTED_PRODUCTS VARCHAR(7000) CCSID 1208 PATH '$.field_affected_products',
CVE_MODIFICATION_DATE VARCHAR(200) CCSID 1208 PATH '$.modified_date',
CVE_X_FORCE_URL VARCHAR(100) CCSID 1208 PATH '$.field_x_force_url',
CVE_FIELD_VULNERABILITY_DETAILS CLOB(102400) CCSID 1208 PATH '$.field_vulnerability_details'
)
)
WHERE CVE_AFFECTED_PRODUCTS LIKE '%' CONCAT IBMI_RELEASE CONCAT '%';
END;
LABEL ON SPECIFIC ROUTINE SQLTOOLS.CVELIST
IS 'IBM i CVE list published by IBM Support';
COMMENT ON PARAMETER SPECIFIC ROUTINE SQLTOOLS.CVELIST (
IBMI_RELEASE IS 'IBM i release, e.g. 7.6 (default: this system)'
);
stop;
-- Example
SELECT * FROM TABLE (SQLTOOLS.CVE_LIST('7.6'));
stop;
-- Purpose: CVE_LIST returns the IBM Support web page with details about the CVE. This page is static, so I parse the tables in the HTML
-- file to identify the affected products (including LPP and the “if present” option) as well as any corrective PTFs.
-- Version: 2.0
-- Date 01/09/2026
-- Author: Andrea Buzzi
-- Docs: https://www.ibm.com/docs/en/i/7.6.0?topic=functions-http-get-http-get-blob
-- https://www.ibm.com/docs/en/i/7.6.0?topic=functions-json-table
-- Note: This UDTF is based on an analysis of an HTML page from the IBM website. If the format changes, it may no longer work
CREATE OR REPLACE FUNCTION SQLTOOLS.GET_CVE_PTFS (
P_URL VARCHAR(200) CCSID 1208,
P_IBMI_RELEASE CHAR(3) CCSID 1208
)
RETURNS TABLE (
RELEASE_VER VARCHAR(10) CCSID 1208,
LPP_PRODUCT CHAR(7) CCSID 1208,
PTF_NUMBER CHAR(7) CCSID 1208,
GROUP_LEVEL INTEGER
)
LANGUAGE SQL
SPECIFIC SQLTOOLS.CVEPTFS
NOT DETERMINISTIC
MODIFIES SQL DATA
CALLED ON NULL INPUT
NO EXTERNAL ACTION
NOT FENCED
SYSTEM_TIME SENSITIVE NO
SET OPTION ALWBLK = *ALLREAD,
ALWCPYDTA = *OPTIMIZE,
COMMIT = *NONE,
DECRESULT = (31,
31,
00),
DFTRDBCOL = QSYS2,
DLYPRP = *NO,
DYNDFTCOL = *NO,
DYNUSRPRF = *USER,
SRTSEQ = *HEX,
USRPRF = *USER
BEGIN
RETURN
WITH HTML_DATA AS (
-- 1. Download the page
SELECT CAST(QSYS2.HTTP_GET(P_URL, '{"sslTolerate":"true"}') AS CLOB(2M) CCSID 1208) AS CONTENT
FROM SYSIBM.SYSDUMMY1
),
-- 2. The headings that delimit the two sections we need
-- section 1 -> "Affected Products and Versions" .. "Remediation/Fixes"
-- section 2 -> "Remediation/Fixes" .. "Workarounds and Mitigations"
SEC_DEF (SECTION_ID, START_PAT, END_PAT) AS (
VALUES
(
1,
'(?i)Affected\s+Products\s+and\s+Versions',
'(?i)Remediation\s*/\s*Fixes'
),
(
2,
'(?i)Remediation\s*/\s*Fixes',
'(?i)Workarounds\s+and\s+Mitigations'
)
),
-- 3a. Where each section starts (return option 1 = just after the heading)
BOUND_START AS (
SELECT D.SECTION_ID,
H.CONTENT,
D.END_PAT,
REGEXP_INSTR(H.CONTENT,
D.START_PAT,
1, 1, 1, 'n') AS S_POS
FROM HTML_DATA H
CROSS JOIN SEC_DEF D
),
-- 3b. Where it ends, searching forward only
BOUND_END AS (
SELECT SECTION_ID,
CONTENT,
S_POS,
REGEXP_INSTR(CONTENT,
END_PAT,
S_POS, 1, 0, 'n') AS E_POS
FROM BOUND_START
WHERE S_POS > 0
),
-- 4. Cut the page between the two positions (to the end if no closing heading)
SECTIONS AS (
SELECT SECTION_ID,
CAST(SUBSTR(CONTENT, S_POS,
CASE
WHEN E_POS > S_POS THEN E_POS - S_POS
ELSE LENGTH(CONTENT) - S_POS + 1
END) AS CLOB(512K) CCSID 1208) AS SECTION_HTML
FROM BOUND_END
),
-- 5. Numbers 1..50, joined whenever we need "the Nth occurrence of something"
NUMS AS (
SELECT ROWNUMBER() OVER (
) AS N
FROM QSYS2.SYSTABLES
FETCH FIRST 50 ROWS ONLY),
-- 6. The tables: one in section 1, up to ten in section 2 (one per component)
TABLES AS (
SELECT S.SECTION_ID,
CAST(REGEXP_SUBSTR(S.SECTION_HTML,
'(?i)<table[^>]*>.*?</table>', 1, N.N, 'n') AS VARCHAR(12000) CCSID 1208) AS TABLE_HTML
FROM SECTIONS S
CROSS JOIN NUMS N
WHERE N.N <= 10
AND (S.SECTION_ID = 2
OR N.N = 1)
),
-- 7. The licensed program is written in the header row of each table, in the
-- column that lists the PTFs ("5770-SS1 PTF Number(s)", "5770-999 ...").
-- The dash is dropped so the value matches QSYS2.SOFTWARE_PRODUCT_INFO.
TABLES_LPP AS (
SELECT SECTION_ID,
TABLE_HTML,
CAST(REGEXP_REPLACE(REGEXP_SUBSTR(REGEXP_SUBSTR(TABLE_HTML,
'(?i)<tr[^>]*>.*?</tr>', 1, 1, 'n'),
'[0-9]{4}[- ]?[A-Z0-9]{3}', 1, 1, 'n'),
'[- ]',
'', 1, 0, 'n') AS VARCHAR(10) CCSID 1208) AS LPP_PRODUCT
FROM TABLES
WHERE TABLE_HTML IS NOT NULL
),
-- 8. Split each table into its <tr> rows
RAW_ROWS AS (
SELECT T.SECTION_ID,
T.LPP_PRODUCT,
CAST(REGEXP_SUBSTR(T.TABLE_HTML,
'(?i)<tr[^>]*>.*?</tr>', 1, N.N, 'n') AS VARCHAR(2000) CCSID 1208) AS ROW_TEXT
FROM TABLES_LPP T
CROSS JOIN NUMS N
),
-- 9. First two cells of each row, stripped of tags and extra blanks
CELLS AS (
SELECT SECTION_ID,
LPP_PRODUCT,
ROW_TEXT,
CAST(TRIM(REGEXP_REPLACE(REGEXP_REPLACE(REGEXP_REPLACE(REGEXP_SUBSTR(ROW_TEXT,
'(?i)<t[dh][^>]*>.*?</t[dh]>', 1, 1, 'n'),
'<[^>]*>',
'', 1, 0, 'n'),
'&nbsp;|&#160;',
' ', 1, 0, 'n'),
'\s+',
' ', 1, 0, 'n')) AS VARCHAR(200) CCSID 1208) AS C1,
CAST(TRIM(REGEXP_REPLACE(REGEXP_REPLACE(REGEXP_REPLACE(REGEXP_SUBSTR(ROW_TEXT,
'(?i)<t[dh][^>]*>.*?</t[dh]>', 1, 2, 'n'),
'<[^>]*>',
'', 1, 0, 'n'),
'&nbsp;|&#160;',
' ', 1, 0, 'n'),
'\s+',
' ', 1, 0, 'n')) AS VARCHAR(200) CCSID 1208) AS C2
FROM RAW_ROWS
WHERE ROW_TEXT IS NOT NULL
),
-- 10. Affected products: C1 is the product name, C2 the version.
-- The version test also drops the header row.
AFFECTED AS (
SELECT C2 AS RELEASE_VER,
C1 AS PRODUCT
FROM CELLS
WHERE SECTION_ID = 1
AND REGEXP_LIKE (
C2,
'^[0-9]+\.[0-9]+$',
'n'
)
),
-- 11. Remediation rows: first cell is the release, the rest holds the PTFs
REMEDIATION AS (
SELECT C1 AS RELEASE_VER,
LPP_PRODUCT,
ROW_TEXT
FROM CELLS
WHERE SECTION_ID = 2
AND REGEXP_LIKE (
C1,
'^[0-9]+\.[0-9]+$',
'n'
)
),
-- 12. Match on the release and read up to twenty PTF numbers per row
EXTRACTED_PTFS AS (
SELECT CAST(R.RELEASE_VER AS VARCHAR(10) CCSID 1208) AS RELEASE_VER,
CAST(A.PRODUCT AS VARCHAR(100) CCSID 1208) AS PRODUCT,
R.LPP_PRODUCT,
-- Nth PTF (or SF99 group) number in the row
CAST(REGEXP_SUBSTR(R.ROW_TEXT,
'([A-Z]{2}[0-9]{5}|SF99[0-9]{3})', 1, N.N, 'n') AS VARCHAR(10) CCSID 1208) AS PTF_NUMBER,
-- Group PTFs only: the number written after "Level"
CASE
WHEN
REGEXP_SUBSTR(R.ROW_TEXT,
'([A-Z]{2}[0-9]{5}|SF99[0-9]{3})', 1, N.N, 'n') LIKE 'SF99%'
THEN CAST(VARCHAR_FORMAT(REGEXP_SUBSTR(R.ROW_TEXT,
'(?i)Level\s*([0-9]+)',
1, 1, 'n', 1)) AS INTEGER)
ELSE NULL
END AS GROUP_LEVEL
FROM REMEDIATION R
CROSS JOIN NUMS N
LEFT JOIN AFFECTED A
ON A.RELEASE_VER = R.RELEASE_VER
WHERE N.N <= 20
AND (P_IBMI_RELEASE IS NULL
OR TRIM(P_IBMI_RELEASE) = ''
OR R.RELEASE_VER = TRIM(P_IBMI_RELEASE))
)
-- 13. Each PTF appears both in the cell and in its download link: DISTINCT
SELECT DISTINCT RELEASE_VER,
LPP_PRODUCT,
PTF_NUMBER,
GROUP_LEVEL
FROM EXTRACTED_PTFS
WHERE PTF_NUMBER IS NOT NULL;
END;
LABEL ON SPECIFIC ROUTINE SQLTOOLS.CVEPTFS
IS 'Corrective PTFs parsed from a CVE support page';
COMMENT ON PARAMETER SPECIFIC ROUTINE SQLTOOLS.CVEPTFS (
P_URL IS 'URL of the IBM Support page for the CVE',
P_IBMI_RELEASE IS 'IBM i release to keep, blank or null = all'
);
stop;
-- Examples:
SELECT * FROM TABLE (SQLTOOLS.GET_CVE_PTFS('https://www.ibm.com/support/pages/node/7283573', '7.6'));-- 1 PTF only
SELECT * FROM TABLE (SQLTOOLS.GET_CVE_PTFS('https://www.ibm.com/support/pages/node/7283578', '7.6')); -- mutliple PTFs
stop;
-- Purpose: We have a UDTF that extracts the list of CVEs, and we have a service that reads the IBM page to identify corrective PTFs,
-- so why not combine the two?
-- Version: 1.0
-- Date 26/08/2026
-- Author: Andrea Buzzi
CREATE OR REPLACE FUNCTION SQLTOOLS.CVE_LIST_DETAILED (
IBMI_RELEASE CHAR(3) CCSID 1208 DEFAULT (
SELECT SYSIBMADM.ENV_SYS_INFO.OS_VERSION CONCAT '.' CONCAT SYSIBMADM.ENV_SYS_INFO.OS_RELEASE
FROM SYSIBMADM.ENV_SYS_INFO)
)
RETURNS TABLE (
CVE_ID VARCHAR(20) CCSID 1208,
SCORE VARCHAR(20) CCSID 1208,
PUBLISH_DATE DATE,
TITLE VARCHAR(2000) CCSID 1208,
"SUMMARY" VARCHAR(2000) CCSID 1208,
DESCRIPTION VARCHAR(5000) CCSID 1208,
LPP_PRODUCT VARCHAR(7) CCSID 1208,
PRODUCT_NAME VARCHAR(50) CCSID 1208,
IBMI_RELEASE CHAR(3) CCSID 1208,
MODIFICATION_DATE DATE,
PTF_NUMBER VARCHAR(7) CCSID 1208,
GROUP_LEVEL INTEGER,
FIELD_VULNERABILITY_DETAILS CLOB(102400) CCSID 1208,
AFFECTED_PRODUCTS VARCHAR(3000) CCSID 1208,
IBM_SUPPORT_URL VARCHAR(200) CCSID 1208,
X_FORCE_URL VARCHAR(100) CCSID 1208
)
LANGUAGE SQL
SPECIFIC SQLTOOLS.CVELISTDTL
NOT DETERMINISTIC
MODIFIES SQL DATA
CALLED ON NULL INPUT
NO EXTERNAL ACTION
NOT FENCED
SYSTEM_TIME SENSITIVE NO
SET OPTION ALWBLK = *ALLREAD,
ALWCPYDTA = *OPTIMIZE,
COMMIT = *NONE,
DECRESULT = (31,
31,
00),
DFTRDBCOL = QSYS2,
DLYPRP = *NO,
DYNDFTCOL = *NO,
DYNUSRPRF = *USER,
SRTSEQ = *HEX,
USRPRF = *USER
BEGIN
RETURN SELECT C.CVE_ID,
C.SCORE,
C.PUBLISH_DATE,
C.TITLE,
C."SUMMARY",
C.DESCRIPTION,
P.LPP_PRODUCT,
C.PRODUCT_NAME,
C.IBMI_RELEASE,
C.MODIFICATION_DATE,
P.PTF_NUMBER,
P.GROUP_LEVEL,
C.FIELD_VULNERABILITY_DETAILS,
C.AFFECTED_PRODUCTS,
C.IBM_SUPPORT_URL,
C.X_FORCE_URL
FROM TABLE (
SQLTOOLS.CVE_LIST(IBMI_RELEASE)
) C
LEFT JOIN TABLE (
SQLTOOLS.GET_CVE_PTFS(C.IBM_SUPPORT_URL, C.IBMI_RELEASE)
) P
ON 1 = 1;
END;
LABEL ON SPECIFIC ROUTINE SQLTOOLS.CVELISTDTL
IS 'IBM i CVE list joined with its corrective PTFs';
COMMENT ON PARAMETER SPECIFIC ROUTINE SQLTOOLS.CVELISTDTL (
IBMI_RELEASE IS 'IBM i release, e.g. 7.6 (default: this system)'
);
stop;
--Example
SELECT * FROM TABLE (SQLTOOLS.CVE_LIST_DETAILED('7.6'));
stop;
-- Purpose: Let's check if my system is actually covered or not...
-- Version: 1.0
-- Date 26/08/2026
-- Author: Andrea Buzzi
-- Docs: https://www.ibm.com/docs/en/i/7.6.0?topic=services-ptf-info-view
-- https://www.ibm.com/docs/en/i/7.6.0?topic=services-data-area-info-table-function
-- https://www.ibm.com/docs/en/i/7.6.0?topic=services-software-product-info-view
CREATE OR REPLACE FUNCTION SQLTOOLS.CVE_STATUS (
IS_AFFECTED VARCHAR(12) CCSID 1208 DEFAULT NULL,
IS_COVERED VARCHAR(11) CCSID 1208 DEFAULT NULL,
SCORE_CRIT VARCHAR(10) CCSID 1208 DEFAULT NULL
)
RETURNS TABLE (
CVE_ID VARCHAR(20) CCSID 1208,
SCORE VARCHAR(20) CCSID 1208,
TITLE VARCHAR(2000) CCSID 1208,
PUBLISH_DATE DATE,
LPP_PRODUCT VARCHAR(7) CCSID 1208,
PTF_NUMBER VARCHAR(7) CCSID 1208,
AFFECTED_STATUS VARCHAR(40) CCSID 1208,
COVERED_STATUS VARCHAR(40) CCSID 1208,
REMEDIATION_DATE DATE
)
LANGUAGE SQL
SPECIFIC SQLTOOLS.CVESTATUS
NOT DETERMINISTIC
MODIFIES SQL DATA
CALLED ON NULL INPUT
NO EXTERNAL ACTION
NOT FENCED
SYSTEM_TIME SENSITIVE NO
SET OPTION ALWBLK = *ALLREAD,
ALWCPYDTA = *OPTIMIZE,
COMMIT = *NONE,
DECRESULT = (31,
31,
00),
DFTRDBCOL = QSYS2,
DLYPRP = *NO,
DYNDFTCOL = *NO,
DYNUSRPRF = *USER,
SRTSEQ = *HEX,
USRPRF = *USER
BEGIN
-- Extract current release
DECLARE V_RELEASE VARCHAR(3) CCSID 1208;
SELECT SUBSTR(D.DATA_AREA_VALUE, 2, 1) CONCAT '.' CONCAT SUBSTR(D.DATA_AREA_VALUE, 4, 1)
INTO V_RELEASE
FROM TABLE (
QSYS2.DATA_AREA_INFO(DATA_AREA_NAME => 'QSS1MRI', DATA_AREA_LIBRARY => 'QUSRSYS')
) D;
-- Extract applied PTFs
DROP TABLE IF EXISTS SESSION.PTFINSTALLED;
DECLARE GLOBAL TEMPORARY TABLE PTFINSTALLED AS
(SELECT PTF_IDENTIFIER,
PTF_TEMPORARY_APPLY_TIMESTAMP
FROM QSYS2.PTF_INFO
WHERE PTF_LOADED_STATUS IN ('APPLIED', 'PERMANENTLY APPLIED', 'SUPERSEDED'))
WITH DATA;
-- Extract installed LPPs
DROP TABLE IF EXISTS SESSION.LPPINSTALLED;
DECLARE GLOBAL TEMPORARY TABLE LPPINSTALLED AS
(SELECT DISTINCT PRODUCT_ID
FROM QSYS2.SOFTWARE_PRODUCT_INFO)
WITH DATA;
-- Extract CVEs
DROP TABLE IF EXISTS SESSION.CVELIST;
DECLARE GLOBAL TEMPORARY TABLE CVELIST AS
(SELECT X.CVE_ID,
X.SCORE,
X.TITLE,
X.PUBLISH_DATE,
X.LPP_PRODUCT,
X.PTF_NUMBER,
CASE
WHEN Z.PRODUCT_ID IS NOT NULL
OR X.LPP_PRODUCT IS NULL THEN 'AFFECTED'
ELSE 'NOT AFFECTED'
END AS AFFECTED_STATUS,
CASE
WHEN Y.PTF_IDENTIFIER IS NULL THEN 'NOT COVERED'
ELSE 'COVERED'
END AS COVERED_STATUS,
DATE(Y.PTF_TEMPORARY_APPLY_TIMESTAMP) AS REMEDIATION_DATE
FROM TABLE (
SQLTOOLS.CVE_LIST_DETAILED()
) X
LEFT JOIN SESSION.PTFINSTALLED Y
ON X.PTF_NUMBER = Y.PTF_IDENTIFIER
LEFT JOIN SESSION.LPPINSTALLED Z
ON Z.PRODUCT_ID = X.LPP_PRODUCT)
WITH NO DATA;
INSERT INTO SESSION.CVELIST
SELECT X.CVE_ID,
X.SCORE,
X.TITLE,
X.PUBLISH_DATE,
X.LPP_PRODUCT,
X.PTF_NUMBER,
CASE
WHEN Z.PRODUCT_ID IS NOT NULL
OR X.LPP_PRODUCT IS NULL THEN 'AFFECTED'
ELSE 'NOT AFFECTED'
END AS AFFECTED_STATUS,
CASE
WHEN Y.PTF_IDENTIFIER IS NULL THEN 'NOT COVERED'
ELSE 'COVERED'
END AS COVERED_STATUS,
DATE(Y.PTF_TEMPORARY_APPLY_TIMESTAMP) AS REMEDIATION_DATE
FROM TABLE (
SQLTOOLS.CVE_LIST_DETAILED(V_RELEASE)
) X
LEFT JOIN SESSION.PTFINSTALLED Y
ON X.PTF_NUMBER = Y.PTF_IDENTIFIER
LEFT JOIN SESSION.LPPINSTALLED Z
ON Z.PRODUCT_ID = X.LPP_PRODUCT;
-- Check user filters
RETURN SELECT CVE_ID,
SCORE,
TITLE,
PUBLISH_DATE,
LPP_PRODUCT,
PTF_NUMBER,
AFFECTED_STATUS,
COVERED_STATUS,
REMEDIATION_DATE
FROM SESSION.CVELIST
WHERE (IS_AFFECTED IS NULL
OR TRIM(IS_AFFECTED) IN ('', '*ALL')
OR AFFECTED_STATUS = UPPER(TRIM(IS_AFFECTED)))
AND (IS_COVERED IS NULL
OR TRIM(IS_COVERED) IN ('', '*ALL')
OR COVERED_STATUS = UPPER(TRIM(IS_COVERED)))
AND (SCORE_CRIT IS NULL
OR TRIM(SCORE_CRIT) IN ('', '*ALL')
OR UPPER(SCORE) = UPPER(TRIM(SCORE_CRIT)));
END;
LABEL ON SPECIFIC ROUTINE SQLTOOLS.CVESTATUS
IS 'CVE exposure and PTF coverage of this system';
COMMENT ON PARAMETER SPECIFIC ROUTINE SQLTOOLS.CVESTATUS (
IS_AFFECTED IS 'AFFECTED, NOT AFFECTED or *ALL (default)',
IS_COVERED IS 'COVERED, NOT COVERED or *ALL (default)',
SCORE_CRIT IS 'Critical, High, Medium, Low or *ALL (default)'
);
stop;
-- Examples
SELECT * FROM TABLE (SQLTOOLS.CVE_STATUS()); -- Get all CVEs info related to your system
SELECT * FROM TABLE (SQLTOOLS.CVE_STATUS(IS_AFFECTED => 'AFFECTED', IS_COVERED => 'NOT COVERED', SCORE_CRIT => 'Medium')); -- Only the MEDIUM ones we are not covered for
stop;
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment