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!
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
would it be possible to get the number of critical, severe and moderate vulnerability count as well for each asset? what table could produce that output?
The fact_asset table has a column for each of those values
thank you John!
dim)asset_vulnerability_best_solution is this table not available on prem if replicated into postgres?
I’m not sure what you’re asking. That table does exist in the console reporting data model.
https://docs.rapid7.com/insightvm/understanding-the-reporting-data-model-dimensions/#dim_asset_vulnerability_best_solution
If you have a data warehouse, the table(s) I think you might be interested in are…
[fact_asset_vulnerability_finding_remediation].
“Accumulating snapshot fact that describes the impact of applying a rollup solution to a vulnerability on an asset. For every rollup solution that is selected for an asset a record will be present in this fact that summaries the result of applying that solution to a vulnerability. Note, this fact does not calculate the impact of solutions that are not the highest level of rollup.”
[dim_asset_vulnerability_finding_rollup_solution]
“Dimension that provides access to what “best” solutions can be used to remediate a vulnerability on an asset. The solution(s) presented for an asset and vulnerability will be matched on the metadata/fingerprints of the asset and take supercedence and rollup into account. Despite this, multiple solutions may be selected and presented if a single solution cannot be selected. See dim_asset_vulnerability_finding_solution to gain access to the solutions without rollup applied.”
Sorry for grave digging here, but I wanted to know if we have the ability to add a vulnerability proof column in this sql export ?
Hi,
I am trying to run a similar report. I can create a remediation task but not able to generate those details into a report with asset details. Would it be possible to help me to generate a report on the below custom query along with asset IP or asset name?
asset.groups IN [‘“asset group name”’] && asset.lastScanTime > /NOW - P2D/ && vulnerability.cvssScore >= 7 && (vulnerability.title CONTAINS ‘Security Update’ || vulnerability.title CONTAINS ‘Obsolete’)
Regards,
Rohit