InsightVM SQL Queries Home

holly_wilsey · October 18, 2022, 2:04pm

SQL queries are a handy option in InsightVM that enable you to access asset and vulnerability data and generate custom reports to fit your team’s needs. If you’re looking to write one of these queries but you’re not sure how to get started, or you want to see what queries other folks have used before, check out our new InsightVM SQL queries repo!

In this public repo, you can access a variety of practical SQL queries that range in terms of complexity and the data they’re retrieving. Take a look at the query title and description to see if it’s what you’re looking for, then copy the query to use in your own environment.

If you’re looking to learn more about the data that’s available for use in these queries so you can further customize them, check out our resources on the reporting data model. Here you can see the database schema and get a better understanding of how to construct these queries to get exactly what you’re looking for.

https://docs.rapid7.com/insightvm/understanding-the-reporting-data-model-dimensions/

https://docs.rapid7.com/insightvm/understanding-the-reporting-data-model-facts/

And if you’d like to share a SQL query you’ve written, feel free to open a pull request at the repo linked above and we’ll give it a review!

holly_wilsey · October 18, 2022, 2:04pm

wheid · October 27, 2022, 9:27am

Its a nice concept, but it needs work. Several of the queries have typo’s in them that are relatively easy to spot. A couple of them don’t validate (i.e. Asset-Inventory.sql) and the larger your organization, the more likely that the query doesn’t return everything it should (i,e. Assets-By- Specific-CVE-Results.sql).

john_hartman · October 27, 2022, 4:23pm

Good look on these, the team meets every week to go over these queries and review any pull requests but we can address these validation issues as well.

holly_wilsey · October 28, 2022, 3:13pm

Thanks for the feedback. Like John said, this repo is an ongoing project and it’s something we’re regularly updating to resolve any errors and continue to improve things, so we’ll take a look at the queries you mentioned. Since this is a public repo, we’re also open to Pull Requests from everyone and will review those so we can continue to improve the queries, as well.

When it comes to organizations with a larger asset count, we do often recommend using the Data Warehouse to offload reports that may contain huge amounts of data. That said, a query’s performance all depends on the data it’s pulling and how it’s scoped, so a query that pulls a lot of data across many assets will definitely be more “expensive” and take longer. If there are any others that you’re having issues with, feel free to share and we’ll see if we can help.

wheid · November 4, 2022, 10:23am

Be careful with Export queries “Most Critical Assets” and “Most Critical Vulnerabilities.” I let them run for about 20 hours and they never finished. After “Canceling” them, I noticed that my database was 5 times its normal size. Fortunately, a reboot fixed things.

holly_wilsey · November 8, 2022, 8:39pm

With regards to the database size, this is a defect that we’ve noted where the temporary disk usage for reports was not being cleaned up until after a reboot. Our engineering team has been tackling this with plans of pushing a fix in the near future.

As for those particular queries, there is definitely a possibility that they will take a long time to execute, depending on the assets and vulnerabilities in your environment. If they’re too long-running, then it’s best to further scope the queries down to something more manageable, based on additional asset or vulnerability criteria. In general, the queries in this repo aren’t necessarily going to work as-is for everyone, so it’s often best to use them as a starting point and further customize from there.

jake_susnik1 · November 15, 2022, 10:00pm

Looking for assistance. In the data model I am not finding a column that would map to GUI “Project Name”?

Our IT team seems to think the R& console is for security and not the techs resolving issues and they just want a CSV of the project.

Basically what I am looking for from the projects tab (Only projects NOT in a “Completed” status):
ProjectName, Solution, IPAddress, Hostname, OS, Remediated, Assignees

And I am really green at SQL so if a way to put each project on a new tab that would be really cool!
If can do project by tab, remove ProjectName as col1 and use a tab name.

john_hartman · November 16, 2022, 2:05pm

Remediation Projects as well as Goals and SLAs are both cloud level items and are therefore not in the SQL database that is local to the console.

Unfortunately the only way to export any data about remediation projects currently is through the actual remediation projects page. Our cloud API doesn’t have endpoints for remediation projects yet either but is something that has been mentioned and may be in the pipeline for a future release.

Your IT team is partially correct as far as the console is concerned. However when you create a remediation project you have the ability to send the project to users outside of the tool by specifying an email. This will send them a link to ONLY the project you specify where they will be able to monitor the progress of their actions as well as see the full scope of the project.

jake_susnik1 · November 16, 2022, 2:28pm

Thank you, Yeah they just want a csv though are site admins. 2 moire questions while here.

is there a way to create an exclusion for say JAVA instead of 400+ exclusions on a single asset if the risk is accepted?
is there a way to create an exclusion for an entire asset or group of assets that are not going to be patched.

john_hartman · November 16, 2022, 3:00pm

Yes, there are multiple scopes for exceptions. There is Global, Asset Group, All Instances on an Asset, and Specific Instance on an Asset.

The options that come up depend on the page you’re trying to create the exception from.

For example 1, that would be a global exception
For example 2, that would be the asset group scope

jake_susnik1 · November 16, 2022, 5:51pm

Can you point me to the documentation on this or quick where to look because I have looked all over and not finding it.

For example 1, that would be a global exception - I only see global exception on say “(CVE-2022-21628)” Not for say: “vulnerability.title CONTAINS ‘java’”

For example 2, that would be the asset group scope - Ok, I have in asset group now what? Other than delete the asset in the group and add the group to the site exclusions to not scan them, is there no way to scan them but exclude them from showing up in any/all projects?

john_hartman · November 16, 2022, 6:02pm

https://docs.rapid7.com/insightvm/working-with-vulnerability-exceptions/

jake_susnik1 · November 16, 2022, 8:35pm

Yeah been through that.

Nope - You can create an exception for all instances of a vulnerability on all affected assets.
Nope - You can create an exception for all instances of a vulnerability in a site.
Nope - You can create an exception for all instances of a vulnerability on a single asset.
Nope - You can create an exception for all instances of a vulnerability on an asset group.
Nope - You can create an exception for a single instance of a vulnerability.

Looking for:
You can create an exception for all vulnerabilities on an asset group.
You can create an exception for all vulnerabilities related to software (Ex: JAVA) on an asset group

Java CPU October 2022 Oracle Java SE, Oracle GraalVM Enterprise Edition vulnerability (CVE-2022-21628)
Java CPU October 2022 Oracle Java SE, Oracle GraalVM Enterprise Edition vulnerability (CVE-2022-21624)
Java CPU October 2022 Oracle Java SE, Oracle GraalVM Enterprise Edition vulnerability (CVE-2022-21626)
Java CPU October 2022 Oracle Java SE, Oracle GraalVM Enterprise Edition vulnerability (CVE-2022-21619)
Java CPU July 2022 Oracle Java SE, Oracle GraalVM Enterprise Edition vulnerability (CVE-2022-21540)
Java CPU July 2022 Oracle Java SE, Oracle GraalVM Enterprise Edition vulnerability (CVE-2022-21541)
Java CPU July 2022 Oracle Java SE, Oracle GraalVM Enterprise Edition vulnerability (CVE-2022-34169)
Java CPU April 2022 Oracle Java SE, Oracle GraalVM Enterprise Edition vulnerability (CVE-2022-21443)
Java CPU April 2022 Oracle Java SE, Oracle GraalVM Enterprise Edition vulnerability (CVE-2022-21476)
Java CPU April 2022 Oracle Java SE, Oracle GraalVM Enterprise Edition vulnerability (CVE-2022-21496)
Java CPU April 2022 Oracle Java SE, Oracle GraalVM Enterprise Edition vulnerability (CVE-2022-21434)
Java CPU April 2022 Oracle Java SE, Oracle GraalVM Enterprise Edition vulnerability (CVE-2022-21426)
Java CPU January 2022 Oracle Java SE, Oracle GraalVM Enterprise Edition vulnerability (CVE-2022-21293)
Java CPU January 2022 Oracle Java SE, Oracle GraalVM Enterprise Edition vulnerability (CVE-2022-21294)
Java CPU January 2022 Oracle Java SE, Oracle GraalVM Enterprise Edition vulnerability (CVE-2022-21296)
Java CPU January 2022 Oracle Java SE, Oracle GraalVM Enterprise Edition vulnerability (CVE-2022-21291)
Java CPU January 2022 Oracle Java SE, Oracle GraalVM Enterprise Edition vulnerability (CVE-2022-21299)
Java CPU January 2022 Oracle Java SE, Oracle GraalVM Enterprise Edition vulnerability (CVE-2022-21282)
Java CPU January 2022 Oracle Java SE, Oracle GraalVM Enterprise Edition vulnerability (CVE-2022-21248)
Java CPU January 2022 Oracle Java SE, Oracle GraalVM Enterprise Edition vulnerability (CVE-2022-21271)
Java CPU January 2022 Oracle Java SE, Oracle GraalVM Enterprise Edition vulnerability (CVE-2022-21349)
Java CPU January 2022 Oracle Java SE, Oracle GraalVM Enterprise Edition vulnerability (CVE-2022-21340)
Java CPU January 2022 Oracle Java SE, Oracle GraalVM Enterprise Edition vulnerability (CVE-2022-21341)
Java CPU January 2022 Oracle Java SE, Oracle GraalVM Enterprise Edition vulnerability (CVE-2022-21360)
Java CPU January 2022 Oracle Java SE, Oracle GraalVM Enterprise Edition vulnerability (CVE-2022-21365)
Java CPU January 2022 Oracle Java SE, Oracle GraalVM Enterprise Edition vulnerability (CVE-2022-21305)
Java CPU July 2021 Java SE, Oracle GraalVM Enterprise Edition vulnerability (CVE-2021-2369)
Java CPU July 2021 Java SE, Oracle GraalVM Enterprise Edition vulnerability (CVE-2021-2341)
Java CPU October 2021 Java SE, Oracle GraalVM Enterprise Edition vulnerability (CVE-2021-35603)
Java CPU October 2021 Java SE, Oracle GraalVM Enterprise Edition vulnerability (CVE-2021-35565)
Java CPU October 2021 Java SE, Oracle GraalVM Enterprise Edition vulnerability (CVE-2021-35564)
Java CPU October 2021 Java SE, Oracle GraalVM Enterprise Edition vulnerability (CVE-2021-35561)
Java CPU October 2021 Java SE, Oracle GraalVM Enterprise Edition vulnerability (CVE-2021-35559)
Java CPU October 2021 Java SE, Oracle GraalVM Enterprise Edition vulnerability (CVE-2021-35556)
Java CPU October 2021 Java SE, Oracle GraalVM Enterprise Edition vulnerability (CVE-2021-35550)
Java CPU October 2021 Java SE, Oracle GraalVM Enterprise Edition vulnerability (CVE-2021-35588)
Java CPU October 2021 Java SE, Oracle GraalVM Enterprise Edition vulnerability (CVE-2021-35586)
Java CPU July 2021 Java SE, Oracle GraalVM Enterprise Edition vulnerability (CVE-2021-2388)
Java CPU October 2021 Java SE, Oracle GraalVM Enterprise Edition vulnerability (CVE-2021-35567)
Java CPU October 2021 Java SE vulnerability (CVE-2021-35560)
Java CPU October 2021 Java SE, Oracle GraalVM Enterprise Edition vulnerability (CVE-2021-35578)
Java CPU October 2021 Java SE vulnerability (CVE-2021-3517)
Java CPU October 2021 Java SE vulnerability (CVE-2021-3522)
Java CPU April 2021 Java SE, Java SE Embedded, Oracle GraalVM Enterprise Edition vulnerability (CVE-2021-2163)
Java CPU April 2021 Java SE, Java SE Embedded, Oracle GraalVM Enterprise Edition vulnerability (CVE-2021-2161)
Java CPU October 2020 Java SE, Java SE Embedded vulnerability (CVE-2020-14779)
Java CPU October 2020 Java SE, Java SE Embedded vulnerability (CVE-2020-14782)

john_hartman · November 17, 2022, 8:15pm

I see what you’re saying. There’s not a very straightforward way to do it but it is “kind of” possible to do so.

You would need to go to a specific asset and look at it’s asset details page. From there the vulnerabilities on that page can let you bulk select and exclude. Once you click exclude you can change the scope to be as an asset group or global.

So it doesn’t take an argument of “CONTAINS java” or anything. You would need to manually click each box for every java vulnerability in the list.

jake_susnik1 · November 17, 2022, 9:34pm

Thank you, That was my finding as well. Just had to pick the asset of the group with the most Java vulns and it got most. My colleague actually added 398 one at a time before I was like what the hell! LOL Maybe a feature request to add this ability somehow?

Thank you for your time John, ensuring I just was not missing something!