SQL Query - Assets and Tags

I have been at this all day and tried various things from this forum; I’m just not finding what I need.

My V.P. has tasked me with tagging all Rapid7 Assets with a Custom Tag, Location Tag and Owner Tag.

I am trying to create query that outputs this information on a single line per asset.
For Example: AssetA, CustomTag1, LocationTag1,OwnerTag1

All Custom Tags would be in the CustomTag Column, Locations in the LocationTag1 Column etc.

If there is no OwnerTag then the entry in that column would be left blank so that I can easily identify any ones that I missed.

Any help is appreciated!
Wolfgang

Hey Wolfgang,

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)
2 Likes

A billion thank you’s for this query!

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.

Thank you again!

2 Likes