Hello!
We need to set up a report, i’m using the top 25 remediations one as a reference (this was the most appealing to the users), but we require it displays the maximum CVSS (a column showing 10, 9.8, etc) that will be addressed by each remediation. Any ideas on how to achieve this?
Thanks in advance!
There’s currently two versions of that query on our Public Github repo:
v1
v2
To get what you are looking for you would essentially just need to add a column for max(dv.cvss_v3_score).
The v1 report already has dim_vulnerability JOINed so may be easier to just add that column in.
Hi John, thanks for your help.
I used v2 since it looked more like I needed, but now i’m having an issue, and it’s that in the IP address and host name it is (for some items), repeating the asset info several times (say for the first remediation it shows as if there were for example 24 assets when there are only 12, because it is duplicating them or triplicating sometimes).
I was thinking a select distinct could help with this but i’m not a sql person (i’m still trting to figure this out, but unsuccessfully though )… could you give me some guidance?
thanks!
SELECT
ds.summary AS “Solution Summary”,
proofAsText(ds.fix) AS “Solution Steps”,
array_to_string(array_agg(da.ip_address), ', ') AS “IP Addresses”,
array_to_string(array_agg(da.host_name), ', ') AS “Host Names”,
CASE WHEN MAX(dv.cvss_v3_score) > 0 THEN MAX(dv.cvss_v3_score) ELSE MAX(dv.cvss_score) END AS “Max CVSS”,
CASE WHEN sum(dv.exploits) > 0 THEN ‘Yes’ ELSE ‘No’ END AS “Exploitable”,
CASE WHEN sum(dv.malware_kits) > 0 THEN ‘Yes’ ELSE ‘No’ END AS “Malware kits”
FROM
fact_remediation(25, ‘riskscore DESC’) AS fr
JOIN dim_solution AS ds ON fr.solution_id = ds.solution_id
JOIN dim_asset_vulnerability_solution davs ON fr.solution_id = davs.solution_id
JOIN dim_vulnerability dv USING (vulnerability_id)
JOIN dim_asset AS da ON davs.asset_id = da.asset_id
WHERE
dv.cvss_score >= 7
or dv.cvss_v3_score >= 7
GROUP BY
ds.summary,
ds.fix
Typically speaking when I see duplicate or triplicate lines it’s because there’s another column in there that wasn’t joined properly. e.g. two different tables both have an asset_id column but aren’t joined together so the query is assuming they are separate values and creating a row for each id in each table.
Typically speaking anytime I run into this and don’t know what columns are causing the issue I just get rid of my selected columns and do SELECT * which just gives me ALL the columns so I can see which ones need to be joined on.
Given that this query is looking for vulnerabilities I’m willing to bet it’s the nexpose_id column that’s creating the issue.
Thanks for your help, I used included some select distincts and got what i needed 