Adding Owner Tag to SQL Query

InsightVM SQL Queries
1

SELECT DISTINCT ds.name AS site_name, da.ip_address, da.host_name, da.mac_address, dos.description AS operating_system, dht.description, dos.asset_type, da.last_assessed_for_vulnerabilities, aggregated_credential_status_description,
das.port

FROM dim_asset da
JOIN dim_operating_system dos USING (operating_system_id)
JOIN dim_host_type dht USING (host_type_id)
JOIN dim_site_asset dsa USING (asset_id)
JOIN dim_site ds USING (site_id)
JOIN fact_asset_scan_service fass using (asset_id)
JOIN dim_credential_status dcs using (credential_status_id)
JOIN dim_credential_status USING(credential_status_id)
JOIN fact_asset fa USING (asset_id)
JOIN dim_aggregated_credential_status USING(aggregated_credential_status_id)
JOIN dim_asset_service das USING (asset_id)

ORDER BY site_name, da.ip_address

Please See Query Above:

I would like to add the “owner tag” to the above SQL Query. I have tried all the connections with the Data map from Rapid7 but cannot get it to work. Any help would be appreciated

2

@jacob_horning Since you can have multiple owner tags associated with a single asset, the best way to do this is to create a list of owner tags first. Something like:

WITH   
   owner_tags AS (  
      SELECT asset_id, array_to_string(array_agg(distinct tag_name),'|') AS owner_tags  
      FROM dim_tag  
         JOIN dim_tag_asset USING (tag_id)  
      WHERE tag_type = 'OWNER'  
      GROUP BY asset_id  
   ) 
SELECT DISTINCT ds.name AS site_name, da.ip_address, da.host_name, da.mac_address, dos.description AS operating_system, dht.description, dos.asset_type, da.last_assessed_for_vulnerabilities, aggregated_credential_status_description,
das.port, ot.owner_tags
  FROM dim_asset da
  JOIN dim_operating_system dos USING (operating_system_id)
  JOIN dim_host_type dht USING (host_type_id)
  JOIN dim_site_asset dsa USING (asset_id)
  JOIN dim_site ds USING (site_id)
  JOIN fact_asset_scan_service fass using (asset_id)
  JOIN dim_credential_status dcs using (credential_status_id)
  JOIN dim_credential_status USING(credential_status_id)
  JOIN fact_asset fa USING (asset_id)
  JOIN dim_aggregated_credential_status USING(aggregated_credential_status_id)
  JOIN dim_asset_service das USING (asset_id)
  LEFT JOIN owner_tags AS ot ON (ot.asset_id = fa.asset_id)
ORDER BY site_name, da.ip_address

This would give a result similar to:

site_name ip_address host_name mac_address operating_system description asset_type last_assessed_for_vulnerabilities aggregated_credential_status_description port owner_tags
site-1 10.1.1.1 hostname-1 00:00:00:00:00:00 Microsoft Windows Server 2012 R2 Standard Edition Virtual Machine Server 2020-06-10 08:18:07.957 All credentials successful 80 tag-1|tag-2|tag-3

Hope this helps!

3 Likes
3

Thank you so much for your help this works perfectly!

1 Like