Finding Application, Configuration, and Patch Vulnerabilities

Hi @holly_wilsey

I had a quick question on the vulnerability category. Does Rapid7 tag all of the web application vulnerabilities with ‘Web’ and all of the Microsoft Patch vulnerabilities with ‘Microsoft Patch’? I am noticing a trend with this and hoping to use a filter using this.

There is the dim_vulnerability_category table that has a lot of different vuln categories (200+ I think), things like Oracle, Adobe, Apple, etc. The “Web” and “Microsoft Patch” categories do exist in there, and the vuln ID’s are there to tie them directly to the vulnerabilities, so you could filter by those.

@holly_wilsey

Thank you for your suggestion. Is there any way to see if the vulnerability directly ties to the operating system on the system?

Hmm, what exactly do you mean by “directly ties to”? Because you can see the vulnerabilities of an asset, and you can see that asset’s OS, so there’s a tie in that way. But it sounds like you’re trying to accomplish something else here.

Yes, what we are trying to look for is if the vulnerability is just patch OS. Just been difficult trying to find a connection to that.

It’s going to be tough to write a query that perfectly captures vulns with a patch OS solution, since I don’t think any of the fields we have to work with will 100% convey that. But you can narrow things down with the “PATCH” and “ROLLUP” solution types, and then filter based on vuln categories that are OS-specific. Here’s an example query with that filtering.

SELECT dv.vulnerability_id, dv.title AS vuln, dvc.category_name,
       ds.solution_type, ds.summary AS solution
FROM dim_vulnerability dv
JOIN dim_vulnerability_category dvc USING (vulnerability_id)
JOIN asset_vulnerability_best_solution avbs USING (vulnerability_id)
JOIN dim_solution ds USING (solution_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

Sorry to comment on OPs topic but this sounds like what I’m researching. I’ve been asked to gather/build a report of assets that lists patches vs configuration issues. I need total patches missing (high/med/low).
I’ve tried combinations of the pre-built reports but not seeing exact what I’m looking for (I could easily be missing it).

1 Like

Did you find a way to find assets with missing patches/Kb and config issues??

I have no yet. I found an SQL Example posted on Rapid7 but it keeps failing to finish a scan/report even though it validates correctly. @holly_wilsey

I’m not positive why it would fail to finish the report if the query is validating. My first thought is it’s pulling back too many vulnerabilities, and it hangs because of data overload. You could try further narrowing the query by interval, for instance.

tried using this query. getting " Error: The query is invalid" on validate. I’ve been tinkering with it for about 6 hrs and for the life of me i cant figure out why its invalid.

Hi christiansm,

I have been toying around with this as well.
I believe this is due to a typo. Because the asset_vulnerability_best_solution table does not exist, however the dim_asset_vulnerability_best_solution does exist.
Could you try the following query?:

SELECT dv.vulnerability_id, dv.title AS vuln, dvc.category_name,
       ds.solution_type, ds.summary AS solution
FROM dim_vulnerability dv
JOIN dim_vulnerability_category dvc USING (vulnerability_id)
JOIN dim_asset_vulnerability_best_solution avbs USING (vulnerability_id)
JOIN dim_solution ds USING (solution_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

Good morning. I have been trying to work on this the last 2 days and see that although the sql query validates the reports never completes. In fact it aborts. Any other suggestions or is there a report that could be made to accomplish this where patches and configuration related items can be split out?

I just ran the query you posted and it previewed just fine for me (see screenshot)
Screen Shot 2022-10-13 at 12.50.49 PM

It could be an issue with the scope if you’re running this against your global list of assets and you happen to have a very large count of assets. Try scoping the query down to a specific site or asset group and re run the query.

Thanks for replying. I did try against a specific asset group. Perhaps it’s too large. I’ll try a smaller scope group. Is there a better way to break out and report patches vs. configuration related issues for say windows servers.

narrowing the scope did in fact work. How would I run this for items that aren’t a patch? Configurations related vulnerabilities, ie: smbv1 enabled. Not having much success

Instead of the above, try something like this

WHERE ds.solution_type LIKE 'WORKAROUND'

that did work.Thanks for your help. Is there a way to get this to work on a larger scale? Trying to sort this out for all our Windows Servers. The subset I was successful with was only 200 or so assets. Also can they be grouped by the impacted assets? Not sure what that variable would be

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