Field used for Vulnerability Solutions

Looking for a query that I can extract the basic asset information but also with the vulnerability solution. I want to be able to be able combine by the actual patch solution instead of every cve

Hi @eerasmuss,

Take a look at this thread, which provides a data warehouse query that returns solution level results. Is this close to what you are trying to accomplish?

If yes, it might be possible to convert and slightly modify to the Security Console schema if that’s your working environment.

If no, providing an example of the information you are looking to return would be helpful.

Here’s a good starting point for a SQL query against the console schema for Solutions. This one is just looking at all of the solutions and aggregating all of the URL, IPs, and Vulns into the next columns. You could break them out by line if you desire though.

SELECT
htmltotext(ds.fix) AS solution,
STRING_AGG(DISTINCT(ds.url), ', ' Order by ds.url) as solution_url,
STRING_AGG(DISTINCT(da.ip_address), ', ' Order by da.ip_address) AS ip_address,
STRING_AGG(DISTINCT(dv.title), ', ' Order by dv.title) AS vulnerability_description

FROM fact_asset_vulnerability_finding favf

JOIN dim_asset da ON favf.asset_id = da.asset_id
JOIN dim_vulnerability dv ON favf.vulnerability_id = dv.vulnerability_id
JOIN dim_vulnerability_solution dvs ON favf.vulnerability_id = dvs.vulnerability_id
JOIN dim_solution ds ON dvs.solution_id = ds.solution_id

--Use this to filter out upgrades
--WHERE ds.fix NOT ILIKE '%Upgrade%'

GROUP BY ds.fix
ORDER by ds.fix DESC

Those WHERE line that I have commented out can be uncommented to filter down on specific fixes or put in any other statement.