Seen a few posts requesting help with a sql query that provides the CVE, but none seemed to help. I am looking for the following:
-Vulnerability title
-CVE identifier
-Number of instances
The below query got me somewhat close, but seem to duplicate the number of actual instances
select
dv.title AS “Title”,
dvr.reference AS “CVE”,
fv.vulnerability_instances AS “Instances”
FROM
fact_vulnerability fv
JOIN dim_vulnerability dv USING (vulnerability_id)
LEFT JOIN dim_vulnerability_reference dvr ON dv.vulnerability_id = dvr.vulnerability_id AND dvr.source = ‘CVE’::text
WHERE
fv.vulnerability_instances >=1
(I am looking for vuln title and CVE for only instances >=1 so that the report is not too large.