InsightVM Reporting Data Model vs Data Warehouse Model

InsightVM SQL Queries
1

Hey Folks,
I’ve got a coworker who spends most of their time writing reports in the console… they use this help doc to write their queries:
https://docs.rapid7.com/insightvm/understanding-the-reporting-data-model-facts/

I spend most of my time writing reports in the data warehouse… I use this doc to write my queries:
https://help.rapid7.com/nexpose/en-us/warehouse/warehouse-schema.html#fact_tag.assets

We compared notes today, and realized how much easier it would be to write console queries if the help doc for the reporting data model was formatted in the same way that the data warehouse. (Take a look at the two links above and you’ll see what I mean)

Is that something R7 might be open to doing?

2 Likes
2

We can definitely pass this along to the team as feedback. What is it about the data warehouse formatting that makes it easier for you? Eg, is it the data mapping at the top that’s most helpful, or the way each table is presented, etc.

3

What would be better is, if the console tables and the DWH tables where more consistant so that you can use the same queries in both rather than having to spend a lot of time adjusting your console queries to fit the DWH only to find that many of the tables you need aren’t actually exported.

2 Likes
4

I definitely understand the pain here, I’ve had to adjust some queries between the two as well. The DWH was built a few years after the console schema, and we decided to prioritize performance and providing richer data, rather than making the two the same. It does lead to some tradeoffs like you said, though.

5

In case parity between the two datasets is difficult, just having the mappings between the fact/dimension tables for the console would be big improvement. The images of the relationships for the console are difficult to read. Also an entry for dim_scope_tag is missing from the console documentation.

2 Likes
6

Definitely the DWH schema is very helpful to understand the relations between tables. I really need in that format the Reporting Data Model to construct in better way and faster the queries