SQL Need a way to De-dup (remove the same ip to several custom tags

Hi is there anything i can do to de-dup the ips on here using tags, as it very repetitive ips but because there are lots of custom tags with each one, how would i get rid and just keep one of each of the ips?

If i switch “custom” to owner it doesnt give me but a few that i have listed, i need all ips but only one time.
Thanks!

I’m trying to use this to report on Discovered from a new site we have implemented. So inventory report that has any tags associated with them, with first scan and last scan (although not sure what last scan is last time it was scanned maybe? ) ip and host name. This seems to work but triples my findings.

SELECT da.ip_address, da.host_name, da.mac_address,dos.name AS “OS Name”, fad.first_discovered, fad.last_discovered,
dt.tag_type, dt.tag_name
FROM fact_asset_discovery fad
JOIN dim_asset da USING (asset_id)
JOIN dim_tag_asset dta USING (asset_id)
LEFT JOIN dim_operating_system dos USING (operating_system_id)
JOIN dim_tag dt USING (tag_id)
WHERE dt.tag_type = ‘CUSTOM’
GROUP BY da.ip_address, da.host_name, da.mac_address,dos.name, fad.first_discovered, fad.last_discovered,
dt.tag_type, dt.tag_name

If you’re just looking for assets that have tags associated, but you only want it listed a single time and you don’t necessarily care about the tags, I think you could just remove the dt.tag_type and dt.tag_name from the SELECT, then remove the same from the GROUP BY. The joins you have should still pull assets that are tagged, but we’re just not selecting those tags anymore, which should avoid the duplicate issue.

1 Like

okay ill try this, in the mean time what if i like a custom tag but specified one can i single out just exact custom tag… So for example i have a custom tag called vlan 1, 2 and 3 but only want to see ips associated with vlan 2?

I think in that case, you could keep the dt.tag_type and dt.tag_name in the select/group by, but you could then add a WHERE clause to filter by the single tag you’re looking for. So like:

WHERE dt.tag_name = 'vlan 2'
1 Like

Thank you so much!