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
Thank you so much. Its working.