Finding Application, Configuration, and Patch Vulnerabilities

If the query isn’t working for larger asset counts it could very well be a resource issue. I would review the current memory allocated to the console and see if you need to increase the specs. Also ensure there’s no other reports or scans running at the same time as they also take up memory.

I’m not exactly sure what you mean by “grouped by impacted assets”?

Ok, I can check that for the resources but the console is pretty well resource allocated. so, this query shows what patch related vulns and workarounds we have for a specific asset group. Can a variable be added to tie the impacted assets to teach vulnerability? I guess I wish there was a canned report like the TOP 25 report that could be run for patches and workarounds separately. That is what I am being asked to provide by our CISO to present and hold the business accountable

Thank you for your help. I had opened a ticket with support but hadn’t heard back yet

ahh my bad, I thought this query already included the asset info and you were asking something different.

try this:

SELECT 
dv.vulnerability_id, 
dv.title AS vuln, 
dvc.category_name,
ds.solution_type, 
ds.summary AS solution,
da.ip_address,
da.host_name

FROM dim_vulnerability dv

JOIN dim_vulnerability_category dvc ON dv.vulnerability_id=dvc.vulnerability_id
JOIN dim_asset_vulnerability_best_solution avbs ON dv.vulnerability_id=avbs.vulnerability_id AND dvc.vulnerability_id=avbs.vulnerability_id
JOIN dim_solution ds ON avbs.solution_id=ds.solution_id
JOIN dim_asset da ON avbs.asset_id=da.asset_id

WHERE (dvc.category_name = 'Microsoft Windows')
AND (ds.solution_type = 'PATCH' OR ds.solution_type = 'ROLLUP')

GROUP BY 
dv.vulnerability_id, 
dv.title, 
dvc.category_name,
ds.solution_type, 
ds.summary,
da.ip_address,
da.host_name

Also the top 25 query would be this:

SELECT DISTINCT
ds.summary AS "Solution",
proofAsText(ds.fix) AS "Fix",
ds.estimate AS "Estimate",
dv.title AS "Vulnerability Title",
da.ip_address AS "IP Address",
da.host_name AS "Host Name",
dacs.aggregated_credential_status_description AS "Access Level",
round(dv.riskscore) AS "Risk Score",
dv.severity AS "Severity",
da.last_assessed_for_vulnerabilities AS "last discovered"
FROM fact_remediation(25, 'riskscore DESC') fr
JOIN dim_solution ds ON (fr.solution_id = ds.solution_id)
JOIN dim_asset_vulnerability_solution davs ON (fr.solution_id = davs.solution_id)
JOIN dim_vulnerability dv USING (vulnerability_id)
JOIN dim_asset da USING (asset_id)
JOIN fact_asset fa USING (asset_id)
JOIN dim_aggregated_credential_status dacs using (aggregated_credential_status_id)

Then you would just add those WHERE statements to filter on just the ‘WORKAROUND’ types

OH
WOW THANK YOU!

@john_hartman - This is fabulous, where would you add the asset group name in the SQL?

you would need to add a column in the select portion for dag.name and JOIN both the dim_asset_group table and the dim_asset_group_asset. The column should probably aggregated for multiple asset groups.

@john_hartman Forgive me but I’m new at all of this, I conceptually understand, but the details are eluding me. Mainly, I get the dag.name, but does that come from the dimension vulnerability table or the facts table? and how would you do the JOIN?

Pretty sure this is what you’re looking for

SELECT 
dv.vulnerability_id, 
dv.title AS vuln, 
dvc.category_name,
ds.solution_type, 
ds.summary AS solution,
da.ip_address,
da.host_name,
dag.name

FROM dim_vulnerability dv

JOIN dim_vulnerability_category dvc ON dv.vulnerability_id=dvc.vulnerability_id
JOIN dim_asset_vulnerability_best_solution avbs ON dv.vulnerability_id=avbs.vulnerability_id AND dvc.vulnerability_id=avbs.vulnerability_id
JOIN dim_solution ds ON avbs.solution_id=ds.solution_id
JOIN dim_asset da ON avbs.asset_id=da.asset_id
LEFT JOIN dim_asset_group_asset daga ON da.asset_id=daga.asset_id
LEFT JOIN dim_asset_group dag ON daga.asset_group_id=dag.asset_group_id

WHERE (dvc.category_name = 'Microsoft Windows')
AND (ds.solution_type = 'PATCH' OR ds.solution_type = 'ROLLUP')

GROUP BY 
dv.vulnerability_id, 
dv.title, 
dvc.category_name,
ds.solution_type, 
ds.summary,
da.ip_address,
da.host_name,
dag.name