We are using the below query to see what duplicates we have in our system. We are noticing that it is comparing the Rapid7 agent ID when we would like it to compare the Windows UUID. Both of those fields appear to be under the unique_ID label. Is there something we can add to the query so that it is only looking at the Windows UUID?
with duplicate_uids as (
select
unique_Id,
count()
from
dim_asset_unique_Id
group by
unique_Id
having
count() > 1
),
duplicate_uids_with_asset_Id as (
select
distinct asset_id,
max(unique_id) as unique_id
from
dim_asset_unique_id
where
unique_Id in (
select
distinct unique_id
from
duplicate_uids
)
group by
asset_Id
),
duplicate_mac_and_uuid as (
select
distinct mac_address,
unique_id,
count()
from
dim_asset
join duplicate_uids_with_asset_Id using (asset_id)
group by
mac_address,
unique_Id
having
count() > 1
)
select
distinct dmau.unique_id,
da.*
from
dim_asset da
join duplicate_mac_and_uuid dmau using (mac_address)
order by
host_name,
asset_id,
ip_address,
mac_address,
unique_Id