SQL Query for Multiple Vulns by CVE

Hello, I’m really new to SQL so forgive my generic request. I’m looking for a specific solution and I’ve only been able to get my query to spit out some of the info I’m after.

First, I’m looking for a query that will pull a list of assets based on specific CVEs and lay it out where an asset has an individual row for each cve. So, if an asset is exposed to 3 of the 7 vulns I’m looking at, that hostname would have 3 rows, one for each cve.

My problem is two-fold. Firstly, I can get the query to run but it’s only pulling back information on the first OR condition meaning, it is finding assets vulnerable to cve-1 or cve-2 but doesn’t continue looking for assets exposed to cve-3 through cve-7 from what I can tell.

Second, the results that do come back are rolled up into a single row with cve-1 and cve-2 separated by commas in the nexpose_id column rather than each cve being on it’s own row. Again, I’m super green to SQL so I’m sure I’m overlooking something obvious here.

An example of my code is below (with the actual CVEs just generically labeled 1-7). I don’t really need the solution and fix in the report. I’m really just looking for CVE ID, Asset ID, IP, and Risk Score.

Thanks in advance!

WITH remediations AS (
    SELECT DISTINCT fr.solution_id AS ultimate_soln_id, summary, fix, estimate, riskscore, dshs.solution_id AS solution_id
    FROM fact_remediation(10,'riskscore DESC') fr
    JOIN dim_solution ds USING (solution_id)
    JOIN dim_solution_highest_supercedence dshs ON (fr.solution_id = dshs.superceding_solution_id AND ds.solution_id = dshs.superceding_solution_id)
assets AS (
    SELECT DISTINCT asset_id, host_name, ip_address
    FROM dim_asset
    GROUP BY asset_id, host_name, ip_address
   csv(DISTINCT dv.nexpose_id) AS "Vulnerability InsightVM ID",
   host_name AS "Asset Hostname", ip_address AS "Asset IP",
   round(sum(dv.riskscore)) AS "Asset Risk"
FROM remediations r
   JOIN dim_asset_vulnerability_solution dvs USING (solution_id)
   JOIN dim_vulnerability dv USING (vulnerability_id)
   JOIN assets USING (asset_id)
dv.nexpose_id = 'msft-cve-1'
OR dv.nexpose_id = 'msft-cve-2'
OR dv.nexpose_id = 'msft-cve-3​'
OR dv.nexpose_id = 'msft-cve-4​'
OR dv.nexpose_id = 'msft-cve-5​'
OR dv.nexpose_id = 'msft-cve-6​'
OR dv.nexpose_id = 'msft-cve-7​'
GROUP BY r.riskscore, host_name, ip_address, asset_id
ORDER BY "Asset Risk" DESC

Hey Matt! I was looking through some of my saved queries and I found something that I think is similar to what you’re looking for. I made some small adjustments to it:

select dv.nexpose_id as "Vulnerability InsightVM ID", da.host_name,
       da.ip_address, round(dv.riskscore) as "Risk Score"
from dim_vulnerability dv
join fact_asset_vulnerability_finding favf
on dv.vulnerability_id = favf.vulnerability_id
join dim_asset da
on favf.asset_id = da.asset_id
where dv.nexpose_id in ('msft-cve-1', 'msft-cve-2', 'msft-cve-3')
group by dv.nexpose_id, da.host_name, da.ip_address, dv.riskscore

It’s different in structure to try to achieve the “one asset row per CVE” thing you mentioned. The “where” clause is also different to avoid any issues with using “OR’s.” So in this case you would just add whichever CVE’s to the list in parentheses that I created.

I hope that helps!

Thank you so much for the reply, ma’am! I just tested it and unfortunately the report came back blank (other than the headers of course) . Any ideas?

1 Like

If it’s blank, my initial thought is that it’s related to the CVE’s used in the “where” clause. Did you update those to valid values to run the query against actual vulns that exist in your environment?