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

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

Thank you

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.

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

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.