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
)
SELECT DISTINCT
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)
WHERE (
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