SQL Report for Assets and Associated Attributes

InsightVM SQL Queries
1

Hi All,

I’m fairly new to the topic.

I’m wanting to get a report of each asset in a particular group and display their Address, Hostname, Operating System, Last Scan Time, Associated Sites, Associated Groups and Assigned User-Added Custom Tags.

Any help would be appreciated.

Thanks in advance.

2

I believe this should get you pretty close to what you’re looking for. It uses string_agg to list things like sites, asset groups, and tags all in one row so you don’t have tons of duplicates in the report.

WITH
   custom_tags AS (
      SELECT asset_id, array_to_string(array_agg(distinct tag_name),',') AS custom_tags
      FROM dim_tag
         JOIN dim_tag_asset USING (tag_id)
      WHERE tag_type = 'CUSTOM'
      GROUP BY asset_id
   )
SELECT da.asset_id, da.ip_address, da.host_name, dos.name AS operating_system,
       da.last_assessed_for_vulnerabilities, string_agg(ds.name, ',') AS sites,
       string_agg(dag.name, ',') AS asset_groups, ct.custom_tags
FROM dim_asset da
JOIN dim_asset_group_asset daga ON da.asset_id = daga.asset_id
JOIN dim_asset_group dag ON daga.asset_group_id = dag.asset_group_id
JOIN dim_site_asset dsa ON dsa.asset_id = da.asset_id
JOIN dim_site ds ON dsa.site_id = ds.site_id
JOIN dim_operating_system dos on dos.operating_system_id = da.operating_system_id
LEFT JOIN custom_tags AS ct ON (ct.asset_id = da.asset_id)
GROUP BY da.asset_id, da.ip_address, da.host_name, dos.name,
         da.last_assessed_for_vulnerabilities, ct.custom_tags;

If you’re newer to SQL, you can also check out our reporting data model here and here to get a better understanding of the tables and fields available for use in queries.

And we have a bunch of SQL examples here (scroll down on the left-hand menu to see all of them):

https://docs.rapid7.com/insightvm/sql-query-export-examples/

3

Thank you very much Holly, I’ll give this a go and let you know :raised_hands:

4

Helo！
You’ll excuse me from the side.

I try to thisSQL .
Only the latest 10 lines were output.
I want to Registered asset information.

Which value should I change?
As far as I could see, there didn’t seem to be the latest designation.

Thank you your kindness.

5

I think what you’re referring to is the “Preview” button when inputting the SQL Query. By default the preview only shows the top 10 lines regardless of the query. To get the full results you need to click “Done” and then “Save & Run the Report”

6

To John
Thank you for your reply.

Unfortunately, there are 10 lines in the report output and the obtained results downloaded.

Describe what I want to do.
I want the custom tag to be displayed when displaying the asset.

On InsghtVM, click on the host and you will see USER-ADDED TAGS.

Report I want
I want to list the assets with it.

In the SQL statement above, I saw USER-ADDED TAGS in the output, but it ended up with 10 lines.

Thank you for your confirmation.

7

Hi all,
I have run this query and in both the preview and full run I always get zero results. Do you have any idea why?
Thanks

8

The only reason I could see the results coming back as empty instead of an invalid query would be is if you do not have any custom tags applied to any assets within the scope of which you are running the report. Another possible scenario would be if you copied/pasted the above query and the quotes " or ’ were translated to a different ASCII character that InsightVM didn’t recognize, I’ve had that before,