DWH Superceding Solution

InsightVM SQL Queries
1

Hello All,

I am trying to aggregate the fix based on the highest superceding solution in the insightvm datawarehouse. It keeps on including all the solution fixes where I only need the select few from the superceding ones. Here is the SQL query I have that joins all of the solutions. Any suggestions?

SELECT DISTINCT davfs.asset_id, string_agg(htmltotext(ds.fix), ', ') AS solution FROM dim_solution_highest_supercedence dshs JOIN dim_solution ds USING (solution_id) JOIN dim_asset_vulnerability_finding_solution davfs USING (solution_id) WHERE davfs.asset_id = ‘’ GROUP BY davfs.asset_id

2

I haven’t tested this on my side yet, but what if you were to do your JOIN between the dim_solution and dim_solution_highest_supercedence tables differently? Maybe doing a JOIN with the superceding_solution_id so when you’re referencing your fix in the SELECT, it’s correctly referring to the superceding one.

3

I think this will give you what you are looking for:

WITH a AS (
	SELECT DISTINCT davfs.asset_id, 
	htmltotext(ds.fix) AS solution
	FROM dim_asset_vulnerability_finding_solution davfs
	JOIN dim_solution ds USING (solution_id)  
	JOIN dim_solution_highest_supercedence dshs USING (solution_id)
	WHERE davfs.asset_id = 58395
)
SELECT asset_id, STRING_AGG(solution, ', ') AS "solution"
FROM a
GROUP BY 1
1 Like