Total risk score for a specified vulnerability category and asset group

InsightVM SQL Queries
1

Hello All,

I’ve created dynamic asset groups that contain my servers and each is filtered to display only assets that have a certain vulnerability category (Microsoft, cifs, java, etc.). However, when I look at the asset group list, the Total Risk column is the sum of all vulnerabilities for assets in that group, and not just the specific vulnerability category I want. Any idea how a custom SQL report could get me this tailored Total Risk score?

I know it works in the Remediation Projects section, but I’d rather have an automated monthly report with that custom total risk value instead of having to recreate the projects every month for updated numbers.

Thanks for any help.

2

Use a SQL Export report with this query. After the query exports to a CSV, you can use Excel to create a pivot table based on categories and OS.

SELECT Title, category_name, malware_kits as Malware, exploits as Exploits, round(cvss_score::numeric, 2) AS CVSS, round(cvss_v3_score::numeric, 2) AS CVSSv3, round(riskscore::numeric, 0) AS risk_score, date_published as PublishedOn, severity, affected_assets as Instances, nexpose_id
    FROM dim_vulnerability 
    JOIN dim_vulnerability_category dvc USING (vulnerability_id)
    JOIN fact_vulnerability fv USING (vulnerability_id)
    WHERE fv.vulnerability_instances > 0
    ORDER BY title ASC
1 Like
3

Thanks, Bill! That did pull the risk scores and vulnerability categories like i wanted, however the Remediation Projects section must do some funky algorithm stuff to come up with a wildly different cumulative risk score than just a straight report. This is what i was looking to do though.

1 Like