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
I haven’t tested this on my side yet, but what if you were to do your JOIN between the
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.
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"
GROUP BY 1