Assets with Owner Tags

InsightVM SQL Queries
,
1

I need a SQL for report that brings back of assets, and owner tags so I can create incidents to particular owner.

2

This query should pull what you are looking for.

SELECT 
	da.ip_address
	,da.host_name
	,dt.tag_name
FROM dim_tag_asset dta
JOIN dim_asset da ON da.asset_id = dta.asset_id
JOIN dim_tag dt ON dt.tag_id = dta.tag_id
WHERE dt.tag_type = 'OWNER'
3 Likes
3

Thank You so much

1 Like
4

Here is a different version that also includes all assets without owner tag and also shows if there is more than one owner tag by separating them with a pipe:

WITH
owner_tags AS (
SELECT asset_id, array_to_string(array_agg(distinct dt.tag_name), ‘|’) AS owner_tags
FROM dim_tag dt
JOIN dim_tag_asset dta ON dt.tag_id = dta.tag_id
WHERE dt.tag_type = ‘OWNER’
GROUP BY dta.asset_id
)
SELECT
da.ip_address,
da.host_name,
MIN(da.mac_address) AS mac_address,
MIN(dos.description) AS operating_system,
MIN(dht.description) AS description,
MIN(dos.asset_type) AS asset_type,
MIN(ot.owner_tags) AS owner_tags
FROM dim_asset da
LEFT JOIN owner_tags ot ON da.asset_id = ot.asset_id
LEFT JOIN dim_operating_system dos ON da.operating_system_id = dos.operating_system_id
LEFT JOIN dim_host_type dht ON da.host_type_id = dht.host_type_id
LEFT JOIN dim_site_asset dsa ON da.asset_id = dsa.asset_id
LEFT JOIN dim_site ds ON dsa.site_id = ds.site_id
LEFT JOIN fact_asset_scan_service fass ON da.asset_id = fass.asset_id
LEFT JOIN dim_credential_status dcs ON fass.credential_status_id = dcs.credential_status_id
LEFT JOIN fact_asset fa ON da.asset_id = fa.asset_id
LEFT JOIN dim_aggregated_credential_status dacs ON fa.aggregated_credential_status_id = dacs.aggregated_credential_status_id
LEFT JOIN dim_asset_service das ON da.asset_id = das.asset_id
GROUP BY da.ip_address, da.host_name
ORDER BY da.ip_address;