Monthly report showing vulnerability severity types and the count of vulnerability by status

InsightVM SQL Queries
#1

Hi,

This is hoping you can help me. I am tasked with generating a monthly report showing vulnerability severity types and the count of vulnerability by status (open, closed, closed within SLA, Still open out of SLA, etc).
I am fairly new to Rapid 7 and I am in need of either ideas on the right tables to query for my task or a sample query that can accomplish said task.
We are using the dimensional data model.

#2

Hi @rekirk_rekirk when you say status, are you referring to the status in a remediation project?
As for vulnerabilities and count by severity you should be able to use the following query to get that information:
Select count(Distinct vulnerability_id), severity from dim_vulnerability group by severity

#3

Hi Jared,

Thank you so much for taking my question.
When I say status, I am not referring to remediation project status.

Instead, my goal is an executive monthly (very very high level birds eye view) report showing the following for a target team (say an applications team):
a) what is the overall count of vulnerabilities on their assets at this moment?
b) what is the total count of closed vulnerabilities on their assets at this moment?
c) How many of those vulnerabilities were closed within SLA (e.g 2 weeks for critical findings)
d) How many of those vulnerabilities remain open and are past SLA (e.g 2 weeks SLA for critical vulnerabilities)

Our Rapid 7 setup is using the dimensional data model.

#4

This would be a fairly complex query, here’s a link to an example to find remediated vulnerabilities from one scan to the next https://docs.rapid7.com/insightvm/sql-example-new-and-remediated-vulnerabilities. You may be able to modify it using the dim_scan table to get the month time range you’re looking for.

#5

Thanks Jared. I’ll take a look at that example.