SQL query to export assets with their tags and asset group assignments

InsightVM SQL Queries
1

Any chance someone knows how to export a list of assets including their tags and asset group assignments?

Thank you

2

Is there any particular asset info you’re looking for besides tags + asset groups? Here’s a basic query that grabs some of that info:

SELECT da.last_assessed_for_vulnerabilities, da.ip_address, da.host_name,
       dag.name AS "asset group", dt.tag_name
FROM dim_asset da
JOIN dim_asset_group_asset daga USING(asset_id)
JOIN dim_asset_group dag USING(asset_group_id)
JOIN dim_tag_asset dta USING (asset_id)
JOIN dim_tag dt USING(tag_id)
ORDER BY last_assessed_for_vulnerabilities

There’s potentially going to be a lot of assets in here depending on your environment, so it may be good to add additional filtering for which assets you want pulled. You could filter by things like asset group name/tag name, IP, hostname, etc. That much data all in one report may be hard to decipher otherwise.

1 Like
3

Thanks Holly! We had a problem with one of our discovery connectors which triggered an automatic rediscovery of a number of assets. The assets lost their tags and asset group assignment since they looked like new assets to Nexpose.

I’m hoping to run a scheduled report to periodically export this information for all our assets.

Hope that makes sense.

1 Like
4

Gotcha, that makes sense. The query above includes IP and hostname, so those should be sufficient identifiers for those assets if you’re ever looking to reassign asset groups/tags. But it can always be updated to include other info as you want it, things like OS, site, etc.

5

Holly,
Thank you for your help with this. I ran some tests with the query but it only exports one Tag and one Asset Group for each asset. Many of our assets are in multiple groups with multiple tags. Any other ways we can capture multiple values?

Thanks again!

6

Hmm, the query above should be pulling all the asset groups and tags for each asset. How it works is if an asset has multiple groups or tags, the query results are going to list that asset multiple times, each time with a different group/tag. In other words, there’ll be multiple rows for one asset. So something like:

ip_address host_name asset group tag_name
0.0.0.1 my-host My First Asset Group First-Tag
0.0.0.1 my-host My Second Asset Group Second-Tag
7

I’m sorry I missed that. Thank you for your help with this!

1 Like
8

Is there a way to change the query so the asset groups and tags associated with a given asset report as comma separated values in their respective columns instead of multiple times?

9

@chris_ward, you should be able to use the string_agg function for this. Something like the follow should get you what you’re looking for.

SELECT da.asset_id, da.ip_address, string_agg(dag.name, ',') AS asset_groups
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
GROUP BY da.asset_id, da.ip_address;
10

Tyler,
Thank you for your help with this! Any chance you can help us pull the asset tag information along with the asset groups?

Thank you,
Peter

11

@peter_lincoln, there are similar tables for tags as well. You should be able to do a similar thing for tags by joining the dim_tag and dim_tag_asset tables.

12

Hello,

I have the need to export the users and groups that an asset contains as a result of a scan, is there a way to obtain this information in a report?

13

Hey @daniel_cruz1 - Try this one

SELECT ds. NAME AS site, da.ip_address, da.host_name, daua."name" AS user_account_name, daua.full_name AS user_account_full_name, dos.name AS os, dos.version AS os_ver 
FROM dim_asset AS da
JOIN dim_asset_operating_system AS daos ON da.asset_id = daos.asset_id
JOIN dim_operating_system AS dos ON daos.operating_system_id = dos.operating_system_id
JOIN dim_site_asset AS dsa ON da.asset_id = dsa.asset_id
JOIN dim_site AS ds ON dsa.site_id = ds.site_id
JOIN dim_asset_user_account AS daua ON da.asset_id = daua.asset_id
GROUP BY ds. NAME, da.ip_address, da.host_name, daua."name", daua.full_name, dos.name, dos.version

I haven’t used this query in a while. I really hope it works lol :stuck_out_tongue:

14

If you want all the tags showing up in one column against IPs use this query:

SELECT da.asset_id, da.ip_address, string_agg(dag.name, ', ') AS asset_groups, string_agg(dt.tag_name, ', ') AS tag
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_tag_asset dta ON da.asset_id = dta.asset_id
JOIN dim_tag dt ON dta.tag_id = dt.tag_id
GROUP BY da.asset_id, da.ip_address;