SQL query to include cve number column

InsightVM
1

I am working on an sql query report but need to include the cve. At the moment the VCE is in the title field but I would like it in a separate column. is there a cheat sheet?

current query
SELECT da.host_name, da.ip_address, ds.nexpose_id, dv.title, ds.applies_to, ds.solution_type, ds.fix

FROM dim_solution ds

JOIN dim_vulnerability_solution dvs USING(solution_id)

JOIN dim_asset_vulnerability_solution davs USING(vulnerability_id)

JOIN dim_vulnerability dv USING(vulnerability_id)

JOIN dim_asset da USING(asset_id)

WHERE ds.nexpose_id ilike ‘%CVE-2018-20250%’

OR dv.title ilike ‘%CVE-2017-11882%’

OR dv.title ilike ‘%CVE-2017-11774%’

2

Hi there,

You can use [dim_vulnerability_reference] which includes the CVE where the [source] = CVE.

Example:


 LEFT JOIN dim_vulnerability_reference dvr ON dv.vulnerability_id = dvr.vulnerability_id AND dvr.source = 'CVE'::text


 **FYI: dv. = dim_vulnerability**

After joining, you can use this logic which combines both options for populating the CVE by first using the reference table and then trying the nexpose_id if null. On a few occasions I noticed the reference table didn’t return a value even though the nespose_id contained one.

CASE
   WHEN dvr.reference IS NULL AND dv.nexpose_id ~~ '%cve-%'::text 
      THEN upper("right"(dv.nexpose_id, length(dv.nexpose_id) - ("position"(dv.nexpose_id, 'cve-'::text) - 1)))
      ELSE dvr.reference
END AS cve
1 Like