SQL query for assets with multiple tags

jhenry2 · July 27, 2023, 11:21am

I am looking for a sql query to show assets that have more than one tag

john_hartman · July 27, 2023, 1:48pm

Are you just trying to identify the assets with multiple tags or do you actually want all tag info displayed per asset?

john_hartman · July 27, 2023, 1:49pm

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

This file has been truncated. show original