Export multiple IP addresses of the same asset

InsightVM SQL Queries
, ,
1

I have a series of assets that have multiple IPs discovered by Nexpose but when I export a list it only exports the last IP that nexpose saw. Is there a SQL Query that allows you to export a list of all the assets with all the IPs that nexpose saw over time?

2

This will list all IPs tied to an asset.

SELECT da.host_name, string_agg(dai.ip_address, ', '), dai.asset_id
FROM dim_asset_ip_address dai
JOIN dim_asset da ON da.asset_id=dai.asset_id
GROUP BY da.host_name, dai.asset_id
HAVING (COUNT(dai.ip_address) > 1)

#Assets with Multiple IPs - SQL Query
2 Likes