Query to list all assets and their Owners, within a specific dynamic asset group

Hi all,

Have been trying to figure out how to go about creating a report listing assets within a dynamic group together with their Owner tag.Yet, (since I’m not that much into SQL) wasn’t successful.
Any tips, pointers, and help please?

The link below would probably be a good place to start. It lists all assets with all included tags. You can reference other example queries in the public repo to see how to JOIN asset groups as well

Hey Thanks for the help!

In case anyone would need the same type of query, the below has been successful for me:

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 asset_id
SELECT da.ip_address, da.host_name, ot.owner_tags
FROM fact_asset_group fag
JOIN dim_asset_group dag USING (asset_group_id)
JOIN dim_asset_group_asset daga USING (asset_group_id)
JOIN dim_asset da USING (asset_id)
LEFT JOIN owner_tags ot USING (asset_id)
WHERE dag.name = 'DynamicGroupName’