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?
1 Like
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?
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.
1 Like
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
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