I am trying to write a query that summarizes vulnerabilities by host name, and includes information about that host. Query is running in Rapid7 InsightVM
The query that returns asset information runs successfully, except when I append that query to return vulnerability information it returns an ambiguous reference error for description. But the ip address, host_name, and asset_id values returns just fine.
I am just trying to combined them together to return that information. I feel like something obvious is missing.
This returns what I want from the asset table including the OS Description (Windows, RHEL, etc):
SELECT da.asset_id, da.host_name, da.ip_address, dos.description FROM dim_asset da JOIN dim_operating_system dos ON dos.operating_system_id = da.operating_system_id JOIN fact_asset fa ON fa.asset_id = da.asset_id GROUP BY da.asset_id, da.host_name, da.ip_address, dos.description This returns an ambiguous reference for the description, it works for asset_id, host_name, and ip_address:
WITH remediations AS ( SELECT DISTINCT fr.solution_id AS ultimate_soln_id, summary, fix, estimate, riskscore, dshs.solution_id AS solution_id FROM fact_remediation(10,'riskscore DESC') fr JOIN dim_solution ds USING (solution_id) JOIN dim_solution_highest_supercedence dshs ON (fr.solution_id = dshs.superceding_solution_id AND ds.solution_id = dshs.superceding_solution_id) ), assets AS ( SELECT da.asset_id, da.host_name, da.ip_address, dos.description FROM dim_asset da JOIN dim_operating_system dos ON dos.operating_system_id = da.operating_system_id JOIN fact_asset fa ON fa.asset_id = da.asset_id GROUP BY da.asset_id, da.host_name, da.ip_address, dos.description ) SELECT csv(DISTINCT dv.title) AS "Vulnerability Title", host_name AS "Asset Hostname", ip_address AS "Asset IP", description AS "OS", round(sum(dv.riskscore)) AS "Asset Risk", summary AS "Solution", fix as "Fix" FROM remediations r JOIN dim_asset_vulnerability_solution dvs USING (solution_id) JOIN dim_vulnerability dv USING (vulnerability_id) JOIN assets USING (asset_id) GROUP BY r.riskscore, host_name, ip_address, asset_id, summary, fix ORDER BY "Asset Risk" DESC WITH remediations AS ( 1 Answer
Most likely, dim_asset_vulnerability_solution or dim_vulnerability also have a description field. Just qualifying the selected fields with their intended source should resolve this problem.
... a.host_name AS "Asset Hostname", a.ip_address AS "Asset IP", a.description AS "OS" ... JOIN assets AS a USING (asset_id) ... GROUP BY r.riskscore, a.host_name, a.ip_address, asset_id, summary, fix Note: asset_id is not a problem because USING has some extra "magic" that merges the references joined by it.
Comment: Unless there are very specific reasons, GROUP BY should not be used as a substitute for SELECT DISTINCT (referring to the assets CTE in particular)
