Please help with custom report -

Is there anyone that could please help me with a custom report - I’m not a SQL statement person but I think it could be put together quickly - If someone can just give me the framework I will try to fill in the blanks.

The special field I need to have in this report is the “modified date” of the vulnerability. The reason I need the modified date is that I’m bouncing this custom report against a monthly initial scan of the same assets, and I have to use the modified date field to filter on to make sure the date range comparisons are correct (initial scan vs. final scan to see what was addressed).

Here’s all of the fields I’d use, in addition with the “modified date” being very important.

Asset Owner
Asset Criticality
Custom Tag
Asset Names
Asset IP Address
Asset OS Name
Vulnerability CVSS Score
Vulnerability CVSSv3 Score
Asset Risk Score
Exploit Count
Vulnerability Severity Level
Vulnerability Published Date
Vulnerable Since
Vulnerability Age
Vulnerability Modified Date*
Vulnerability CVE IDs
Vulnerability Title
Vulnerability Proof
Vulnerability Solution
Vulnerability Description
Vulnerability Additional URLs
Service Name Service Port
Service Product
Service Protocol

Hey @ashley_harrell, here are a couple queries that could serve as a starting point for getting this info. The first one is more vulnerability oriented with the “modified date” that you’re looking for, and the second is more asset oriented.

SELECT vuln.date_modified, vuln.title, vuln.description, vuln.severity, vuln.date_published, vuln.date_added,
       vuln.cvss_score, vuln.cvss_v3_score, sol.fix, sol.url, sol.additional_data
       FROM fact_asset_vulnerability_finding AS avf
       JOIN dim_vulnerability AS vuln ON avf.vulnerability_id = vuln.vulnerability_id
       JOIN dim_vulnerability_solution AS vs ON avf.vulnerability_id = vs.vulnerability_id
       JOIN dim_solution AS sol ON vs.solution_id = sol.solution_id
SELECT asset.host_name, asset.ip_address, fa.critical_vulnerabilities, fa.riskscore, tag.tag_name
       FROM fact_asset_vulnerability_finding AS avf
       JOIN dim_asset AS asset ON asset.asset_id = avf.asset_id
       JOIN fact_asset AS fa ON asset.asset_id = avf.asset_id
       JOIN dim_tag_asset AS ta ON avf.asset_id = ta.asset_id
       JOIN dim_tag AS tag ON ta.tag_id = tag.tag_id

I broke this up into separate queries because the fields you’ve listed here are scattered across many different tables, and doing it in a single query would likely result in a ton of data that takes ages to load. I hope this helps as a starting point for you!

Thank you so much - so if I want to report on asset groups that I’m scanning , what would that look like?

@ashley_harrell - you can scope the SQL report to the specific asset groups. Here’s the link on how to do this: https://insightvm.help.rapid7.com/docs/creating-a-basic-report#section-selecting-assets-to-report-on

Hope this helps!

Hi Aniket,

Is this possible when you are using a query like in the above reply?

I’m pretty familiar with what you sent over, but that’s only available in the report that does not use the SQL statements as far as I know.

Hi Ashley! You should be able to do this in the UI with SQL reports as well.

Thank you very much!

So do you think my original question is not a query that could be solved? I’m wanting to perform my query on subsets of assets and not my entire population.

I think it’s doable in a single query, it’s just a matter of how long it takes to execute. If you’re doing it on a subset of your assets then it might be better (depending on the size of that subset). The data you’re looking to get is also pretty broad, and we tend to recommend having separate queries that tackle asset-centric vs. vulnerability-centric data, for example. Doing it that way is a little cleaner and could help you avoid lots of duplicate data in your results.

But it is possible to merge the queries I posted above into one, and join with any other tables you need to get what you’re looking for. To get the service details you listed, I believe you can use dim_asset_service.

There’s also an example from another topic here that shows you how to get the owner tags. If you look at the start of the query it has the WITH owner_tags bit.

Let us know if there are any particular fields you’re having trouble finding, but in general this is a good place to help you find what you’re looking for and figure out which tables you need to use in your query.

Thank you again - and one last question - I’m reading all of the topics and I have a short term need to get this query working - is there any way you could please join the two queries you posted so I can look at the syntax and try to reverse it in my head? I’m sure it’s simple but I’m an old dog trying to learn a new trick.

thank you!!!

Hi Ashley,

As @holly_wilsey said, this is A LOT of data and it’s very likely that the query may not finish given a large data set (lots of vulnerabilities). Depending on the size of your environment, scoping the query to specific assets/groups will be critical for getting it to complete.

WITH tags AS (
    SELECT fa.asset_id AS asset_id, dt.tag_type, csv(dt.tag_name) AS tags
    FROM fact_asset fa
             LEFT JOIN dim_tag_asset dta ON fa.asset_id = dta.asset_id
             LEFT JOIN dim_tag dt ON dta.tag_id = dt.tag_id
    GROUP BY fa.asset_id, dt.tag_name, dt.tag_type
),
     refs AS (
         SELECT dv.vulnerability_id AS vulnerability_id, dvr.source, csv(dvr.reference) AS refs
         FROM dim_vulnerability dv
                  LEFT JOIN dim_vulnerability_reference dvr on dv.vulnerability_id = dvr.vulnerability_id
         WHERE dvr.source = 'CVE'
            OR dvr.source = 'URL'
         GROUP BY dv.vulnerability_id, dvr.source
     )
SELECT da.asset_id,
       CASE WHEN tags.tag_type = 'OWNER' THEN tags.tags END       AS "Asset Owner",
       CASE WHEN tags.tag_type = 'CRITICALITY' THEN tags.tags END AS "Asset Criticality",
       CASE WHEN tags.tag_type = 'CUSTOM' THEN tags.tags END      AS "Custom Tag",
       da.ip_address                                              AS "Asset IP Address",
       csv(DISTINCT dahn.host_name)                               AS "Asset Names",
       dos.description                                            AS "Asset OS Name",
       ROUND(dv.cvss_score::numeric, 1)                           AS "Vulnerability CVSS Score",
       ROUND(dv.cvss_v3_score::numeric, 1)                        AS "Vulnerability CVSSv3 Score",
       ROUND(fa.riskscore)                                        AS "Asset Risk Score",
       COUNT(dve.exploit_id)                                      AS "Exploit Count",
       dv.severity                                                AS "Vulnerability Severity Level",
       dv.date_published                                          AS "Vulnerability Published Date",
       fava.first_discovered                                      AS "Vulnerable Since",
       fava.age                                                   AS "Vulnerability Age",
       dv.date_modified                                           AS "Vulnerability Modified Date",
       CASE WHEN refs.source = 'CVE' THEN refs.refs END           AS "Vulnerability CVE IDs",
       dv.title                                                   AS "Vulnerability Title",
       htmltotext(favi.proof)                                     AS "Vulnerability Proof",
       ds.summary                                                 AS "Vulnerability Solution",
       htmltotext(dv.description)                                 AS "Vulnerability Description",
       CASE WHEN refs.source = 'URL' THEN refs.refs END           AS "Vulnerability Additional URLs",
       dsvc.name                                                  AS "Service Name",
       das.port                                                   AS "Service Port",
       dp.name                                                    AS "Service Protocol"
FROM fact_asset fa
         JOIN dim_asset da ON fa.asset_id = da.asset_id
         JOIN tags ON tags.asset_id = da.asset_id
         JOIN dim_asset_host_name dahn ON da.asset_id = dahn.asset_id
         JOIN dim_operating_system dos ON da.operating_system_id = dos.operating_system_id
         JOIN fact_asset_vulnerability_instance favi ON da.asset_id = favi.asset_id
         JOIN dim_vulnerability dv ON favi.vulnerability_id = dv.vulnerability_id
         JOIN fact_asset_vulnerability_age fava ON dv.vulnerability_id = fava.vulnerability_id
         LEFT JOIN refs ON dv.vulnerability_id = refs.vulnerability_id
         LEFT JOIN dim_vulnerability_exploit dve ON dve.vulnerability_id = dv.vulnerability_id
         LEFT JOIN dim_service dsvc ON favi.service_id = dsvc.service_id
         LEFT JOIN dim_asset_service das ON favi.service_id = das.service_id
         LEFT JOIN dim_protocol dp ON dp.protocol_id = das.protocol_id
         LEFT JOIN dim_asset_vulnerability_best_solution davbs
                   ON da.asset_id = davbs.asset_id AND dv.vulnerability_id = davbs.vulnerability_id
         LEFT JOIN dim_solution ds ON davbs.solution_id = ds.solution_id
GROUP BY da.asset_id, tags.tag_type, tags.tags, dos.description, dv.cvss_score, dv.cvss_v3_score, fa.riskscore,
         dv.severity, dv.date_published, fava.first_discovered, fava.age, dv.date_modified, refs.refs, refs.source,
         dv.title, favi.proof, ds.summary, dv.description, dsvc.name, das.port, dp.name

I threw this query together fairly quickly, so I haven’t done a ton of testing. However, it should retrieve all the data points that you mentioned.

One last thing. I made a couple updates there to clean up the data :slight_smile:

Thank you so much - I’ll give this a try. This query will be ran on a small subset of servers, usually about 10 - 20 assets.

Tyler,

Can you take a peek at this again? When I run it against a subset, I get no results whatsoever…

thank you!!!

Sorry about that, the last query was scoped for asset groups specifically. I’ve updated the post again, give that one a go.

Tyler, this is almost perfect as to what I need (just missing the IP address of the asset).

Do you have any idea why the vulnerabilities are repeated multiple times? I’m struggling to figure out why it doesn’t report a vulnerability just one time.

:wave: I’ve updated the query once more. The table that includes that information was already joined, just needed to add that column to the SELECT statement, documented here: https://docs.rapid7.com/insightvm/understanding-the-reporting-data-model-dimensions#dim_asset

I’ll also link the documentation for the fact tables used since they have a lot of great data that this query incorporates as well: https://docs.rapid7.com/insightvm/understanding-the-reporting-data-model-facts

As for the repeated vulnerabilities, that’s because the fact_asset_vulnerability_instance table includes specific instances of a vulnerability, not the finding summary. Here’s how we explain it in our docs:

https://docs.rapid7.com/insightvm/vulnerability-metrics-explained#vulnerability-instances

A “vulnerability instance” refers to the specific condition on an asset that causes it to be vulnerable to a vulnerability. An asset can be vulnerable to the same vulnerability in multiple ways. Common causes for this scenario are:

  • Having multiple versions of the same software installed on an asset at the same time; all of which are vulnerable to the same vulnerability
  • Being vulnerable to the same vulnerability through multiple network ports

Vulnerability instances are the most granular view available for determining the level of risk in your environment.

There is also the fact_asset_vulnerability_finding table which is a summary of the finding and only lists the count of instances of that particular vulnerability; however, it doesn’t have have the port/service information since that can be different for each instance. If you see different ports/services for the vulnerabilities with multiple records in your results, that is likely the reason.

Hope that helps!