SQL query to take all remediations for multiple asset groups and allow export to CSV

InsightVM SQL Queries
1

Our insightVM is configured with multiple remediation projects (asset groups) covering assets managed by various departments.

A manual run through the various asset groups remediation lists, elicits 1594 separate entries, across 300 servers. Creating that spreadsheet is a long and dreary process.

I’m using the following SQL, lifted from this forum and modified (probably incorrectly) - the xxxxxxx contains our domain address, so redacted here, I only want the server name not the full domain.
The aim being to get a non repeating list of rolled up vulnerabilities, with one data row per “fix”, per server - 2 identical entries should appear as 1 entry (distinct should achieve that)
As you can tell, I only need the following fields “Asset Group”,“Server”, “OS”, “Severity_level”,“Vulnerability_Solution”, which makes me feel like I have too many joins here.

I initially thought to try fact_asset_vulnerability_finding_remediation from the diagram, but this is reporting “not found” when validating the SQL.

SELECT DISTINCT
dag.name as “Asset Group”,
replace(da.host_name,’.xxxxxx’,’’) AS “Server”,
dos.system AS “OS”,
dv.severity AS “Severity_level”,
ds.summary AS “Vulnerability_Solution”
FROM fact_asset fa
inner JOIN dim_asset da ON fa.asset_id = da.asset_id
inner join dim_asset_group_asset daga ON da.asset_id = daga.asset_id
inner join dim_asset_group dag on daga.asset_group_id = dag.asset_group_id
inner JOIN dim_operating_system AS dos ON da.operating_system_id = dos.operating_system_id
inner JOIN fact_asset_vulnerability_instance favi ON da.asset_id = favi.asset_id
inner JOIN dim_vulnerability dv ON favi.vulnerability_id = dv.vulnerability_id
inner JOIN fact_asset_vulnerability_age fava ON dv.vulnerability_id = fava.vulnerability_id
inner JOIN dim_vulnerability_exploit dve ON dve.vulnerability_id = dv.vulnerability_id
left JOIN dim_asset_vulnerability_best_solution davbs ON da.asset_id = davbs.asset_id AND dv.vulnerability_id = davbs.vulnerability_id
inner JOIN dim_solution ds ON davbs.solution_id = ds.solution_id

GROUP BY “Asset Group”,“Server”, “OS”, “Severity_level”,“Vulnerability_Solution”

This SQL produces 80 results, which is way lower than expected, even allowing for issues being remediated.

Any help appreciated, I’ve been batting at this for days and getting nowhere - one attempt took over 8 hours to run, which isn’t great SQL on my part, and returned 339000 rows of “stuff”.

2

I’m not 100% sure which part of this query may be causing the fewer-than-expected results on your side, but I’ve got a couple suggestions for things to try to start troubleshooting it.

  1. What happens if you simply use da.hostname instead of the replace you have in your SELECT statement? (I assume nothing, but figured it’s worth a check)
  2. Have you tried using plain old JOIN statements, rather than the left/inner joins? They can be a bit tricky sometimes, so I like to start with a standard JOIN to make sure my data is correct, then optimize with more specific ones after that.
  3. I think you can get rid of the joins with fact_asset_vulnerability_age and dim_vulnerability_exploit to make sure that’s not impacting your data + query runtime. I don’t think they’re used elsewhere in the query.

The data looks about as expected when I run it in my test environment, in terms of there being a row per asset, per remediation. You may have to add limitations of some sort to this query, because it has the potential to return thousands and thousands of rows that the console may not be able to handle. Some folks do it by severity, for instance, and start by only returning the most severe vulnerabilities across their assets so they can tackle those first.

We do also have the Data Warehouse, which can be a good option for querying larger sets of data like you potentially want to here.