Duplicate assets in sites

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.

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

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.

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

1 Like

Thank you so much. Its working.