The online documentation shows some examples of the tables and how we can then use joins around those. Is there a complete list of all the tables in the database somewhere that we could just have for then determining what we’d want to join more easily?
Hey Aaron!
Here’s a list of the tables, as well as extra large graphics to visualize the relationships in the Data Warehouse for InsightVM:
https://help.rapid7.com/nexpose/en-us/warehouse/warehouse-schema.html
Also some high-level information around the database:
https://docs.rapid7.com/insightvm/understanding-the-reporting-data-model-overview-and-query-design/
Finally, if you don’t have the data warehouse set up currently, here’s steps for getting that done:
https://docs.rapid7.com/insightvm/configuring-data-warehousing-settings/
1 Like
That warehouse schema visual looks like it a good flow of all the things we’d can map from. Going to look at that export to our external data warehouse as well, that may be another option to hook our assset mgmt system into that rather then pulling/pushing between them.
Thanks!
1 Like
Please note the warehouse schema is NOT the console postgres DB Schema. No one within support has been able to provide the Nexpose postgres schema or provide details on what the differences are. This is extremely confusing when you are trying to write a SQL query, again with no help from support, and you only have the warehouse schema as a reference, thus your query will be wrong.
2 Likes
Dead topic but this is still the case. I’m currently wrestling with some sequel and I’d give my left arm to have the console’s schema. I don’t have any use for the warehouse schema but given that running sql queries in the console is clearly supported I don’t understand why the schema isn’t properly documented anywhere.
1 Like
Just thought I’d add to this
I was in contact with the support in a case related to the SQL export report feature and they stated that this feature does not have access to the same set of data as the rest of the console, although this is not documented anywhere.
Not entirely sure if that’s what you’re referring to, I don’t know of any other part of insightvm where you can run SQL queries
Data export. They actually have docs on sql queries specifically for export reports (SQL example - software counts and listing | Nexpose Documentation (rapid7.com)) which is nice and all, but I know sql. I don’t need to get an example that’s kinda sorta what I’m looking for and drop it in, I’d rather just have the full (accessible) schema so I can write what I need. Currently it’s a lot of trial and error to see how the tables are interacting.