Duplicate assets in sites

InsightVM SQL Queries
1

Hello everyone,
Same assets are added in different sites it causing multiple scans on same asset. Which query can helps me to find these duplicate assets.

2

Are you saying that the assets are coming up as Global when you look at the asset page?

3

Let me explain. We have different sites for example 100+. In these sites some assets are added multiple times in different sites. I like to know which assets are added in different sites. For example Asset: test added into different sites like site 1,2,10,95, etc…
I like to know how many assets are like this added in different sites.

4

In theory this should do the trick

SELECT 

 da.host_name
,STRING_AGG(DISTINCT ds.name, ', ') AS site_list
,COUNT(DISTINCT dsa.site_id) AS site_count

FROM
	dim_asset da
JOIN
	dim_site_asset dsa ON da.asset_id = dsa.asset_id
JOIN 
	dim_site ds ON dsa.site_id = ds.site_id
WHERE
	ds.name NOT IN ('Rapid7 Insight Agents')
GROUP BY
	da.host_name
HAVING 
	COUNT(DISTINCT dsa.site_id) > 1
ORDER BY
	COUNT(DISTINCT dsa.site_id) DESC

Bob

2 Likes
5

Thank you so much. Its working.