# Finding Application, Configuration, and Patch Vulnerabilities **URL:** \ **Category:** SQL Queries\ **Created:** [January 4, 2021, 6:57pm UTC](https://discuss.rapid7.com/t/finding-application-configuration-and-patch-vulnerabilities/2949 "2021-01-04T18:57:36Z")\ **Posts on this page:** 18\ **Page:** 2
**Author:** ![john\_hartman](https://discuss.rapid7.com/user_avatar/discuss.rapid7.com/john_hartman/32/1255_2.png) [@john\_hartman](https://discuss.rapid7.com/u/john_hartman)\ **Post date:** [October 13, 2022, 6:56pm UTC](https://discuss.rapid7.com/t/finding-application-configuration-and-patch-vulnerabilities/2949/21 "2022-10-13T18:56:17Z")
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”? ---
**Author:** ![jstraus](https://discuss.rapid7.com/letter_avatar_proxy/v4/letter/j/4bbf92/32.png) [@jstraus](https://discuss.rapid7.com/u/jstraus)\ **Post date:** [October 13, 2022, 7:26pm UTC](https://discuss.rapid7.com/t/finding-application-configuration-and-patch-vulnerabilities/2949/22 "2022-10-13T19:26:43Z")
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 ---
**Author:** ![jstraus](https://discuss.rapid7.com/letter_avatar_proxy/v4/letter/j/4bbf92/32.png) [@jstraus](https://discuss.rapid7.com/u/jstraus)\ **Post date:** [October 13, 2022, 7:29pm UTC](https://discuss.rapid7.com/t/finding-application-configuration-and-patch-vulnerabilities/2949/23 "2022-10-13T19:29:17Z")
Thank you for your help. I had opened a ticket with support but hadn’t heard back yet ---
**Author:** ![john\_hartman](https://discuss.rapid7.com/user_avatar/discuss.rapid7.com/john_hartman/32/1255_2.png) [@john\_hartman](https://discuss.rapid7.com/u/john_hartman)\ **Post date:** [October 13, 2022, 7:46pm UTC](https://discuss.rapid7.com/t/finding-application-configuration-and-patch-vulnerabilities/2949/24 "2022-10-13T19:46:33Z")
ahh my bad, I thought this query already included the asset info and you were asking something different. try this: ```auto 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 ``` ---
**Author:** ![john\_hartman](https://discuss.rapid7.com/user_avatar/discuss.rapid7.com/john_hartman/32/1255_2.png) [@john\_hartman](https://discuss.rapid7.com/u/john_hartman)\ **Post date:** [October 13, 2022, 7:49pm UTC](https://discuss.rapid7.com/t/finding-application-configuration-and-patch-vulnerabilities/2949/25 "2022-10-13T19:49:13Z")
Also the top 25 query would be this: ```auto 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 ---
**Author:** ![jstraus](https://discuss.rapid7.com/letter_avatar_proxy/v4/letter/j/4bbf92/32.png) [@jstraus](https://discuss.rapid7.com/u/jstraus)\ **Post date:** [October 14, 2022, 5:33pm UTC](https://discuss.rapid7.com/t/finding-application-configuration-and-patch-vulnerabilities/2949/26 "2022-10-14T17:33:34Z")
OH WOW THANK YOU! ---
**Author:** ![ppr85](https://discuss.rapid7.com/letter_avatar_proxy/v4/letter/p/f1d935/32.png) [@ppr85](https://discuss.rapid7.com/u/ppr85)\ **Post date:** [November 2, 2022, 1:13pm UTC](https://discuss.rapid7.com/t/finding-application-configuration-and-patch-vulnerabilities/2949/27 "2022-11-02T13:13:22Z")
> [@john\_hartman](#): > > ```auto > 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 > > ``` @john_hartman - This is fabulous, where would you add the asset group name in the SQL? ---
**Author:** ![john\_hartman](https://discuss.rapid7.com/user_avatar/discuss.rapid7.com/john_hartman/32/1255_2.png) [@john\_hartman](https://discuss.rapid7.com/u/john_hartman)\ **Post date:** [November 2, 2022, 1:49pm UTC](https://discuss.rapid7.com/t/finding-application-configuration-and-patch-vulnerabilities/2949/28 "2022-11-02T13:49:12Z")
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. ---
**Author:** ![ppr85](https://discuss.rapid7.com/letter_avatar_proxy/v4/letter/p/f1d935/32.png) [@ppr85](https://discuss.rapid7.com/u/ppr85)\ **Post date:** [November 2, 2022, 5:56pm UTC](https://discuss.rapid7.com/t/finding-application-configuration-and-patch-vulnerabilities/2949/29 "2022-11-02T17:56:18Z")
@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? ---
**Author:** ![john\_hartman](https://discuss.rapid7.com/user_avatar/discuss.rapid7.com/john_hartman/32/1255_2.png) [@john\_hartman](https://discuss.rapid7.com/u/john_hartman)\ **Post date:** [November 2, 2022, 11:40pm UTC](https://discuss.rapid7.com/t/finding-application-configuration-and-patch-vulnerabilities/2949/30 "2022-11-02T23:40:10Z")
Pretty sure this is what you’re looking for ```auto 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 ``` ---
**Author:** ![ss153152](https://discuss.rapid7.com/letter_avatar_proxy/v4/letter/s/97f17d/32.png) [@ss153152](https://discuss.rapid7.com/u/ss153152)\ **Post date:** [February 10, 2023, 6:09pm UTC](https://discuss.rapid7.com/t/finding-application-configuration-and-patch-vulnerabilities/2949/31 "2023-02-10T18:09:55Z")
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? ---
**Author:** ![john\_hartman](https://discuss.rapid7.com/user_avatar/discuss.rapid7.com/john_hartman/32/1255_2.png) [@john\_hartman](https://discuss.rapid7.com/u/john_hartman)\ **Post date:** [February 10, 2023, 6:30pm UTC](https://discuss.rapid7.com/t/finding-application-configuration-and-patch-vulnerabilities/2949/32 "2023-02-10T18:30:06Z")
The fact\_asset table has a column for each of those values > **[Understanding the reporting data model: Facts | InsightVM Documentation](https://docs.rapid7.com/insightvm/understanding-the-reporting-data-model-facts/)** > > Fact tables provide access to information allowed by the configuration of the report. ---
**Author:** ![ss153152](https://discuss.rapid7.com/letter_avatar_proxy/v4/letter/s/97f17d/32.png) [@ss153152](https://discuss.rapid7.com/u/ss153152)\ **Post date:** [February 10, 2023, 9:46pm UTC](https://discuss.rapid7.com/t/finding-application-configuration-and-patch-vulnerabilities/2949/33 "2023-02-10T21:46:23Z")
thank you John! ---
**Author:** ![ss153152](https://discuss.rapid7.com/letter_avatar_proxy/v4/letter/s/97f17d/32.png) [@ss153152](https://discuss.rapid7.com/u/ss153152)\ **Post date:** [February 16, 2023, 11:40pm UTC](https://discuss.rapid7.com/t/finding-application-configuration-and-patch-vulnerabilities/2949/34 "2023-02-16T23:40:31Z")
dim)asset\_vulnerability\_best\_solution is this table not available on prem if replicated into postgres? ---
**Author:** ![john\_hartman](https://discuss.rapid7.com/user_avatar/discuss.rapid7.com/john_hartman/32/1255_2.png) [@john\_hartman](https://discuss.rapid7.com/u/john_hartman)\ **Post date:** [February 17, 2023, 12:49pm UTC](https://discuss.rapid7.com/t/finding-application-configuration-and-patch-vulnerabilities/2949/35 "2023-02-17T12:49:50Z")
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](https://docs.rapid7.com/insightvm/understanding-the-reporting-data-model-dimensions/#dim_asset_vulnerability_best_solution) ---
**Author:** ![bobsledtedd](https://discuss.rapid7.com/letter_avatar_proxy/v4/letter/b/dc4da7/32.png) [@bobsledtedd](https://discuss.rapid7.com/u/bobsledtedd)\ **Post date:** [February 20, 2023, 2:11pm UTC](https://discuss.rapid7.com/t/finding-application-configuration-and-patch-vulnerabilities/2949/36 "2023-02-20T14:11:45Z")
If you have a [data warehouse](https://help.rapid7.com/nexpose/en-us/warehouse/warehouse-schema.html), 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.”_ ---
**Author:** ![brzrkstrk](https://discuss.rapid7.com/letter_avatar_proxy/v4/letter/b/9e8a1a/32.png) [@brzrkstrk](https://discuss.rapid7.com/u/brzrkstrk)\ **Post date:** [March 24, 2023, 7:55pm UTC](https://discuss.rapid7.com/t/finding-application-configuration-and-patch-vulnerabilities/2949/37 "2023-03-24T19:55:00Z")
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 ? ---
**Author:** ![rsiva1](https://discuss.rapid7.com/letter_avatar_proxy/v4/letter/r/22d042/32.png) [@rsiva1](https://discuss.rapid7.com/u/rsiva1)\ **Post date:** [July 12, 2023, 10:43am UTC](https://discuss.rapid7.com/t/finding-application-configuration-and-patch-vulnerabilities/2949/38 "2023-07-12T10:43:57Z")
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 [Previous page](https://discuss.rapid7.com/t/finding-application-configuration-and-patch-vulnerabilities/2949.md?page=1)