If you haven’t already done so I recommend checking out our Public Github repo for SQL Queries which should provide a lot of help for some example queries. I just put in a pull request to add the query you’re looking for. The queries for use against the console would be under the sql-query-export folder.
Once merged the query will be called “Assets-With-All-Tags.sql” but in the meantime, the query is here below:
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'
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(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)
I worked through many of the queries on Github yesterday and tried writing my own from the various things that learned working with them, but no luck. This morning I found a “backwards” way of retrieving the data in pieces.
The query that you provide here gets me a usable format in Excel that I can work with to clean up and polish my tagging effort.