SQL to export data used in Risk And Assets Over Time graph on Home page

InsightVM SQL Queries
1

Please could I check if it’s possible to export the data used to generate the Risk and Assets over Time graphs that appear at the top of the homepage via a SQL export query?

We just need a table that shows the data points used in the graph (date, risk score and asset count) and it’s proving difficult to work out how to do it. I can’t see anything obvious in the documentation or the github repository of SQL queries, and there are no dashboards cards i can find that help.

Thanks
Trevor

2

This table should get you exactly what you’re looking for

3

Thanks John - I did try the fact tables and wrote a couple of really simple queries to try to get some information out and build it up. They would validate, but then would fail after a few minutes of running for no obvious reason.

At least I know I was looking in the right place, so i’ll go back and have another look and update this post with either a solution or (more likely) more questions.

4

So the query you’re looking for could be as simple as a one line statement:

SELECT * FROM fact_asset_date(‘2023-01-01’, ‘2023-04-01’, INTERVAL ‘1 week’)

If you’re queries are validating but then failing it’s very possible that the dataset is too large and there’s not enough resources on your console to run the query. You could try scoping the query down to a single asset or an asset group or something to try and reduce the amount of memory it uses up to run the request. You can do this outside of the query itself but in the actual report builder.

5

Thanks John,

I have got a basic report running now, although running it by selecting asset groups in the scope comes back with just headers and no data. However, running the same query by selecting individual assets or by tag works fine though so I’m doing it that to get around it.

My (minor) problem now is that the query takes a long time to run, and even longer if i want to do any SUM and GROUP in the query, although I’m getting around this importing the simple data into Excel and doing the sum\group in that.

6

What kind of resources does your console have? Also is it acting as the scanner as well or do you have dedicated scan engines?

Typically speaking the main cause of slowness when running reports is not enough resources like CPU and RAM to handle the queries. This could either be because the console has low provisioned resources OR there are too many concurrent actions eating up the resources like trying to run a report while also in the process of running a scan (using the local console engine) , etc.