Hi
Attemping to pull back assets and specific custom tags only. Any tag that starts ‘AH’.
Have cobbled together below, which is bringing back no custom tags … I assume that WHERE is broken/looking for wrong fields.
Advice very much welcome.
WITH
custom_tags AS (
SELECT dta.asset_id, string_agg(dt.tag_name, ', ') as custom_tags
FROM dim_tag dt
JOIN dim_tag_asset dta ON dt.tag_id=dta.tag_id
WHERE dt.tag_type = 'CUSTOM' AND dt.tag_name LIKE 'AH%'
GROUP BY dta.asset_id
),
location_tags AS (
SELECT dta.asset_id, string_agg(dt.tag_name, ', ') as location_tags
FROM dim_tag dt
JOIN dim_tag_asset dta ON dt.tag_id=dta.tag_id
WHERE dt.tag_type = 'LOCATION'
GROUP BY dta.asset_id
),
owner_tags AS (
SELECT dta.asset_id, string_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
),
criticality_tags AS (
SELECT dta.asset_id, string_agg(dt.tag_name, ', ') as criticality_tags
FROM dim_tag dt
JOIN dim_tag_asset dta ON dt.tag_id=dta.tag_id
WHERE dt.tag_type = 'CRITICALITY'
GROUP BY dta.asset_id
)
SELECT da.ip_address, da.host_name, ct.custom_tags, lt.location_tags, ot.owner_tags, crit.criticality_tags
FROM dim_asset da
LEFT JOIN custom_tags ct USING (asset_id)
LEFT JOIN location_tags lt USING (asset_id)
LEFT JOIN owner_tags ot USING (asset_id)
LEFT JOIN criticality_tags crit USING (asset_id)