SQL help find all java vulns with proof

InsightVM SQL Queries
1

I’m attempting to tweak the log4j sql script that was posted here in order to find all vulnerabilities regarding java/oracle for a speific group.I have about 800 lines of nexpose ids in there which I think is causing problems. I’m looking looking for a better way to do this since the script keeps aborting itself after a couple of hours. Is there a way to use a wild card for the similar ids? Any help is appreciated. Thanks in advance.

WITH
vuln_references AS (
SELECT vulnerability_id, array_to_string(array_agg(reference), ', ') AS references
FROM dim_vulnerability
JOIN dim_vulnerability_reference USING (vulnerability_id)
GROUP BY vulnerability_id
)

SELECT array_to_string(regexp_matches(proofAsText(favi.proof), ‘([^/]+))$’, ‘g’) , ‘,’) as filename
,
da.ip_address, da.host_name, da.mac_address, da.last_assessed_for_vulnerabilities,
dv.title AS vulnerability, dvs.description AS status, favi.date AS discovered_date,
CASE WHEN favi.port = -1 THEN NULL ELSE favi.port END AS port,
dp.name AS protocol, dsvc.name AS service, proofAsText(dv.description) AS vulnerability_description,
proofAsText(favi.proof) AS proof, dv.severity, round(dv.riskscore::numeric, 0) AS risk,
round(dv.cvss_score::numeric, 2) AS cvss_score, vr.references, dv.exploits, dv.malware_kits, dv.pci_status
FROM fact_asset_vulnerability_instance favi
JOIN dim_asset da USING (asset_id)
JOIN dim_vulnerability dv USING (vulnerability_id)
JOIN dim_vulnerability_status dvs USING (status_id)
JOIN dim_protocol dp USING (protocol_id)
JOIN dim_service dsvc USING (service_id)
JOIN vuln_references vr USING (vulnerability_id)

where nexpose_id in(
‘jre-vuln-cve-2010-3550’,
‘jre-vuln-cve-2014-4222’,
<about 800 other IDs>
‘redhat_linux-cve-2022-21248’,
‘redhat_linux-cve-2022-21277’,
)
ORDER BY da.host_name, da.ip_address

2

Try this but it will retrieve the category name. The report will include the CVE ID though. Use the % at the end for a wildcard (as in Oracle %). Notice I commented out the date line but you can include it if you need a date range

SELECT da.ip_address, da.host_name, dv.title AS vulnerability_title, dos.description AS operating_system, dvc.category_name AS Vulnerability_Category, favi.key AS Key, favi.proof As Proof

FROM fact_asset_vulnerability_finding favf

JOIN dim_asset da USING (asset_id)

JOIN dim_operating_system dos USING (operating_system_id)

JOIN dim_vulnerability dv USING (vulnerability_id)

JOIN dim_vulnerability_category dvc USING (vulnerability_id)

JOIN fact_asset_vulnerability_instance favi USING (vulnerability_id)

WHERE category_name LIKE ‘Oracle %’

ORDER BY da.ip_address ASC, dv.title ASC