We are taking a datawarehouse into PowerBI. There are some customs dashboards I want to create which I assume will take some work. For already created dashboards(cards on insightvm) is there a way to view what it’s pulling so I don’t need to re-invent the wheel in PowerBI.Or is there a SQL query to build off of?
2 Likes
I have same need and question.
MSFT PowerBI can fill the major gaps that we currently experience with Rapid7 InsightVM dashboards and reporting.
@dereko have you made any progress on this?
1 Like
Not really. setup the PG datawarehouse to go into MS SQL. It’s all very sloppy and so far Rapid7 has been less than helpful for support.
2 Likes
No $hit 
I am not surprised. Very unfortunate…
Hello everyone,
I have created my own dashboard on PowerBI since introducing Rapid7 InsightVM to our company. I am running a dedicated PostgreSQL database (recommended) and also have a dedicated PowerBI Report Server (I am currently not using PowerBI Cloud). The dashboard is designed for asset and application owners, allowing them to check their systems after each scan (scheduled weekly scans). The dashboard is automatically updated on a daily basis (as I also conduct ad-hoc scans from time to time).
Initially, I tried to adapt the cards from Rapid7 InsightVM but was not very successful, partly due to my lack of SQL knowledge. However, my dashboard has evolved over the last 4-5 years, thanks to a lot of feedback from my colleagues on the front lines.
Here, you can see an example from the main page.
If you want I could give you some posgresql queries I use or with which tables I work.
Good luck
1 Like
Hi David,
Could you please share your queries and tables that you work for ? Your dashboard looks fanstatic !
Thank you.
Hi,
I only have only three .sql queries as saved searches in pgadmin. In PowerBI I connect to these saved searches. I am also using an export (.csv) from our CMDB with all our assets, and combine this list (in PowerBI) with the d_asset table. This way I get all the asset owner information.
d_vulnerability.sql
SELECT
dv.vulnerability_id,
dv.nexpose_id,
dv.title,
htmlToText(dv.description) AS description, -- Ensure htmlToText is a valid function
dv.severity,
dv.cvss_score,
dv.cvss_v3_score,
dv.risk_score,
COALESCE(dvc.category_name, 'Not Listed') AS category_name,
dv.date_published,
dv.date_added,
COALESCE(ds.solution_type, 'NO SOLUTION') AS solution_type -- Include solution_type
FROM dim_vulnerability dv
LEFT JOIN dim_vulnerability_category dvc
ON dv.vulnerability_id = dvc.vulnerability_id
AND dvc.category_name = 'CISA KEV'
LEFT JOIN (
SELECT DISTINCT ON (dvs.vulnerability_id)
dvs.vulnerability_id,
REPLACE(ds.solution_type, 'ROLLUP', 'PATCH') AS solution_type -- Replace 'ROLLUP' with 'PATCH'
FROM dim_vulnerability_solution dvs
JOIN dim_solution ds
ON dvs.solution_id = ds.solution_id
ORDER BY dvs.vulnerability_id, ds.solution_type -- Order by vulnerability_id and solution_type
) ds
ON dv.vulnerability_id = ds.vulnerability_id
WHERE dv.title NOT LIKE 'Scanning Diagnostics:%'
f_asset_vulnerability_instance.sql
Select
Host(da.ip_address) AS ip,
da.host_name AS ci_name,
fasvi.asset_id,
da.os_description,
dv.title,
dv.vulnerability_id,
dv.nexpose_id,
htmltotext(dv.description) AS description,
htmltotext(fasvi.proof) AS proof,
dv.severity,
dv.risk_score,
COALESCE(dvc.category_name, 'Not Listed') AS category_name, -- Replace null with 'Not Listed'
fasvi.service,
fasvi.port,
fasvi.protocol,
dv.date_published,
dv.date_added,
da.last_assessed_for_vulnerabilities AS last_seen
FROM
fact_asset_vulnerability_instance fasvi
JOIN
dim_vulnerability dv USING(vulnerability_id)
JOIN
dim_asset da USING(asset_id)
LEFT JOIN (
Select dim_vulnerability_category.vulnerability_id, dim_vulnerability_category.category_name
FROM dim_vulnerability_category
WHERE dim_vulnerability_category.category_name = 'CISA KEV') as dvc
USING(vulnerability_id)
WHERE
dv.title NOT LIKE 'Scanning Diagnostics:%' -- Dont need diagnostics data on dashboard
d_asset.sql
Select
asset_id,
ip_address,
host_name,
os_description,
credential_status,
last_assessed_for_vulnerabilities,
risk_modifier,
datag.name
FROM
dim_asset da
LEFT JOIN (
SELECT
dat.asset_id,
dt.name
FROM dim_asset_tag dat
LEFT JOIN
dim_tag dt using(tag_id)
WHERE
dt.type = 'LOCATION'
AND dt.source not in ('CUSTOM')) as datag
USING(asset_id)
Hope this helps! But I have to warn you. I have built and optimized our dashboard the last 4 years.
In PowerBI I have done a lot work on each connected data source within the power query editor section. If you need more information, just ask.
Best
David
1 Like
@David I stumbled this post and I found it very helpful.
Do you use a Data Warehouse for PowerBI to use those saved searches/queries?
I am currently building a Power Automate Flow to pull vulnerabilities from InsightVM via API and then create Azure DevOps work items for each vulnerability.
We previously tested R7 ticketing integration with ServiceNow and it did not provide enough value.
I would love to pick your brain on getting data out of InsightVM via API and leveraging Power Automate or like tool to automate creation of Azure DevOps work items to track remediation work across teams.
@Lee Sorry for my late response.
I am running a dedicated postgresql server as my data warehouse and export the data on a daily basis to this warehouse because I scan our Windows 10 / 11 Clients on a daily basis and our Server environment on a weekly basis. This means, one day after the scan I will have the results on the dashboard.
Best regards
David