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

ddavidsp · January 23, 2023, 11:08am

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?

john_hartman · January 23, 2023, 1:42pm

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

github.com

rapid7/insightvm-sql-queries/blob/master/sql-query-export/Assets-With-All-Tags.sql

-- Console query to list all assets by IP and Host Name with the corresponding tags for each tag category
-- Copy the SQL query 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

This file has been truncated. show original

ddavidsp · January 24, 2023, 12:41pm

Hey Thanks for the help!

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

WITH
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’