I’m attempting to retrieve critical / high fixes related to Java. I’m new to sql query export. here is the information looking to extract. #3 is not available in the “export” canned reports.
Hope that makes sense.
Here is a useful query I have been using for Java. I found it on github, but I think the repo has since been removed. It extracts the Oracle/IBM java versions/paths and aggregates the associated vulnerabilities by asset. This is to be run in the console.
WITH
dim_asset AS ( -- Filters out miscellaneous sites
SELECT asset_id, ip_address, host_name, operating_system_id, sites
FROM dim_asset
--WHERE sites !~* '^((SERVICE)|(UNKNOWNS)|(TEST))'
),
dim_aggregated_credential_status AS ( -- Filters out 'N/A' descriptions
SELECT aggregated_credential_status_id, aggregated_credential_status_description
FROM dim_aggregated_credential_status
WHERE aggregated_credential_status_description <> 'N/A'
),
owner_tag AS (
SELECT dt.tag_name, dta.asset_id
FROM dim_tag_asset dta
JOIN dim_tag dt USING(tag_id)
WHERE dt.tag_type = 'OWNER'
),
dim_asset_group AS ( -- Filters on DEPT DAG and Orphans
SELECT asset_group_id, name
FROM dim_asset_group
--WHERE name ~* '(Internal)|(External)'
),
dim_vulnerability AS ( -- Filters on Critical/High or Riskscore > 650
SELECT vulnerability_id, nexpose_id
FROM dim_vulnerability
--WHERE severity = 'Critical' OR severity = 'High'
),
fact_asset_scan_vulnerability_finding AS ( -- Filters out most recent instance
SELECT asset_id, vulnerability_id, MAX(scan_id) "scan_id"
FROM fact_asset_scan_vulnerability_finding
WHERE scan_id = lastScan(asset_id)
GROUP BY asset_id, vulnerability_id
),
vulnerabilities AS ( -- Categorizes vulns
SELECT vulnerability_id,
CASE
WHEN nexpose_id ~* '(^jre)' THEN 'o_java'
WHEN nexpose_id ~* '(^ibm-java)[^jre]' THEN 'i_java'
END "category"
FROM dim_vulnerability
),
proofs AS ( -- Extracts details from Proof
SELECT asset_id, vulnerability_id, scan_id,
SUBSTRING(proof FROM 'Vulnerable software installed: (Oracle JRE.*)\([CD\/][^<>]*\)') "o_java",
SUBSTRING(proof FROM 'Vulnerable software installed: (IBM JRE.*)\([^<>]*\)') "i_java",
SUBSTRING(proof FROM '\(([^)]+)\)') "file_path"
FROM fact_asset_scan_vulnerability_finding
JOIN fact_asset_scan_vulnerability_instance USING(asset_id, scan_id, vulnerability_id)
),
assets AS ( -- Groups details for an asset
SELECT da.asset_id, da.ip_address, da.host_name, dos.system, da.sites,
dag.name, fa.scan_finished, dacs.aggregated_credential_status_description
FROM dim_asset da
JOIN fact_asset fa USING(asset_id)
JOIN dim_asset_group_asset daga USING(asset_id)
JOIN dim_asset_group dag USING(asset_group_id)
LEFT JOIN dim_operating_system dos USING(operating_system_id)
LEFT JOIN dim_aggregated_credential_status dacs USING(aggregated_credential_status_id)
),
summary AS ( -- Counts total vulns per category, and strings together vulnerable versions
SELECT asset_id,
NULLIF(SUM((v.category = 'o_java')::INT), 0) "o_java_ct",
NULLIF(SUM((v.category = 'i_java')::INT), 0) "i_java_ct",
STRING_AGG(DISTINCT p.o_java, ',' ORDER BY p.o_java DESC) "o_java",
STRING_AGG(DISTINCT p.i_java, ',' ORDER BY p.i_java DESC) "i_java",
STRING_AGG(DISTINCT p.file_path, E'\n\r') "file_path"
FROM vulnerabilities v
JOIN proofs p USING(vulnerability_id)
GROUP BY p.asset_id
),
final AS ( -- Formats data for final select
SELECT DISTINCT
a.asset_id, a.ip_address, a.host_name, o.tag_name,
s.o_java_ct , s.o_java , s.i_java_ct , s.i_java , s.file_path,
a.aggregated_credential_status_description,
ROUND(age(a.scan_finished, 'days'), 0) "scan_finished",
CASE WHEN a.sites ~* '(EXT)' THEN 'External' ELSE 'Internal' END "scope"
FROM assets a
JOIN summary s USING(asset_id)
JOIN owner_tag o USING(asset_id)
WHERE s.o_java_ct > 0 OR s.i_java_ct > 0
)
SELECT
ip_address "IP Address",
host_name "Hostname",
scan_finished "Scan Age",
tag_name "Owner",
o_java "Oracle Java Versions",
o_java_ct "Oracle Vuln Count",
i_java "IBM Java Versions",
i_java_ct "IBM Vuln Count",
file_path "File Path",
scope "Scope",
aggregated_credential_status_description "Credential Status"
FROM final ORDER BY host_name, ip_addressI tried this query and it does not pull any results. Thoughts?
We’re trying to achieve the same via SQL query export reporting.
Any help on that please?