Needing Owner tag added to this Discovery SQL

InsightVM SQL Queries
#1

SELECT da.ip_address, da.host_name, da.mac_address, fad.first_discovered, fad.last_discovered
FROM fact_asset_discovery fad
JOIN dim_asset da USING (asset_id)

Attempting to added just plain and simple owner tag to this so owner tags pop up and custom tags so i see which ones have been discovered with tags already on them…All other inqueries are so complicated that have the owner tag incorporated…

Any way we can add custom tag and owner to this simple query?

#2

There’s a table for dim_tag and dim_tag_asset, so those should have what you’re looking for. I added those on to your query:

SELECT da.ip_address, da.host_name, da.mac_address, 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)
JOIN dim_tag dt USING (tag_id)
WHERE dt.tag_type = 'OWNER'
OR dt.tag_type = 'CUSTOM'
GROUP BY da.ip_address, da.host_name, da.mac_address, fad.first_discovered, fad.last_discovered,
         dt.tag_type, dt.tag_name